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 ...


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

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

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

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

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

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

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