Make your voice heard. Take the 2019 Developer Survey now

# Questions tagged [formulas]

Formulas used in spreadsheets edited by web applications like Excel Online, Google Sheets, among others.

423 questions
248 views

### Is it possible to import a formula from another spreadsheet?

I have a master spreadsheet, call it Master, and a lot of copies of this spreadsheet, one for every person in my organization, because they need their own copy. The Master sheet has a lot of formulas. ...
545 views

### How can I seed a sequence of random numbers in Google Sheets?

I understand the use of the function rand means the output is updated with every modification to the sheet contents. I am looking for a way to fix a seed so that random numbers don't change unless I ...
690 views

### How to multiply two cells ignoring text?

I have two cells, containing "number text", how do I extract numbers from them to multiply?
66 views

### How do I reference the value of multiple cells from one spreadsheet to another?

The real kicker here is that I only want to reference certain cells if a cell has been selected. I am creating a "leads" Spreadsheet on Google. Once a target has been contacted, their information ...
649 views

### Calculation based on first and last value in row

I have a Google Sheets spreadsheet which has intermittent data. I have a column where I'd like to calculate the difference between the first value in each row, and the last value in that row. The ...
28 views

### Formula Sum = 0

The sum still comes up zero and I have other sheets that are not having this issue:
864 views

### Help with ordering a Google Sheets =QUERY

I'm trying to add an ORDER by F to the following query, but every time I do it returns a syntax error. ORIGINAL QUERY (working) =QUERY(data!A2:Z, "select G, E, F, I, J WHERE A = " & C1, "") ...
122 views

How do I output nothing with an IF function? I have created this nested function that takes the average of three cells. However, if one of those cells is empty, it should only take the average of the ...
449 views

### Write cell value based on the URL of another sheet

Is there any way populate a cell in a Google sheet based on the value in the URL? For example: If the hyperlink below was on sheetA and the link was clicked. Is there any way to get ?cell=english ...
957 views

### In a formula search for closest future date in list, how do I include current week?

This array formula searches from a list of dates in Column A and returns the closest future date from today: =ArrayFormula(MIN(IF(A2:A>TODAY(),A2:A))) Column A: 8/4/2017 8/11/2017 8/8/2017 8/25/...
306 views

### Change formula based on value of cell

I'm calculating the a Loan payment with PMT function and was wondering how I can change the percentage based on the outstanding loan. Say if the outstanding loan drops below 80% of the total loan ...
834 views

### Date Format Lost When Combining Two Cells Using “&” and CONCATENATE in Google Sheets

I'm trying to figure out "how to" maintain the date format when I concatenate a date formatted cell with a nondate formatted cell. Result: The Date Format Is Lost Formulas Used Desired Result: I ...
4k views

### VLOOKUP using cell reference with IMPORTRANGE gives error

I can't see what I'm doing wrong with this. This Doesn't Work =Vlookup(L2,IMPORTRANGE("https://docs.google.com/spreadsheets/d/1_Le0cMfbHRSgXqhCdzjQrBscFNm6p1sL-DowWdCZ0Fs/edit#gid=1761671943","...
92 views

I want to make SUMIF in Google Spreadsheets look at the previous row for its evaluation. So if my sheet looks like this: trigger, value1 something, value2 I want it to add value2, because the ...
145 views

### What script / function would allow me to subtract a value from a unique account

I have an item shop and bank account set up for my students in class using Sheets. Currently students purchase their items via Google Forms and it logs their order in a Sheet. Normally, I would find ...
3k views

### Conditional countif date is before today and cell in another column is blank

Column AE has dates Column AG has text I need to count how many rows have these occurrences: Column AE is before today AND Column AG is blank I've been trying for a while, looking around the ...
880 views

### What do A:A and !A:A mean?

I have come across this SUMIF formula, I can't find any help doing a Google search. =sumif(N:N,"Cat",C:C) So, from what I understand of SUMIF, as per the documentation: SUMIF(range, criterion, [...
51 views

