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.

11
votes
1answer
4k views

Formula for smart, scale-aware number-formatting in Google Sheets? (e.g. 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: ...
3
votes
1answer
1k 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 ...
21
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, ...
4
votes
2answers
2k 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 ...
15
votes
4answers
21k 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 ...
53
votes
6answers
128k 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 ...
5
votes
3answers
12k 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,...
3
votes
3answers
3k 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 ...
3
votes
1answer
352 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 ...
3
votes
1answer
3k 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?
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 ...
15
votes
3answers
82k 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' ...
3
votes
3answers
2k views

Import current price from website to spreadsheet

As you can probably gather from the title, the goal is to plug the current price of this product into a spreadsheet. Perhaps I'm over-complicating things, but there must be an easy way to do this. ...
3
votes
2answers
2k views

Running Total In Google Sheets with Array

QUESTION: How do I create a running balance that has the following criteria: It uses an array formula the rows don't need to be sorted by date, but running total only returns balance of: Rows with ...
4
votes
3answers
20k 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))) ...
1
vote
1answer
1k 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 ...
14
votes
2answers
17k 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 ...
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 ...
3
votes
1answer
71 views

Complicated formula nesting issue in Google Spreadsheets

I'm currently trying to develop a complicated formula for a work project and I've hit a roadblock. This spreadsheet tracks people’s physical performance in 5 specific areas during a physical ...
2
votes
2answers
4k views

Time duration formatting in Google Spreadsheets

A couple issues here: I have a formula that is trying to determine if a time duration (ex 7:51) is greater than or less than a baseline duration value. When typing the formula I have to put the ...
2
votes
1answer
4k views

Validation based on multiple sheets

I have two sheets that each contain a column of data that I would like combined and used as a validation list for a column in another sheet. What would the formula look like for this?
1
vote
1answer
245 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 ...
1
vote
1answer
440 views

Dynamic updating referenced cell from another workbook after adding/deleting rows using IMPORTRANGE - use ARRAY or VLOOKUP?

I add/deleted/move rows frequently in my workbooks. When I add/remove rows from Spreadsheet#1 ("S1"), I need the referenced cell in Spreadsheet#2 ("S2") to dynamically update so it still references ...
1
vote
1answer
690 views

How can I add hours in Google Sheets to show days?

I have 2 cells in Google Sheets with values: 22:00:00 22:00:00 and I have a cell which has the function sum to sum these values and it shows the value: 20:00:00 I am guessing it is trying to tell ...
1
vote
1answer
9k views

Take SUM of colored cells in a column in Google Spreadsheets?

I have a column of numbers, where I've changed the background color of some of the cells to blue. All of the other cells in the column have the regular white background. Is it possible to create a ...
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
2answers
22 views

ArrayFormula for consequential sum-up?

Here's a sample test spreadsheet - https://docs.google.com/spreadsheets/d/ What do I have to do in the ONE cell in column D such that I won't have to copy and paste the same formula in column C ...
0
votes
1answer
56 views

Complicated formula nesting issue in Google Spreadsheets pt2

I'm currently trying to develop a complicated formula for a work project and I've hit a roadblock. This spreadsheet tracks people’s physical performance in five specific areas during a physical ...
0
votes
1answer
31 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 ...
0
votes
1answer
87 views

JOINing code-points after CHAR conversion in Google Spreadsheets

I have a spreadsheet with hexadecimal Unicode code-points in one column. Usually it’s just a single number, which I can convert to a character with =CHAR(HEX2DEC(XY123)). In some cases, though, there ...
0
votes
1answer
14 views

Alternate to nested if statements (alternate posing of question)

Sample Data Sheet: https://docs.google.com/spreadsheets/d/1EIQcpzwdaoNSKEPu-F1NCUOtEjS6Ez0P73dmotClT7Q/edit?usp=sharing The end goal is to have a Month + Year show up in a single cell, based on the ...
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 ...
-1
votes
1answer
52 views

Importxml convert content only

I am getting the price value from the website. I would like to make the value as static(remove formulas) to prevent auto refresh in sheet due to that the price values will change-- I don't want it. ...