Tag Info

New answers tagged

1

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


3

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       


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


0

While using nested IF statements works, the resulting formulas can be lengthy, error-prone, difficult to change or debug. For a complex pricing scheme I would use VLOOKUP. Example: the columns D and E contain the pricing policy +-------------------------+--------+ | If price is at least... | Markup | +-------------------------+--------+ | ...


0

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


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 ?


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

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.


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


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



Top 50 recent answers are included