Tag Info

Hot answers tagged

5

With this little snippet you can do that. Code function isFormula(startcol, startrow) { // prepare string var str = String.fromCharCode(64 + startcol) + startrow; // retrieve formula var fCell = SpreadsheetApp.getActiveSpreadsheet() .getRange(str).getFormula(); // return false if empty else true return fCell ? true : false; } Usage ...


5

This formula will do the job: =regexextract(A1;"@(.*)") If no @ is found, the original value is shown. The =regexextract function (documentation) executes the regular expression parameter (@(.*)) against the value of cell A1, and returns the matching group.


4

I would use the following formula to obtain an URL. Formula =IF(ISEMAIL(A2), REGEXEXTRACT(A2,"@(.+)"), IF(ISURL(A2), A2, IF(ISURL(REGEXEXTRACT(A2,"@(.+)")), REGEXEXTRACT(A2,"@(.+)"), "No valid entry" ) ) ) Copy / Paste ...


4

I found three solutions for you. Formula =INDIRECT("DATA!A"&A1) Google Apps Script function dataSubset(data,subset) { var output = []; for(var i=0, iLen=subset.length; i<iLen; i++) { for(var j=1, jLen=data.length+1; j<jLen; j++) { if(parseInt(subset[i]) == parseInt(j)) { output.push(data[j-1]); } } } return ...


3

Please try, 1 in A2 and in A3 copied down: =if(mod(row(),7)=2,A2+1,A2)


3

You can use the string concatenation operator, &: =IF(A1>A2, "value is " & C5, "value is " & D5) This is equal to, but less verbose than: =IF(A1>A2, CONCAT("value is ", C5), CONCAT("value is ", D5)) As to your follow-up comment, you can concatenate several strings and values: =IF(A1>A2, "value is " & C5 & " right now, but ...


3

The problem you have is that the data row reference changes when you drag-copy the formula down. It needs to be made static. So instead of sheet2!A1:K you need to write sheet2!A$1K or in this case (as you're starting from row 1 anyway), you can use: sheet2!A:K


2

You can filter out the unwanted results by using the FILTER function: =UNIQUE(FILTER(A1:A; A1:A <> "No explanation/don't know"; A1:A <> "To far"; A1:A <> "Too costly")) Check out this example spreadsheet.


2

I've found another solution: =JOIN(" vs ",SPLIT(JOIN("%",C10:C14),"%",0)) The % can be any symbol really that isn't present in the list, like a comma, or ampersand, or question mark.


2

Perhaps something like this: =query(A:C;"select C where A=x and B=y") with x and y replaced by what it is you wish to select. Since sight of your spreadsheet, I am not sure of what you would like where but suggest: insert a column immediately to the right of D in Fleet Log and in E2 enter: =C2&"|"&D2 and copy down to suit. in Sheet5 ...


2

The third parameter you speak of in the QUERY function, is meant to control the headers. If set to be -1, then the Google Spreadsheet will depict its own choice in choosing headers, based on the data available: I've used the following data set: If set to be 0, then no header will be used, leaving: If set to be 1, then the first row will be used, ...


2

Add the following formula in K1: =ARRAYFORMULA(H:H * J:J) If you have a header in the first row, then do this: =ARRAYFORMULA(H2:H * J2:J) To avoid the remaining zero's, best is to select a fixed range: =ARRAYFORMULA(H2:H250 * J2:J250) Reference Google Drive Help: ARRAYFORMULA


2

In Dashboard D36 please enter: =sumif(log!C:C,A36,log!B:B) (should result in the 19 you mentioned) and then copy up to D4.


2

I think you are getting the correct answer but .2857 is being rounded down to less than £1! (ie it is just the formatting).


2

You should refer to the sheet name only once, so: =SUM(Sheet1!B1:B5) returns the sum of the range B1:B5 in Sheet1.


1

I used this to solve my problem. =IF(ISTEXT(VLOOKUP($A6,responses!$D:$M,3, FALSE)),"Yes","No")


1

As Google Sheets does not natively support iteration with circular referencing, you will need to resort to Google Apps Script. Eg: function onEdit(e) { var editRange = e.range; var sheet = editRange.getSheet(); if (sheet.getName() == "Sheet1" && editRange.getA1Notation() == "B1") { var updateRange = sheet.getRange("A1"); var newValue ...


1

Assuming the data starts in row 1, try in B1: =ArrayFormula(IF(LEN(A:A)=0,VLOOKUP(ROW(A:A),{{0;FILTER(ROW(A:A),LEN(C:C)=0)},{FILTER(A:A,LEN(C:C)=0);""}},2),)) Out of interest, the formula simplifies a bit if you are retrieving from cells below: =ArrayFormula(IF(LEN(A:A)=0,VLOOKUP(ROW(A:A),FILTER({ROW(A:A),A:A},LEN(C:C)=0),2),))


1

If the data starts in row 2, then try: =ArrayFormula(IF(A2:A="A",SUMIF(IF(A2:A="A",ROW(A2:A),ROWS(A:A)+1),"<="&ROW(A2:A),B2:B),))


1

ASSUMING the weekdays will always be abbreviated exactly as shown, are the entire cell content (not part of a longer string) and data is in ColumnA, the following may serve: =ArrayFormula(sum(countif(A:A,{"Mon","Tues","Wed","Sun"}))) with the insertion of other search strings to suit.


1

This script will do the same (plus a little bit more). Code function myInterpolation(x, y, value) { if(value > Math.max.apply(Math, x) || value < Math.min.apply(Math, x)) { throw "value can't be interpolated !!"; return; } var check = 0, index; for(var i = 0, iLen = x.length; i < iLen; i++) { if(x[i][0] == value) { ...


1

I found a way to do it - there may be a better way, but this is what I came up with: Assuming the data is in A1:B10 and $C$1 contains the key to look for: =FORECAST($C$1, OFFSET(B$1,MATCH($C$1,A$1:A$10,1)-1,0,2,1), OFFSET(A$1,MATCH($C$1,A$1:A$10,1)-1,0,2,1)) In detail: FORECAST does a linear interpolation, but it assumes a straight line. So we ...


1

You can use the =SUMIF as MaryC says, or the =FILTER function that I usually prefer: =SUM(FILTER(F2:F, B2:B = "/Online Computers")) The =FILTER function filters your data so that only rows matching B2:B = "/Online Computers" are included. See the example spreadsheet I've set up, and the Google spreadsheets function list.


1

This is the function you want: SUMIF(range, criterion, [sum_range]) Example SUMIF(A1:A10, ">20", B1:B10) Summary Returns a conditional sum across a range. range The range which is tested against 'criterion'. criterion The pattern or test to apply to 'range'. sum_range - [optional] The range to be added up, if different from ...


1

Please try: =if(COUNTIFS('Our Animals'!$B:$B,A2,'Our Animals'!$C:$C,"no")=0,"Yes","No") in Animal Types B2, copied down.


1

You need to prepend your "s with another ": =if(C2= "Aubrey said, ""you are my favorite friend.""", 10, 0) Notice how you get three "s in a row - that's two "s to close the citation, and the last one to end the text string.


1

Have you tried <>? =SUMIF (A1:A38, "<>Liverpool", B1:B38) + SUMIF(D1:D38, "<>Liverpool", C1:C38) Not that the <>-symbols should be inside the quotation marks.


1

do you have any suggestions for a way to do with with another application? This is possible with our product, JotForm. Please see How To Add Options in a Payment Form tutorial for details. The tutorial assumes you already know how to use JotForm so first do these steps: Go to JotForm site and create an account Create a new form Add "Purchase ...


1

It looks like there is a hidden built-in function ISFORMULA() from Google. I realized when I deleted my own function and was still working. I tried it in a new spreadsheet and still works. No cache issue.


1

I think I just discovered an undocumented formula in the new Google Sheets. @Pnuts was saying the CELL formula didn't work (partially because it's only available in the new Google Sheets), so I copied the code into a new Google Sheet. An error was raised, telling me my (custom) formula accepts only one argument and not two: Renaming the isFormula into ...



Only top voted, non community-wiki answers of a minimum length are eligible