Hot answers tagged

5

Is it at all possible to use column headers like this? Yes, it is possible. First, you need to use MATCH to get the column number of the column whose value matches 'Name'. Then you need to use ADDRESS to get the cell reference. Finally, you need to use SUBSTITUTE to remove the row number from the cell reference. =QUERY(Sheet1!A1:C,"SELECT A, B, C where "&...


4

I found the function LEN(A1) to be very useful and robust. It also works for cells that contained text that has been deleted. It returns the length of a cell's content and if it returns 0, the cell is empty. IF(NOT(LEN(A1))) or IF(LEN(A1)=0) to check for empty cells IF(LEN(A1)) or IF(LEN(A1)<>0) to check for non-empty cells I prefer the fist ...


4

Although regexextract can return an array, you'd need to know how many there are, so this isn't useful in your case. The way to get around this limitation of regexextract is to use regexreplace to get rid of the text you don't want. Like this: =trim(regexreplace(A2, "(^|\s)[^#]\S*", "")) The regular expression matches the words not beginning with # and ...


3

Short answer Use ARRAY_CONSTRAIN(array,row_limit,column_limit) Explanation ARRAY_CONSTRAIN function could return the number of rows and columns that you want, just set the row_limit and column_limit parameters accordingly. To automatically set this parameters you could use a function like COUNT or COUNTA. To calculate the number of rows/columns ...


3

You need =E$1 there. (Or, =$E$1 if the formatted range spans multiple columns.) Putting E1 would compare cells to the string "E1", which is not what you want. Putting =E1 compares the first cell in your column range to E1, the second to E2, the third to E3, etc -- because the reference is relative. The dollar sign makes it an absolute reference.


3

Keep the inequality signs going in the same direction: =IF(A1<60, "F", IF(A1<70, "D", IF(A1<80, "C", IF(A1<90, "B", "A")))) The above works fine as long as there aren't too many cases. But if you had many options, C-,C+,B-,B+... it would be better to use a lookup table instead. For example, if you have a lookup table such as the one below, ...


3

The command offset does what you want: =offset(D3, -1, -2) is the value 1 row above and two columns to the left of D3. So, your formula for D3 could be =offset(D3,-1,0) + offset(D3,0,-2) - offset(D3,0,-1) Extended down the column, it will do the intended computations regardless of row insertions.


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


3

Short answer A formula that does the required in the question =JOIN(",", QUERY( {ArrayFormula({A1:A6}&{B1:B6}),B1:B6}, "Select Col1 Where Col2<>''" ) ) Explanation The above formula has nested three functions, use the matrix handling feature of Google Sheets and the concatenate operator. ArrayFormula({A1:...


3

Nested IFs should do fine - check for empty first, return either "false" or the results of your date-exceeded check: =if(F9="","False",IF($L$1>=F9,"exeeded","")) However, if L1 contains the due date, and F9 contains a date to check against the due date, you may want to reverse the comparison to be "<" or "<=" instead of ">=" =if(F9="","False",IF(...


2

It doesn't work with arrayformula - importdata, importxml, and split will not work it unfortunately.


2

This worked for me when I tried it: =COUNTIFS(A1:A7,"JESS",C1:C7,"OFF") This allows you to "count if" on multiple criteria. NOTE: The COUNTIFS function is only available in the new Google Sheets (released December 2013), so old spreadsheets that have not yet been converted will not be able to utilize it.


2

Google Sheets could be expanded to implement "a formula approach" for certain tasks. This is called "custom functions". Custom functions should only be used to display values, so they should not be used to set the conditional formating directly. Instead use the conditional formatting built-in feature. References Custom Functions in Google Sheets - Apps ...


2

The solution, as I understand it, is as follows. By setting a FILTER formula (array & condition) you create the first step, the "what are we looking for, specifically" then that formula gets wrapped in a SUM formula which adds the array values. So, in the case above =SUM(FILTER(B:B,A:A="June")) this formula is looking for the values, all of them, in ...


2

The problem is that "11:00:00 AM" is not a time value, it's just a string with characters 1, 1, :, 0, and so forth. You can convert it to a time value with timevalue function: =A1<timevalue("11:00:00 AM") or better yet, define the time directly with time =A1<time(11, 0, 0) The second approach is locale-independent; it does not rely on ...


2

You can use it without "="& like this: =DGET(F60:M61,"Sat",{"Start Time";F67})


2

The where clause supports several types of string matching. Method 1: String comparison with >= and < The query string select A where A < 'G' selects all strings that would precede G in a dictionary, i.e., all that begin with letters A-F. This is case-sensitive. The case-insensitive form is select A where lower(A) < 'g' More complex ...


2

If you did not insist on array-based formula, then sumifs(C:C, A:A, A2, B:B, "<="&B2) entered in D2 and copied around the D column, would do the job. I don't think that an arrayformula-based solution exists, because arrayformula runs through an array once, performing some element-wise operations. You want to compare each element with all others, ...


2

Although this is a special case of In a Google Spreadsheet, show all combinations for a selection of columns I think it's good to have a simpler answer specifically for the case of two columns. The technical term is "Cartesian product of two sets". I use the same method as Rubén, which requires a character that does not appear in the column entries. Rubén ...


2

This can be done with a script, using insertSheet method which takes the new sheet name and the template after which it's to be created. The first function adds a custom menu item when the spreadsheet is opened, the second performs duplication when that menu item is invoked. As far as I can tell, the contents are copied over in the same way as they are ...


2

If the Team and Rank columns are B and C, then you need the formula =sort(B2:C, 2, False) which sorts columns B and C according to the second one (C), in descending order (False). Note that the output of sort cannot overwrite the data in place. It has to be placed elsewhere, e.g., on a another sheet named "sorted".


2

You should filter the values before counting uniques: =countunique(filter(A:A, C:C>0))


2

Assuming the data is in column B, the following two formulas do the job. In cell C1: =filter(B1:B, row(B1:B) < 1 + max(filter(row(B:B), len(B:B))) / 2) In cell D1: =filter(B1:B, row(B1:B) >= 1 + max(filter(row(B:B), len(B:B))) / 2) Explanation: Find the last row with data by max(filter(row(B:B), len(B:B))) Divided by 2 and use as a threshold ...


2

The main goal is to make sum ArrayFormula. The original brilliant solution was provided here. And your formula is: =ArrayFormula( IF($Q2:Q="No", SUMIF(IF(COLUMN(J2:P2),ROW(A2:A)),ROW(A2:A),J2:P), IF($Q2:Q="Yes", SUMIF(IF(COLUMN(R2:X2),ROW(A2:A)),ROW(A2:A),R2:X), IF($Q2:Q="",""))))


2

The following formula counts the rows of Data sheet which contain the string given in cell A1 of current sheet: =countunique(arrayformula(if(Data!$C2:$F = A1, row(Data!$C2:$F), ))) Explanation: the arrayformula replaces each matching string with the row number, and leaves other cells blank. Then countunique counts the number of distinct entries within ...


1

To avoid listing 1 88 times, you can use 1+0*row(1:88) within arrayformula: =IRR(arrayformula({1+0*row(1:88); -236})) I'm leaving aside the issue of how this aligns with IRR documentation (which says that the first amount must be negative), and the fact that in my spreadsheet the above formula returns an error "IRR attempted to compute the internal ...


1

The following formula automatically fills the output column and stops at the row 10. To fill more rows, replace 10 by the desired number of rows or by a function / subformula that calculates the required rows. =ArrayFormula( ARRAY_CONSTRAIN( VLOOKUP(ROW(A:A),{IF(LEN(A:A)>0,ROW(A:A),""),A:A},2), 10, 1 ) )


1

Can't edit your spreadsheet, but this formula (entered in C3) =ArrayFormula(if(row(B3:B) <= max(if(not(isblank(B3:B)), row(B3:B))),vlookup(row(B3:B),filter({row(B3:B),B3:B},len(B3:B)),2),)) should bring you the output you expected.


1

The other alternative is to combine split, join, repeat with regex extract like this: =join(" ",REGEXEXTRACT(A1,rept(".*(#\w+\S?\w+)",counta(split(A1,"#"))-1))) it basically split out the text by # and then repeats the generic regex to capture it that number of times (-1 for the additional cell it puts out when you use split) - Also it is able to include ...


1

An alternative to the use of OFFSET is the use INDIRECT and the R1C1 notation. Note: For relative positioning use R[1]C[1]. The formula from D3 to D9 could be: =INDIRECT("R[-1]C[0]",FALSE)+INDIRECT("R[0]C[-2]",FALSE)-INDIRECT("R[0]C[-1]",FALSE) Remarks: For better or worse, the references will not change if new rows or columns are added.



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