Questions tagged [formulas]
Formulas used in spreadsheets edited by web applications like Excel Online, Google Sheets, among others.
183
questions
1
vote
1
answer
2k
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(...
- 21
42
votes
6
answers
36k
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 ...
- 22.8k
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 ...
- 2,857
48
votes
9
answers
87k
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.
- 585
12
votes
4
answers
12k
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?
- 155
9
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?
- 227
2
votes
5
answers
2k
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
...
- 26.2k
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 ...
- 183
3
votes
3
answers
33k
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 ...
- 2,857
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 ...
- 11
150
votes
9
answers
747k
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 ...
- 2,857
34
votes
12
answers
77k
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 ...
- 17.1k
14
votes
4
answers
43k
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,...
- 733
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 ...
- 171
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 ...
- 49
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.
2
votes
1
answer
371
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, ...
- 23
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 ...
- 746
36
votes
5
answers
58k
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: ...
- 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, ...
- 527
10
votes
6
answers
30k
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 ...
- 1,864
8
votes
7
answers
39k
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....
- 197
7
votes
3
answers
28k
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.
...
- 1,294
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 ...
- 560
5
votes
1
answer
16k
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 ...
- 365
2
votes
1
answer
7k
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 ...
- 121
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 ...
- 2,857
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 ...
- 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 ...
- 27
37
votes
3
answers
79k
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 ...
- 411
36
votes
9
answers
255k
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 ...
- 521
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.
- 481
27
votes
5
answers
47k
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 ...
- 403
24
votes
9
answers
65k
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 ...
- 6,043
15
votes
6
answers
30k
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 ...
- 253
14
votes
3
answers
67k
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 ...
- 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 ...
- 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)))
...
- 111
10
votes
7
answers
29k
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 ...
- 209
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 ...
Edward
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 ...
5
votes
4
answers
46k
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 ...
- 153
5
votes
1
answer
10k
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 ...
- 51
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 ...
- 139
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
...
- 243
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 #...
- 51
3
votes
1
answer
1k
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 ...
- 1,192
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 ...
- 223
1
vote
1
answer
2k
views
Return cell reference containing closest value
I'm trying to get a spreadsheet to tell me which cell(s) contain values closest to a value I provide. Without going into too much detail it's for a golf game I play, and I've done some data collection ...
- 11
1
vote
1
answer
845
views
How to create an automatically updating Google spreadsheet with information from Steam?
How would I create an automatically updating Google spreadsheet containing information about Recent Activity in Steam?
Table must look like here:
But the hours column must automatically updating ...
- 25