Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange
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.

0
votes
1answer
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. ...
0
votes
1answer
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 ...
1
vote
1answer
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?
-1
votes
1answer
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 ...
4
votes
2answers
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 ...
0
votes
1answer
28 views

Formula Sum = 0

The sum still comes up zero and I have other sheets that are not having this issue:
0
votes
1answer
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, "") ...
2
votes
2answers
122 views

How to make IF function in Google Spreadsheets output nothing?

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 ...
1
vote
2answers
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 ...
1
vote
1answer
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/...
0
votes
0answers
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 ...
2
votes
1answer
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 ...
0
votes
3answers
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","...
1
vote
1answer
92 views

Google Spreadsheets SUMIF look to previous row

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 ...
2
votes
1answer
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 ...
2
votes
1answer
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 ...
1
vote
2answers
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, [...
-1
votes
1answer
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 ...
0
votes
1answer
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 ...
1
vote
1answer
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 ...
1
vote
1answer
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 ...
1
vote
1answer
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(...
2
votes
2answers
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(...
1
vote
1answer
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 ...
3
votes
1answer
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 ...
3
votes
1answer
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 ...
1
vote
2answers
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 ...
-1
votes
1answer
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 ...
-2
votes
1answer
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) ...
1
vote
1answer
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 ...
1
vote
1answer
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 ...
0
votes
1answer
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"."...
1
vote
1answer
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 ...
0
votes
1answer
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, ...
1
vote
1answer
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 ...
2
votes
1answer
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 ...
1
vote
1answer
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 ...
1
vote
2answers
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. ...
3
votes
2answers
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)?
1
vote
1answer
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 ...
0
votes
3answers
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 ...
1
vote
1answer
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 ...
2
votes
1answer
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 ...
0
votes
3answers
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 ...
0
votes
1answer
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 ...
3
votes
1answer
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 ...
1
vote
2answers
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 ...
3
votes
2answers
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 ...
0
votes
2answers
512 views

Adding row to Google Sheets updates SUM formula - I don't want this

I'm using an online third-party form to collect data, that data gets submitted to Google Sheets. I applied a sum formula to calculate the sum of a column =SUM(A2:A50). A1 is used for header. Now, when ...
2
votes
1answer
307 views

Reference Gmail labels in Google Sheets

We have a shared Gmail account for our support team. That inbox has been given labels including a set which lists the email's priority level 0-5. To keep track of the progress we use a Google sheet ...