### Look for today() in range D5:AP5 is X in the range D8:AP8

I used this formula: - Look for "X" in range D8:AP8 = found/not found, which is fine. Formula: =IF(ISERROR(MATCH("X";NW_Calendar!D8:AP8;0));"Not found";"Found") but I would like to have it like ...
32 views

### How to get particular words to appear based on a number in another column

In Google Sheets: In column B, I want to display a certain word on each line, based on the value of column A. B2 gets it value from A2, B3 gets it value from A3 etc. range: 1500 triggers the ...
2k views

### How to return array of cells from a table in Google Sheets

We have # EXAMPLE TABLE with product SKUS on the top row, and the first columns is ingredients. Each SKU row is showing the value of the ingredient row. What we want to do is have another sheet with a ...
314 views

### Where do GOOGLEFINANCE((“CURRENCY:xxx xxx”)) takes the prices from?

I'm using the GOOGLEFINANCE((“CURRENCY:USDARS”)) function to convert US DOLLARS to ARGENTINIAN PESOS. I wanted to know where do Google take the prices from? I'm comparing with our local trade values ...
380 views

### Count blank cells in query formula

I am ranking answer for a form. and I came up with this formula =ArrayFormula(QUERY({'LEADs oGV'!U:U,LEN('LEADs oGV'!U:U)}, "select Col1, count(Col2) where Col2 > 0 group by Col1 order by count(...
3k views

### Compare single cell to multiple values

I have a cell in a spreadsheet that can have virtually any value. However, if that cell has one of four different values, I want a different cell to display certain text. Right now I have this: =IF(...
2k views

### Combining If And Or functions into one in Google Sheets

I'd like to build a formula that will state that if Cell C5 is equal to "Core Advocate" or "Advocate" or "Current Client" and if, at the same time, cell B5 is marked as "Yes," then the result needs to ...
801 views

### How to calculate variance with criteria?

I would like to know how to calculate variance (VAR, VARA, VARP) with criteria as in AVERAGEIF and AVERAGEIFS. The VARIF function does not seem to exist. This question also applies to others functions ...
62 views

### AND in ARRAYFORMULA giving error

I'm just getting myself confused here. I have a Planning spreadsheet with people's name in A, their skills in C, and their status in I. I would like to create another sheet within this spreadsheet ...
515 views

### Repeating unique data from a column in Google Sheets

I have a selection of values in Column A, some of which are repeated X (where X varies) times, like this: Apples Apples Oranges Pears Pears Bananas I want to end up with a list in another ...
251 views

### index(split(ImportXML formula works in a spreadsheet of mine, not in another?

=index(split(ImportXML("http://finance.google.com/finance?q=" & V9, "//td[@data-snapfield='latest_dividend-dividend_yield']/following-sibling::*"), "/"),1,2) / 100 This formula works in a ...
69 views

### How to extract the Account's Name according with the total of members? [closed]

For each relevant row I would like to have ColumnC populated with the value from ColumnA repeated (one above the other) in the C cell the number of times there are separate (one above the other) ...
70 views

### Sum data based on dates

I have a Google Sheet of data like this: 4/1/17 | 5 4/1/17 | 7 4/1/17 | 22 4/2/17 | 5 4/2/17 | 10 4/3/17 | 5 4/3/17 | 23 4/3/17 | 1 4/3/17 | 5 4/3/17 | 17 4/3/17 | 3 And I want some sort of formula ...
103 views

### Formula to compute the cardinality of the intersection of two sets of dates

Given: Two dates, the bounds of a date range; A list of dates (given in-extenso in a line or a column); Is there a way, in Google Spreadsheet, to get the number of dates (days) in the date range ...
22 views

### Choose “Type” of Input Number When Using Number Formatting

So I have the a formula that outputs a duration as seconds with three digits of milliseconds. For example if the formula outputs 67.769, It should be formatted as 01:07.769 I tried using mm":"ss"."...
1k views

### Google Sheets: How can I combine multiple ImportXML results for multiple URLs into a single column? [duplicate]

I want to grab some lists of data from various pages on the web and I'm using IMPORTXML to do it. Assume I have a list of URLs in column D. Depending on the URL, the length of the results can be ...
260 views

### Filling a column with a formula that references correct row without dragging down

In Google Sheets, I am trying to create a formula that will automatically apply to every cell in a column. The formula refers to another column of cells in a 1:1 way, that is S3 should refer to B3, ...
31 views

### Google Sheets If than statement doing whatever it wants?

B7 = A number B8 =IF(B7 > 1.25,((B7-1.25)*2)+1.25,IF(B7 < 1.25,1.25-((1.25-B7)*2),"1.25")) B9 =IF(B8 > 1.5,"1.5",IF(B8 < 0.5,"0.5",B8)) So basically, B7 is whatever number you want. If ...
39 views

### A formula must choose between three choices depending on a if/then statement

I am creating a formula that checks to see if a number is either above 1.25, below 1.25 or equals 1.25. Depending on which one it matches, a different formula will be applied. I understand​ that you ...
156 views

### Defining a range in a function with the value in a cell instead of a range

I have the following table and I'm trying to write a formula that will take the value of column C and put it into the COUNTIF function where the ?? is. A B ...
1k views

### Arrayformula for entire column in Google Sheets with a lookup not working

I have been working on automating a reporting sheet I have and discovered the very handy use of arrayformula in Sheets that autofills the column with your formula based on data in another column. ...
962 views

### Sum values by week using weeknumber

I am trying to calculate the hours for each week (starting Monday). What formula should I write to: retrieve the weekly sum of hours from another tab (based on week number)?
1k views

### Formula that displays the last non-zero value cell

I have a report that is able to pull/display values based on yesterday's values. Is there a way I can modify the formula to ignore cells that contain zeros "0s" and to continue to reference/pull the ...
119 views

### Reverse QUERY in chronological order from two standalone spreadsheets

I have a report that pulls data from a separate sheet using a QUERY. The report has a "notes" column (not part of the query) so users can add notes associated with a particular row. This solution ...
142 views

### Google Sheets - Nested If/Not Blank to Fill a Value

I have 6 columns for recording scores of clients. Column N: Intake Column O: 3 Months Column P: 6 Months Column Q: >6 Months Column R: Discharge/Drop Column S: Comparison Score* There will always be ...
1k views

### How can I stop apostrophes in names from tripping up formulae?

I'm using a formula to run through a list of names, compare them to a master list and check associated values for the names that appear on both. =COUNTA( Iferror(QUERY(Requirements!A2:F, "select A ...
3k views

### Google Sheets COUNTIFS if cell contains part of text, exclude blanks

I am unsure of the use of COUNTIFS with two variables while excluding blank cells from being counted in this scenario, wanted to see if there would be a more viable solution. I am relatively new to ...
1k views

### How to create a variable in Google Sheets

So, I want to create a variable for the purposes of updating it in one place and have it change everywhere where it is referenced on the sheet. For example. I want to associate a number with a day of ...
373 views

### Custom Number Format for scale-aware number formatting in Google Sheets (e.g. 1,200→ 1.2k; 1,200,000 → 1.2M; 1,200,000,000 → 1.2B)

Does anyone know a generalized Custom Number Format I can apply in Google Sheets such that numbers appear with it's scale operator and rounded to one decimal place? Example: I looked up a good ...
48 views

### Need to identify when a specific amount of time has past given a specific span of time

I have a start date and a payout date which is 70 days from the start date. Throughout the year I need to know when each time 70 days has past and when it does is that date between the start and ...
234 views

### Google Sheets - Sum formula that relates with one row to another

The title might sound confusing cause I don't know how to word it. Let start with the example So I have a spreadasheet with data First column shows Grade levels: Kinder, First, Second, Third.... The ...