Questions tagged [formulas]

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

Filter by
Sorted by
Tagged with
2 votes
1 answer
3k views

Finding the longest time duration in Google Sheets

I'm trying to find the longest time value (hh:mm:ss) and I'm looking for it in column I, starting at row 5. I've already tried using the LARGE function but that outputs a decimal, not a time. =LARGE(...
Camuise's user avatar
  • 31
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
4 votes
3 answers
7k views

Forms Data Manipulation In Google Sheets

I'm trying to develop a solution using Google Forms with Google Sheets that will collect Google Forms data and then automatically divide that one form submission's data into multiple rows of data in ...
Mr. B's user avatar
  • 2,867
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
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
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
3 votes
5 answers
3k views

multiple IF statements with between number ranges alternative

How to efectively deal with multiple IF-statements and with multiple "between number" ranges in Google Sheets' spreadsheet (e.g. C column based on B column) with a rule set as follows: 01-10 11-20 ...
user0's user avatar
  • 26.5k
4 votes
2 answers
15k views

Image in a cell with a hyperlink

I need a cell that contains an image to also contain a hyperlink. Following formula is the only one I found but doesn't work (error analyzing formula) =HYPERLINK("URL", image("URL")) Following ...
Marek Marhoul's user avatar
3 votes
3 answers
34k views

Google Sheets QUERY won't display cell text if other cells have numbers

I'm not sure what is happening in my Google Sheet query and could really use some help. I have a quick screencast that demonstrates and explains the issue. Basically, I'm using a query to pull data ...
Mr. B's user avatar
  • 2,867
1 vote
2 answers
7k views

Google Sheets, formula, reference to variable sheets

Google says in their web help how to reference data from another sheet, but what happens if what I need is to make the reference depending on my fields on the current sheet? Let's say I have a sheet ...
Joan Carles's user avatar
151 votes
9 answers
761k 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
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
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
6 votes
2 answers
13k views

Show all combinations for a selection of columns

I have a sequence of letters, in Google Spreadsheets: | A B C D E F --|---------------------- 1 | U Y 0 4 X N 2 | H X Y 3 | W The first row contains a character ...
yellow-saint's user avatar
4 votes
2 answers
5k views

Use of importdata with arrayformula

I’m using importdata to retrieve Yahoo stock information, which works fine on individual rows, but when combined with arrayformula to fill down the page when additional symbols are added does not ...
Chris's user avatar
  • 49
2 votes
1 answer
375 views

Formula breaks subtly in ARRAYFORMULA in Google Sheets

I have a spreadsheet document with a formula comparing a list of URLs to lists on two other sheets within the document. I desire to know if each URL on the first list is present on the blocklist, ...
Clay's user avatar
  • 23
2 votes
2 answers
2k views

Google Sheets Split row in multiple rows

I'm doing a spreadsheet for an event and I need to split a row into multiple rows. It's getting data from a form. I need a script that can arrange the data like in the blue rectangle.
Comunicação Andreia's user avatar
1 vote
2 answers
2k views

Conversion from Excel to Google Spreadsheet - sum across sheets

I'm importing an Excel spreadsheet into Google Drive. Its a huge spreadsheet, and overall Google spreadsheets has converted everything, except the following: There are a few cells with formulas like ...
Djave's user avatar
  • 746
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
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
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
8 votes
7 answers
40k views

LEFT JOIN two and more tables in Google Sheets

Say I have two tables. Table A (material requirements): product_id | material | required 1 | A | 0.5 1 | B | 0.7 2 | A | 0.2 3 | A | 0....
emihir0's user avatar
  • 197
7 votes
3 answers
29k views

Find the closest value (equal or greater) in a range in Google Spreadsheets

I have a range with a list of values (depths). I'd like to be able to select the row where the depth matches a value in a cell I provide. If no value matches, the greater value should be selected. ...
Simone Carletti's user avatar
6 votes
1 answer
6k views

Google Spreadsheets: IF not working

I have problems with my IF command in Google Sheets. If gives #ERROR! all the time. See this simple test: Not even if I put in the number directly and avoid references: Am I using the IF wrong? Or ...
Steeven's user avatar
  • 600
5 votes
1 answer
17k views

