The Stack Overflow podcast is back! Listen to an interview with our new CEO.

# Tag Info

17

There is no need to jump into GAS, standard spreadsheet functions handle this easily. Please paste the following into a cell in your example =iferror(filter(\$A\$2:\$A, match(\$A\$2:\$A, B2:\$B , false)), "no match") The match function generates a cartesian product of the two ranges full of errors except where a match is found. It returns an index into the 2nd ...

12

Try Shift+Ctrl+: and Ctrl+; Those are the keyboard shortcuts for "insert time" and "insert date" respectively. (based on pnuts' comment)

11

=SUMIF(D:D,"bob",C:C) in C column are numbers, in D column are ("bob" or others) if you wish do not sum all column you can use for example =SUMIF(D2:D6,"bob",C2:C6) Note: Text is case sensitive (Bob is not equal bob)

10

I bashed up a comparison sheet for the formulas between MS Excel 2010 and Google Spreadsheet. Comparison was based on the names only. See snapshot of the current situation: If you want to make sure that Google Spreadsheet files are properly converted and still work in MS Excel 2010, then don't use the following formulas: ADD ARRAYFORMULA CONCAT CONTINUE ...

7

Solved with: =INDIRECT( ADDRESS( 4, COLUMN() ) ) ADDRESS gives me a string like "\$A\$1" and INDIRECT makes it a cell reference.

5

You can use the INDIRECT function. Here is how it works: And watch it live at this link

5

This is an old post, but I was looking into this recently and figured I'd chime in. Depending on you needs, the easiest answer I could think of is by creating a drop down menu and referencing a function on another cell. Example: Select A10:A20 > right click > Data Validation Criteria = "List from range" > F2 Formula in cell F2: =mult(now(),1) To ...

5

Short answer QUERY() allow the use of a subset of SQL but this subset does not include the FROM clause. QUERY() require a single array as the source table, so it could not be used, instead use ARRAYFORMULA(), TRANSPOSE(), JOIN(), SPLIT(), REPT(), FILTER(), COUNTA(), SORT() and the division (/) and concatenate (&) operators to build a "flexible ...

5

First, I'd split the data from column A into two columns. In this case it looks like the length is consistent so it makes it easier (I can demo the other if need be). Get the left chunk with =left(A2,11) Get the right chunk with =right(A2,10) Now you have Element 1: | Value 1.1 To flip the unique elements use =transpose(unique(B2:B7)) Now you have ...

5

Formula =ArrayFormula(FILTER(A4:C7,ISERROR(match(A4:A7,D4:D5,0)))) Explanation MATCH returns an error if the invited doesn't appear on the attended list. ISERROR converts errors to TRUE and values to FALSE This result is used as the filtering criteria. The result is, the list of absents: | 001 |Tim |Smith | 003 |Amy |Wilson Just add the ...

5

=AVERAGEIF(A1:Z1,"<>") "<>" on its own means "not equal to an empty string" in Google Sheets.

5

You could use INDEX without a row parameter to return a column For example, =INDEX(GOOGLEFINANCE("TSLA", "price", TODAY()-30, TODAY(), "DAILY"),,2) will return the second column fully. However, To fully manipulate the array, you can use QUERY: =QUERY(GOOGLEFINANCE("TSLA", "price", TODAY()-30, TODAY(), "DAILY"), "Select Col1,Col2 where Col2>355 ...

4

Google Docs has size limits. There can be 2 million cells containing formulas. You can compare the Excel functions with this Google Spreadsheet function list. While there are some common functions, Google Spreadsheet does have its own set of formulas Google spreadsheets also have complexity limits. Every time a cell is updated, any cell that references it ...

4

I bashed up a comparison sheet for the formulas between MS Excel 2013 and the new Google Spreadsheet. Comparison was based on the names only. See snapshot of the current situation. Screenshot If you want to make sure that the new Google Spreadsheet files are properly converted and still work in MS Excel 2013, then don't use the following formulas. ...

4

Formula Instead of =FILTER( Recherche!B4:N , Recherche!O4:O1002 = "Beleggersprofielen"| Recherche!C4:C1002 = "11 - 20" ) Note: Added a breakline for readability. This haven't affect how the formula works. Try =FILTER( Recherche!B4:N1002, (Recherche!O4:O1002="Beleggersprofielen")+(Recherche!C4:C1002="11 - 20")) Note: FILTER require that the ...

4

Try: =D5*index(GoogleFinance("CURRENCY:USDINR","price","23/07/2017"),2,2) The error message seems clear enough to me: Function MULTIPLY parameter 2 expects number values. But 'Date' is a text and cannot be coerced to a number. Why 'Date'? ... because, as mentioned for GOOGLEFINANCE: Historical data, even for a single day, will be returned as an expanded ...

4

3

Figured it out... took me a while... INDIRECT() has to wrap the entire reference. In order to achieve what I needed, the formula looked like this: =INDIRECT("Sheet2!A"&A1)

3

If you use SUBTOTAL function in place of your SUM functions then subsequent SUBTOTAL functions will ignore the numbers returned by other SUBTOTAL functions, hence avoiding double counting. For example if A5 has this formula =SUBTOTAL(9,A1:A4) [the first argument dictates the function, 9 = SUM] then if you use this formula in A6 =SUBTOTAL(9,A1:A5) you ...

3

This little script will compare two ranges, as a formula: function COMPARE(array1, array2) { var array = []; for(i=0; i<array1.length; i++) { for(j=0; j<array2.length; j++) { if(array1[i][0] == array2[j][0]) { // the extra square brackets will make it a 2D array, // aligning it vertically array.push([array1[i][...

3

Ultimately, you need to do what you already predicted: adding a leading zero. The TEXT function will make that happen like this: =TEXT(A2, "00") I've created a formula that takes on the complete column, filter for empty cells, brings together the MONTH and DAY and SORTs the lot. Formula =SORT(ARRAYFORMULA(TEXT(MONTH(FILTER(A2:A;A2:A<>""));"00") &...

3

In array formulas instead of AND and OR functions use * and + respectively. Example AND: Instead of =ArrayFormula(and(A2:A4,B2:B4)) use =ArrayFormula(A2:A4*B2:B4) OR: Instead of =ArrayFormula(or(A2:A4,B2:B4)) use =ArrayFormula(A2:A4+B2:B4)

3

Let's look first at why the function you've posted isn't working. VLOOKUP needs a two-dimensional data range to work with. It looks for your search_key (param 1) in the first column of range specified (has to be the first column - an unfortunate limitation of VLOOKUP), and returns a value from the matching row. The column returned is specified by param 3, ...

3

You can do this with a combination of the INDIRECT and MATCH functions. In the last column of your table add the following formula in line with the data that you would like to determine the max of: Google Spreadsheet =INDIRECT(ADDRESS([Name_Row], MATCH(MAX([Data_Span]), [Data_Span], 0) + [Column_Offset])) Excel =INDIRECT(ADDRESS([Name_Row], MATCH(MAX([...

3

Short answer Add a new sheet. Add the following formulas to the new sheet Cell A2 -> Headers =OFFSET('Form responses 1'!\$A\$2,ceiling((row()-1)/5,1)-1,0) This could be repeated several times, one for each header column, just change the last parameter accordingly. Cell B2 -> data =OFFSET('Form responses 1'!\$A\$2,ceiling((row()-1)/5,1)-1,mod(row()-2,5)*...

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