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

The following custom number format mask will produce the desired results without losing the accuracy of the underlying value. [>999999]0.0,,\M;[>999]0.0,\K;0       


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

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

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


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


3

In cell G9 of the spreadsheet you shared, I entered this formula: =arrayformula(if(len(B9:B33)*len(C9:C33)*(C9:C33>B9:B33);if(timevalue(B9:B33)<timevalue("17.00.00"); mod(C9:C33-time(17;0;0);1)*1440; mod(C9:C33-B9:B33;1)*1440);)) It will only output a result when there are values in col B and col C and when col C > col B. See if this helps ?


3

Assuming your data is in A1:A, try in B1 =query(ArrayFormula(iferror(regexextract(A1:A, {"^(.+)\s", "\s(.+)$"}))), "select Col1, count(Col2) where Col1 <>'' group by Col1")


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

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


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

Both of the solutions above work if there is at least one cell containing text. However: = JOIN(" vs ",SPLIT(JOIN("%",C10:C14),"%",0)) Would return %%%% if C10:C14 were all empty and. = JOIN( " vs " ; FILTER(C10:C14; NOT(C10:C14 = "") )) Would return #N/A if C10:C14 were all empty. However, you can slightly amend the first solution to replace the % ...


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

At this time Google Docs (documents) doesn't include a feature to do calculations. An alternative is to use an add-on or a Google Apps Script to extend the built-in features of Google Docs. References Extend Google Docs, Sheets, and Forms with Apps Script - Docs editors Help


2

I would use VLOOKUP instead of DGET; it has simpler syntax and works with ARRAYFORMULA. Example: Sheet2 columns A and B +---------+-------+ | User Id | Name | +---------+-------+ | 4444 | Grace | | 1111 | Jon | | 2222 | Tim | +---------+-------+ Another sheet has numbers 1111, 2222, 4444, 2222 in cells A2-A5. Entering in B2 the formula ...


2

You can refer to cells in the DSUM criterion. The problem with your query that you put a function inside of a string instead of appending its result to a string. That is, instead of ">index(FILTER(i:i,not(ISBLANK(i:i))),rows(filter(i:i,not(ISBLANK(i:i)))),1)" you need ...


2

Here is a formula that does the above, line-broken and indented for readability: =JOIN(",", ARRAYFORMULA( FILTER($A2:$A7, NOT(ISBLANK(B2:B7))) & FILTER(B2:B7, NOT(ISBLANK(B2:B7))) )) There is also a simpler formula, but it leaves a trailing comma in the result: =CONCATENATE(ARRAYFORMULA( IF(B2:B7="", "", $A2:$A7 & B2:B7 & ",") ))


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

If your email address (bob@example.com) is in A1 =index(split(A1,"@"),0,2) = example.com =index(split(A1,"@"),0,1) = bob You are splitting on the @ and than using index to select which column you want from the split.


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



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