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 if(fCell == '') { return false } ...


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

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

If you don't want to precalculate the subtotal of off days, you can use an array formula: =arrayformula(SUM((A:A="Jess")*(B:C="OFF")*1)) A:A="Jess" returns a bunch of TRUE and FALSE, in your sample, it would be {TRUE, FALSE, FALSE, TRUE, FALSE} B:C="OFF" returns another bunch of TRUE and FALSE, in your sample, it would be {FALSE, TRUE; FALSE, FALSE; ...


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

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

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


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 is another way of doing it: =COUNTA(FILTER('Survey Data'!F2:F200,'Survey Data'!F2:F200="Divisional Exec", 'Survey Data'!A2:A200="Act")) but, as Jean-Christophe Meillaud stipulated, it yields 1 if the criteria aren't met. The following formula is yet another way of doing things: =IFERROR(INDEX(QUERY(A2:F200,"SELECT COUNT(F) WHERE F='Divisional ...


1

I'm not sure if you necessarily require a Google Apps Script solution; the tags you have used suggest that a spreadsheet formula might be adequate. If this is the case, then it is definitely possible to sum only rows that are not hidden with SUBTOTAL. For example: =SUBTOTAL(109;A:A) will sum all cells in column A that are not hidden.


1

Tried to find all kinds of solutions with formulas, but wasn't successful. Therefore I wrote this very small script to do the work for you: function myCalc(range1,range2) { if(range1.length != range2.length) { throw "Both ranges need to be the same !!"; return; } var l = range1.length; var output = []; for(i=0; i<l; i++) { ...


1

My approach would be to "normalise" the data first: =ArrayFormula(IF({1,0};IFERROR(REGEXEXTRACT(A2:A;"^([a-zA-Z]+)"));B2:B)) and then you can actually wrap this directly in a QUERY function to produce the desired output: =ArrayFormula(QUERY(IF({1,0};IFERROR(REGEXEXTRACT(A2:A;"^([a-zA-Z]+)"));B2:B);"select Col1, sum(Col2) where Col1 != '' group by Col1 ...



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