Hot answers tagged

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       


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

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

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.


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

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


2

This formula will work to keep blank cells from returning "0" to another cell. =IF( AND( ISBLANK(A1)=true , isblank(B1)=true ) , "dontsayzero") Then, you can just remove the dontsayzero after you've tested it, but LEAVE THE QUOTES! Also, you can make things more sophisticated adding more "IF" and "OR" statements. Check this one out (remove carriage ...


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

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

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

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

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

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

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

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


1

This is what the command VLOOKUP is for. Put in C1 =VLOOKUP(A1, Sheet2!A:C, 3, FALSE) and drag down the row. Here, A1 is the entry to search for Sheet2!A:C is the range in which the first column (A) is to be searched 3 is the column number from which to take the result (that is, C) FALSE requires exact match, and does not assume the keys are sorted in ...


1

You need a custom function contained in a script. For example, the output below is from entering B1 = hashtag(A1), etc, where hashtag is the custom function at the end of this post, placed in a script (go to Tools > Script Editor to paste it in). +---+-------------------------+-------------------------+ | | A | B ...


1

Indeed, the first invocation of importrange cannot be nested within another function: the request for authorization does not "bubble up" (at first I thought this was an error on Google's part, but then considered that a single command can combine several importrange within it, so providing an authorization dialog in such a case would be extra complicated). ...


1

You can extract the number to another column using REGEXEXTRACT, and then sort the range by that column. For example, suppose this is your C column: +-----------------------+ | 503 in Kitchen & Home | | 85.5 in Bed & Bath | | -263 in debt | | 633.26 in something | | 543 in Outdoors | +-----------------------+ Enter in column D ...


1

Use JOIN subject to FILTER by ISBLANK. For the example spreadsheet below, the command =JOIN(", ", FILTER(A1:A6, ISBLANK(B1:B6))) returns "Robert, Jay, Tim" A B 1 Jon attacked 2 Robert 3 Grace attacked 4 Jay 5 Tim 6 Shog attacked


1

Prepend the column letter and/or row number that you don't want to change with $. Let me illustrate: Let's say in cell B2 I have this formula =B1*A1 If I copy that to C2, what is pasted in C2 is =C1*B1 But that's not what I want. I always want to multiply against A1. If, instead, B2 was =B1*$A$1 Then when I copied to C2 I'd get =C1*$A$1 If I ...


1

If you have multiple sums in the formula, you need multiple SUM commands, one for each sum. That is, =F15*20+SUM(F16:F20)*25+SUM(F21:F23)*30 Have to add things up before multiplying them all by a common factor.


1

Try: =ARRAYFORMULA(transpose(split(concatenate(if(B2:D<>"", B2:D,)&char(9)),char(9)))) or: =ARRAYFORMULA(transpose(split(concatenate(B2:D&char(9)),char(9)))) or also: =ArrayFormula(transpose(trim(query(transpose(B2:D),,rows(A:A)))))


1

I think you need the time() function. e.g: A1= hour B1 = minutes in C1, try: =TIME(A1, B1, 0)



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