Duplicate and rename sheet in one action on Google Sheets

I have a script which references the name of a sheet in Google Spreadsheets. When I duplicate a sheet with a cell that uses that function, the function is called right away and returns something like ...
Daniel's user avatar
  • 375
2 votes
3 answers
8k views

QUERY error for BY column

I'm using the following query to pull data from one sheet into another. =query(data!B2:CL,"select B, C, D, E, F, G, H, I, J, BB, BZ, CA, BA where J = 'Industry Certification'",false) The above query ...
Mr. B's user avatar
  • 2,867
2 votes
1 answer
8k views

Partial cell formatting with CONCATENATE in Google Sheets

Is there a way to format partially using CONCATENATE in Google Sheets? For example, =CONCATENATE("*", Sheet1!A1, Sheet1!B1), and I want to make the * bolded or blue in color or something else. I ...
user650654's user avatar
1 vote
1 answer
3k views

ArrayFormula to compute running average for groups of rows

Is it possible to write an array formula that calculates the running average of the Amount column for each of the Name groups in the following sheet? The rows are sorted by Name. In Column C, I have ...
DwightM's user avatar
  • 13
0 votes
2 answers
2k views

How do I find the address of last non-blank cell in a row (while skipping columns)

Basically, I need to do what the title says on my spreadsheet here. So for row 3, I need to find the address of the last non-blank cell in that row, while skipping the grayed out columns (with the ...
TheMrIggs's user avatar
39 votes
3 answers
83k 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
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
28 votes
5 answers
49k 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
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,151
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
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
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
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
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
8 votes
2 answers
27k 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 | --+-----...
rossjkw's user avatar
  • 81
7 votes
2 answers
14k 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 ...
user avatar
6 votes
2 answers
6k views

Add missing dates to list of dates with values

Current Data: 9/20/2018 6 9/22/2018 3 9/23/2018 1 Desired Output: 9/20/2018 6 9/21/2018 9/22/2018 3 9/23/2018 1 I would like to have the missing dates added to Column A while the ...
Travis Manning's user avatar
5 votes
1 answer
11k views

Cell row reference coming from another cell [duplicate]

I need to make Google Sheets evoke the row number for a cell reference by pointing to another cell. I have a form with different cells that point to another sheet (Sheet1!). All the cells always point ...
Fernando's user avatar
5 votes
4 answers
47k views

COUNTIF but when multiple conditions are met

I'm trying to create a GDocs spreadsheet to collate data from a survey. I'm fairly new to the advanced features of GDocs/Excel but here's what I'd like to achieve. I've tried Googling but it's a tough ...
Tom Maitland's user avatar
3 votes
3 answers
4k views

How to apply a function to all the rows preceeding the current one in an arrayformula?

So I have the following situation: In the first column I have certain values, in the second column, given a certain row, I want it to contain the result of a function applied to all the preceeding ...
Fabio A.'s user avatar
  • 139
3 votes
2 answers
2k views

How can social hashtags be extracted from a cell?

Example: A2 -> What a beautiful day! #sunday #summer B3 -> #news Stay tuned for our next contest! #something Desired output on B2 ->#sunday #summer Desired output on B3 ->#news #...
Renan's user avatar
  • 51
3 votes
1 answer
2k views

Do named ranges not work under certain conditions?

Been tearing my hair out today with this one. I'll be as concise as I can. I have a number of named ranges, and I would like to reference them in a long, ugly formula that works without them just ...
JVC's user avatar
  • 1,236
3 votes
2 answers
2k views

Convert columns of ISO 8601 strings to dates in Google Sheets

I'm pulling data to my sheet from an API using an ImportJSON function. The data array contains mixed data, including several columns of ISO 8601 date strings in UTC. For example: 2020-06-02T00:00:00Z ...
Julius's user avatar
  • 243
2 votes
1 answer
6k 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 ...
Fauxcuss's user avatar
  • 223
1 vote
1 answer
4k views

How to automatically set date in a cell when a different cell value is changed by a formula from data on another sheet?

I currently have a column, e.g. ColB, containing SUMIF functions with its data range from other sheets and a column, e.g. ColC, with dates that I want to automatically update to the last date ColB's ...
Fred's user avatar
  • 11