Tag Info

Hot answers tagged

5

Introduce the POWER function and use the ROW function to increment the exponent. Your second line would be, =100*power(1.01, row(1:1)) Fill down and the ROW(1:1) will increment as 1, 2, 3, 4, 5, etc..


4

No, that isn't currently possible. However, if the spreadsheet is used for viewing data (and not entering or modifying) you could make a new spreadsheet for each user, and use importRange from the "master" spreadsheet in each copy's "A1" cell. Each copy can then freeze/hide/filter as they please and their data will update almost in real-time as the master ...


4

Yes, and it's called RATE. Example of usage: =RATE(60,0,-100,150) calculates the percentage required for growing $100 to $150 over 60 interest payment periods, while taking out $0 each period. The answer is 0.68%. Wolfram Alpha agrees. The complete list of financial functions in Google Spreadsheets is here.


3

You may want to give the query() function a try. Assuming your data is in the range A1:D, try: =query(A1:B; "select A, sum(B) where A <>'' group by A"; 1) Note: depending on your locale you may have to change the semi-colons with comma's.. This is a one formula solution that will update when new rows are added. I hope that 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

When you find yourself writing a bunch of similar IF statement, you probably need FILTER. For example, =JOIN(CHAR(10), FILTER(A1:F1, NOT(ISBLANK(A1:F1)))) joins the content of cells A1 through F1, separating them with linebreaks — but the empty cells are skipped as they fail the condition NOT(ISBLANK(...)).


3

The following formula for A1:A7 =WORKDAY.INTL("1/05/2015",Row(),"0000011") returns Tuesday, January 6, 2015 Wednesday, January 7, 2015 Thursday, January 8, 2015 Friday, January 9, 2015 Monday, January 12, 2015 Tuesday, January 13, 2015 Wednesday, January 14, 2015 Reference WORKDAY.INTL - Docs Editor Help


3

This can be done with a conditional statement involving WEEKDAY Let's say you put 5/7/2015 in A2, and the conditional formula =IF(WEEKDAY(A2)=6,A2+3,A2+1) in A3. (Weekday=6 means Friday, after which two days are skipped). Dragging this down you'll see 5/7/2015 5/8/2015 5/11/2015 5/12/2015 5/13/2015 5/14/2015 5/15/2015 5/18/2015 and so on.


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       


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

Use conditional formatting with a custom formula. For example, select cell A1, go to Format/Conditional formatting, select "custom formula" and enter =ISERROR(VALUE(A1)) In the same dialog you can choose the range to which this formatting should be applied.


3

For conditional formatting, apply the following to A2: "Custom formula is" =AND(F2>TODAY(),G2>TODAY(),H2>TODAY())=TRUE (You can then indicate what the range of formatting should be, e.g., A2:A to get all A column except the header). To filter the content, use either Query or Filter. Here is an example with FILTER: ...


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

I found a way to do it: Select Column A and "Insert 1 left" to add a column. Your table data is moved over one to columns B - D. In your newly blank Cell A2, enter formula =IF(COUNTIF($B$2:$B2,B2)=1, "Unique", "Duplicate") Copy the formula from A1 and paste it down the A column range for as far as you have data. The "$" absolute references will ensure ...


2

The FILTER command does this nicely. Suppose this is your table. A B C 1 Beer Brewery Rating 2 abc QQ 1 3 qwe WW 3 4 wer EE 5 5 ert WW 2 6 dfg QQ 4 7 sdf WW 3 Then add 9 Brewery MaxRating Favorites 10 WW where ...


2

Alternatively, for a 1-formula solution automatically updating when new rows are added, try: =ArrayFormula({trim(transpose(split(concatenate(rept(A2:A&char(9),counta(B1:1))), char(9)))), transpose(split(rept(join(char(9), B1:D1&char(9)),counta(A2:A)), char(9))), transpose(split(CONCATENATE(B2:D&char(9)), char(9)))}) NOTE: after adding a name ...


2

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


2

Seems like the answer was pretty easy. Managed to figure it out myself. =FILTER(All!A2:C999, All!C2:C999 = IF(C4 = "Any", All!C2:C999, C4)) If C4 is set to Any, then the filter allow all of them since every cell will be equal to itself. If its not, it only passes the ones which are equal to the C4 value.


2

The value of GoogleFinance("CURRENCY:USDBRL") is the value of one US dollar in Brazilian currency. This needs to be multiplied by the dollar amount that you want to convert. So, if B5 is the USD amount, the formula =B5*GoogleFinance("CURRENCY:USDBRL") will give the equivalent amount in Brazilian currency.


2

There is no way to dynamically change the confirmation/thank you message. However, you could use the Autocrat add-on (or adapt its code) to send your customers a confirmation email with the reference number.


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

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

Make a new sheet inside your current spreadsheet. Put two columns in it: VoiceType Sort order SOPRANO 1 CONTRALTO 2 TENOR 3 BASS 4 In your main worksheet (the one with the list of people), in a new column put a formula like this: =vlookup(upper(B2),Sheet2!A$2:B$5,2,0) Note that I have used the UPPER() function in case some of the ...


2

You probably want 1 instead of 16 as the last parameter of the query; this parameter means the number of header rows, not of headers. You also have an extra pair of parentheses in your formula. Otherwise the formula is correct (tested in my spreadsheet), although you could shorten it by replacing select Col1,Col2,..., Col16 with select * ...


2

Check out the built-in revision history, that's available in both the spreadsheet and the script editor under the first menu option (File). Read the Google Help for more info: revision history


2

Use double instead of single quotations marks i.e. =IF(C1="NULL",B1,C1) References IF - Google Editors Help


1

This is exactly what VLOOKUP does. =VLOOKUP(A2, Sheet1!A:C, 3, FALSE) searches for A2 in the first column of the range Sheet1!A:C, and returns the content of the 3rd column (relative to the range) in the row that matched. In case of no match, an error #N/A is raised. You can provide a custom error message by wrapping this in IFERROR: ...


1

I would suggest using dget in the first sheet, if the number of users is defined, and doesn't grow automatically. Assuming the second sheet is Sheet2, I would add 2 columns to the first sheet. The first column would have cells with the formula (changing the cells accordingly, with copy and paste): =dget(Sheet2!$A:$C,"PAID ACCOUNT",{"USER ID";$A2}) The ...


1

One alternative is to join both tables using formulas, then use Pivot Table and Copy and paste values. To join both tables using formulas, in the second sheet add the following formulas assuming that your first sheet name is Sheet1: In D2 =INDEX(Sheet1!B:B,MATCH(A2,Sheet1!A:A,0) In E2 =INDEX(Sheet1!C:C,MATCH(A2,Sheet1!A:A,0) Then fill down to the ...


1

The Chart Gallery in Google Developers - Google Charts is a collection of demos about using code to create charts. This charts can't be inserted in a Google Sheets but they could a Google spreadsheet as data source. It's worth to say that the Column Chart that can be inserted in a Google spreadsheet, but could be a bit tricky to add the annotations. For ...



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