Questions tagged [formulas]

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

Filter by
Sorted by
Tagged with
151 votes
9 answers
762k views

Google Sheets formula for "if contains"

I'm trying to figure out how to identify IF a list of items in one cell containing a value or string. EXAMPLE Cell A1 contains sites, sheets, docs, slides. I want cell B1 to display a 1 'if' cell A1 ...
Mr. B's user avatar
  • 2,867
58 votes
6 answers
132k 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 ...
Robert Koritnik's user avatar
48 votes
9 answers
91k 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.
David Wheatley's user avatar
45 votes
4 answers
88k 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 ...
Santiago's user avatar
  • 561
43 votes
6 answers
37k 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 ...
Jacob Jan Tuinstra's user avatar
39 votes
3 answers
84k 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 ...
Rushmik's user avatar
  • 431
37 votes
9 answers
266k 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 ...
stim's user avatar
  • 531
36 votes
5 answers
61k 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: ...
jm3's user avatar
  • 465
36 votes
13 answers
80k 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 ...
Vidar S. Ramdal's user avatar
34 votes
5 answers
60k 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 ...
Djave's user avatar
  • 746
32 votes
4 answers
19k views

How do I convert a cell to RFC 3986 standard (URLEncoded) in Google Spreadsheets?

Is it possible to convert text from a certain cell to RFC 3986 (http://url-encoder.org/) in a Google Spreadsheet? Lets say C4 has the value John Doe, then D4 must be John%20Doe.
Robin Alexander's user avatar
30 votes
1 answer
107k 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 ...
idanshmu's user avatar
  • 403
28 votes
5 answers
50k 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 ...
NoamNelke's user avatar
  • 413
28 votes
3 answers
73k 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 ...
Aubrey's user avatar
  • 281
26 votes
2 answers
88k 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 ...
Djave's user avatar
  • 746
24 votes
10 answers
67k 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 ...
Pacerier's user avatar
  • 6,161
23 votes
3 answers
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, ...
rog's user avatar
  • 527
21 votes
1 answer
79k 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='"&...
Stobber's user avatar
  • 365
17 votes
5 answers
68k 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 ...
Moiz Tankiwala's user avatar
17 votes
4 answers
27k views

How to add a note or comment into a formula in Google Sheets?

Microsoft Excel Microsoft Excel has this feature where one can add a note into a formula with the N() function. It looks like this: Source: Add Comments to a Formula in Excel for Your Future ...
Lernkurve's user avatar
  • 1,820
17 votes
6 answers
44k 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 ...
Jonathan Day's user avatar
16 votes
4 answers
47k 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 ...
Andrew G. Johnson's user avatar
15 votes
1 answer
20k 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 ...
Timst's user avatar
  • 559
15 votes
6 answers
31k 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 ...
lineplay's user avatar
  • 253
15 votes
4 answers
27k 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 ...
Mr. B's user avatar
  • 2,867
15 votes
2 answers
20k 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"...
stephen gilliss's user avatar
14 votes
3 answers
70k 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 ...
RLH's user avatar
  • 297
14 votes
4 answers
45k 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,...
EboMike's user avatar
  • 743
14 votes
2 answers
10k views

Combine Custom Formula with Colour Scale in Conditional Formatting

In Conditional Formatting in Google Sheets, I'm wondering if it is possible to combine the Format Cells if - Custom Formula is with a Colour Scale? It would appear not, but is there a clever ...
TopMarx's user avatar
  • 143
13 votes
2 answers
62k 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 ...
salgire's user avatar
  • 131
13 votes
5 answers
13k views

Combine two columns into a list of all possible combinations of entries

I have two columns such as: a 1 b 2 c and I need to combine them like: a 1 a 2 b 1 b 2 c 1 c 2 Is it possible with a formula?
offa's user avatar
  • 165
13 votes
3 answers
5k 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?
epeleg's user avatar
  • 253
12 votes
6 answers
93k 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.
braindamage's user avatar
12 votes
4 answers
19k 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 ...
Alamb's user avatar
  • 367
12 votes
1 answer
15k views

HAVING clause in Google Sheets QUERY

My Problem I have built a count query that returns the most common elements from column H in the final sheet: =QUERY(final!H2:H,"SELECT H, COUNT(H) GROUP BY H ORDER BY COUNT(H) DESC") I would like ...
Adam Matan's user avatar
  • 1,813
12 votes
4 answers
4k 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 ...
Spongebob Squarepants's user avatar
12 votes
2 answers
12k 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 ...
user12248's user avatar
  • 121
12 votes
5 answers
59k 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 ...
void.pointer's user avatar
11 votes
4 answers
23k views

Is there any way to use a formula in a Google Sheets filter view?

I have a sheet with a column of numbers. I'd like to create a filter view that only shows rows with a value greater than 500, but I can't find any way to do that. There are lots of different numbers, ...
Michael's user avatar
  • 212
11 votes
4 answers
56k 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))) ...
stephanie's user avatar
  • 111
10 votes
2 answers
21k 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 ...
Kzqai's user avatar
  • 248
10 votes
3 answers
20k views

Which formula extracts the domain name from an email address ([email protected] -> example.com)?

Which Google Spreadsheets formula do I use to get the domain part from an email address? Example: [email protected] → example.com
nic's user avatar
  • 8,754
10 votes
3 answers
24k 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?
b_dev's user avatar
  • 247
10 votes
5 answers
46k 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?
user avatar
10 votes
2 answers
24k views

Comparing times on Google spreadsheet

I want to check if a set of fields indicate times of the day that are before 11am. The dates are stored in this format: 8:42:00 AM I tried A1<"11:00:00 AM" but it always returns True, even if it ...
Mohamed Khamis's user avatar
10 votes
2 answers
33k 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 ...
WSG's user avatar
  • 101
10 votes
6 answers
31k 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 ...
Dom Delimar's user avatar
  • 1,864
10 votes
1 answer
4k 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 ...
Ondra Žižka's user avatar
10 votes
7 answers
30k views

Function to find distance between cities

I'm working with Google Docs and want to create a spreadsheet of the following form: Point A Point B Mileage Los Angeles Miami 292100 Palo Alto San Francisco ...
Nick Heiner's user avatar

1
2 3 4 5
42