Questions tagged [formulas]
Formulas used in spreadsheets edited by web applications like Excel Online, Google Sheets, among others.
1,546
questions
82
votes
8answers
412k views
Google Sheets formula for “if contains”
I'm trying to figure out how to identify IF a list of items in one cell contains a value or string.
EXAMPLE
Cell A1 contains sites, sheets, docs, slides.
I want cell B1 to display a 1 'if' cell ...
57
votes
6answers
131k views
ISBLANK() function
Is there any way I can check whether a cell is empty?
There is a function ISBLANK() but it returns false when there's a formula in a cell even thought the formula doesn't return anything (or an empty ...
39
votes
4answers
31k views
Cumulative Sum without Script
For this question, I've created a Google Apps Script function, that calculates the cumulative sum of a range. Martin Hawksey on Google+ made a comment about a more efficient way of calculating the ...
36
votes
4answers
71k views
Add 3 hours to Date value (DD/MM/YYYY 00:00:00)
I have cells with dates in this format: DD/MM/YYYY 00:00:00 and I want to add 3 hours because I'm in a different GMT zone.
Is possible to use dates in a formula?
Something like: 30/12/2012 22:15:00 ...
36
votes
7answers
50k views
Stop a '+' from generating a formula
I'm having a bit of a struggle. I'm creating a spreadsheet which uses plus signs (+) regularly. I want a semi-permanent fix for +'s turning into addition formulae.
34
votes
2answers
68k views
FILTER(condition OR condition) syntax?
I am using FILTER() to display a value from Sheet 2 where the Name columns match.
My problem is where I want:
=FILTER(Sheet2!A:F, Sheet2!A:A="Combat Medic" OR Sheet2!A:A="Universal")
as OR results ...
26
votes
5answers
38k views
Concatenating only filled cells
I currently have a spreadsheet to keep track of scores in a card game. There can be between two and five players. I have the following:
| Players |
|----------|
| Dave |
| Paul |
| John ...
26
votes
9answers
155k views
How to Fill a Column with Sequential Dates in Google Sheets
In Excel, you can right-click drag a date downwards to fill a column with the next dates in the sequence. For example, if you enter 9/1/2014, select that cell, then right-click drag down over the ...
26
votes
2answers
87k views
IF OR Statements
How do you do an IF ... OR ... THEN statement in a Google spreadsheet? In PHP I would do this:
if($C7 == "U.S.A." || $C7 == "Canada"){ ...
How to do the same as a spreadsheet formula?
At the ...
25
votes
3answers
23k views
Short number format in Google Sheets; 1,024 ā 1K, 1,816,724 ā 1.8M
Any recipes out there for a "smart" number formatting formula for Google Sheets that's "scale-aware," a la Rails ActionView's distance_of_time_in_words method?
I would expect to enter a number like: ...
22
votes
3answers
9k views
Complete reference documentation for Google Spreadsheets formulae?
As a programmer, I find myself somewhat frustrated when trying to use Google Spreadsheets, because there seems to be no definitive description of the syntax that I can use for formulae. For instance, ...
21
votes
2answers
44k views
How do I output a string containing quotation marks?
I am not super computer literate. I am trying to use the equations to grade my Google quiz, the problem is that there is a quote in the answer. Is there a different symbol I need to use?
This is what ...
20
votes
5answers
40k views
Can I use column headers in a =QUERY?
Reading the documentation for the =QUERY function1, 2, 3, some of it seems to imply that I should be able to use column headers directly in my query. For example, the =QUERY function takes a third ...
19
votes
1answer
55k views
How do I combine a cell value with a string in a Google Spreadsheets formula?
I have the following IF condition in some cell:
=IF(A1>A2, "value is <C5>", "value is <D5>")
where <C5> and <D5> should be the values of cells C5 and D5.
I know it is a ...
18
votes
4answers
25k views
How to get the intersection of two sets
I have two lists (unique sets) in a spreadsheet and I want to get the intersection of the two.
Is there an easy way to do this?
Here's an example of what I want to achieve:
List 1 List 2 ...
14
votes
5answers
27k views
Perform basic maths on contents of Google Document (not spreadsheet)
In Microsoft Word, it is possible to use very simple formula e.g. =SUM(ABOVE) to display the total of a column or =SUM(LEFT) etc. Is it possible to do something similar in Google Docs? Obviously this ...
13
votes
7answers
35k views
How to stop NOW function from auto-updating?
I'm using Google Spreadsheet's NOW() function as a shortcut to insert the current timestamp now.
However, the value in the cell changes whenever the spreadsheet is modified in any way, even when the ...
13
votes
3answers
4k views
Looking for an “isFormula” formula for Google Spreadsheets
Is there a function that will tell if a specific cell has a plain (manually entered) value or has a formula?
13
votes
2answers
13k views
Sum of all cells in Google Spreadsheet where matching row fits a value
I want to return the sum of all cells in ColumnA only if the value of a cell on the same row is equal to a specific value.
For example, sum C4 if D4 = "Bob". Assuming there are six rows whereby "Bob"...
12
votes
2answers
55k views
Calculating shaded cells
I am using Google Spreadsheets to create a matrix (time/activity) as a daily schedule for staff to sign into.
Is there a formula to calculate the number of cells in a column that are shaded (or not ...
12
votes
1answer
37k views
How to use ARRAYFORMULA with QUERY in Google Sheets
I have a query that needs to be run for every row in a list. It works nicely when written for a single row:
QUERY(MaraRankData,
"select J,I,H,G,F,E where
A='"&E3&"' and
B='"&...
12
votes
5answers
58k views
Conditionally formatting a cell based on another cell's value in Google Spreadsheet
I did some searching on this and came across this post.
However, this solution uses an onEdit() trigger. I would like to see a solution that uses a formula approach, so in the OP's example from that ...
11
votes
4answers
33k views
Is there a way to generate number of days in a month?
The title kind of says it all but just to give you the use case, I have a spreadsheet where I track monthly advertisement revenue and want to end up with a daily revenue column.
Here's a simplified ...
11
votes
5answers
17k views
Change date format [YYYYMMDD] to [MM/DD/YYYY]
I generated a report that formats date as 20160509 and my Google Sheets locale is already set to United States. I've played around with various options within Format > Number > Date Format but I can't ...
11
votes
4answers
2k views
Average of last 7 non-empty non-blank cells in Google Sheets
I have numbers in range D7:D and there are times when some cells are empty. I want the average of the last 7 numbers but to skip blanks. So if in the last 7 there's only 3 that are full, I want it to ...
11
votes
4answers
12k views
How to write AverageIf (Not an Empty String) in Google Sheets?
I have the answer to this question. However, it took me a few wasted hours of research and work to figure it out. I couldn't find an answer anywhere online, so I thought I'd write out a question and ...
10
votes
3answers
23k views
How to interpolate data in a range in Google Sheets
I have an array with data:
X Y
3 50
5 60
9 120
11 130
18 90
20 150
The data is entirely non-linear. X is guaranteed to be sorted.
Now for any given value,...
10
votes
2answers
40k views
How to skip blank cells when copying a range from source to destination by Formula
I have three levels of hierarchical data in Google Spreadsheets that looks something like this:
As data is being entered in here, I want it collect in three separate sheets like this:
The ...
10
votes
3answers
12k views
Which formula extracts the domain name from an email address (bob@example.com -> example.com)?
Which Google Spreadsheets formula do I use to get the domain part from an email address?
Example: bob@example.com → example.com
10
votes
1answer
10k views
Select subrange of an array-returning formula like GOOGLEFINANCE()
In Google Sheets, GOOGLEFINANCE returns an array of values, like so:
The formula is entered in A1 but the result extends into other rows and columns. I would like to "select" (not sure what the exact ...
10
votes
1answer
8k views
Retrieve a cell having a column and a row header in Google Spreadsheets
I have a spreadsheet in Google Docs like this:
Johan Mark Gil Pablo
Course 1 45 50 80 50
Course 2 250 300 50 270
Course 3 19 50 ...
9
votes
2answers
20k views
Adding one column to a string to dynamically create a URL in the next column
I have a spreadsheet of product ID codes that I'd like to turn into links, and from everything I've read, the concat or concatenate functions in Google Sheets should work for that, but so far ...
9
votes
3answers
32k views
How can I create a unique list of data across multiple rows AND columns?
Google Sheets has a UNIQUE() function which when given a column of values, will output a column of all unique values.
This works well for column-specific data, however, you have multiple columns and ...
9
votes
3answers
21k views
Are Excel formulas and Google Spreadsheet formulas the same?
I want to be sure all my Google Spreadsheet work can be converted OK to Excel. How many of the Excel formulas match the formulas of Google spreadsheets?
9
votes
5answers
42k views
Count the number of words in a string in Google Sheets
I have a simple Google spreadsheet with a number of text strings. I just want the number of words contained in each cell. Is there an in-built Google spreadsheet function I may use?
9
votes
3answers
40k views
Return a blank when cell reference is blank
I am using the following formula in a Google Sheets to calculate the number of days between two dates (dates are in columns G and AL):
=ARRAYFORMULA(if(row(A:A)=1, "Readmission Days", (G:G)-(AL:AL)))
...
9
votes
5answers
23k views
Split content of one row into multiple rows?
I have a table where each row contains data like this:
Team name | First member | Second member | Third member | Team description
Would it be possible to transform/break this data so that each row ...
9
votes
4answers
17k views
Compare two lists and display missing items in a Google Spreadsheet
I'm trying to find a solution to compare two separate lists (invited - attended) and create a 3rd list (absent).
The function must look at the key in "Attended List (D,E,F)" and compare it with the ...
8
votes
3answers
17k views
A spreadsheet formula to assign letter grades based on numeric ranges
I'm trying to figure out how to write an IF statement that will be 'true' if a value is in-between two values.
Example:
If value is between 60 and 69.9 return "D"
If value is between 70 and 79.9 ...
8
votes
2answers
27k views
Ignore formula if cell is blank
I'm attempting to create a formula that essentially counts the days between today's date and the date entered in a cell. So far I believe it works. See below:
=DATEDIF(H3, Today(), "D")
My dates ...
7
votes
5answers
41k views
Is there an option to add a time picker on Google Sheets?
I don't need timezones or anything complicated, just a string on the HH:MM format, that matches the custom format available. I don't need days, months or anything like that, just the time.
7
votes
2answers
11k views
How do you replace a formula with its result?
I created a Google Spreadsheet that is made up of 20+ sheets (all formatted the same way). The company I work for loads containers. One of those sheets calculates a particular parameter on each of ...
7
votes
3answers
12k views
Compare each cell of two rows
I am working on a Google Doc spreadsheet using a not-so-simple formula. The formula works but is not as I want it. What I want to achieve is:
"Compare each cell from T3 to AM3 to each cell from T2 ...
7
votes
1answer
938 views
Google Forms erroneous deletes rows in second sheet
I created a form in Google Docs with quite some questions. For a good overview I created a second sheet to pick the important parts from the survey. There I'm also able to add extra columns to add ...
6
votes
1answer
3k views
How to refer to a cell in the same column at given row?
I would like to get a cell in a particular row above the current cell.
This is to be used in conditional formatting, so I can't specify a particular cell, e.g. A$4āthe reference would be the same for ...
6
votes
1answer
14k views
How do I multiply column values?
I simply want to multiply column h with column j, and store the result in column k in each row.
What do I type in as the formula to do this?
6
votes
2answers
8k views
How to check if a cell contains any of an array of values?
TL;DR: Using SEARCH(search_for, text_to_search), "search_for" is a variable-length array.
On Google Sheets, I have four cells, E1:E4. The values in each cell are as follows:
| E |
--+-----...
6
votes
2answers
10k views
Query Multiple Ranges with Results in Row
I am trying to query five ranges with the results displayed across a single row. To do this, I was using { } with commas separating each range, but it's only giving me the first range in return.
...
