Questions tagged [formulas]

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

Filter by
Sorted by
Tagged with
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
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.
4
votes
3answers
5k 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 ...
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?
1
vote
4answers
962 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 ...
5
votes
1answer
6k 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?
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 ...
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,...
5
votes
2answers
9k 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 ...
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 ...
4
votes
1answer
3k 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 ...
1
vote
2answers
7k 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 ...
2
votes
1answer
291 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, ...
2
votes
3answers
16k 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 ...
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 ...
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, ...
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 ...
2
votes
3answers
3k 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 ...
5
votes
1answer
4k 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 ...
1
vote
2answers
5k 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 ...
0
votes
2answers
925 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 ...
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 ...
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 ...
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 ...
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 ...
1
vote
1answer
2k 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 ...
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 ...
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))) ...
3
votes
3answers
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 ...
4
votes
4answers
17k 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....
3
votes
1answer
871 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 ...
2
votes
1answer
4k 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 ...
1
vote
2answers
231 views

Export PDF link sheet ID auto change

I have the link for export as PDF a specific range on a sheet =HYPERLINK("*****export?exportFormat=pdf&format=pdf&gid=962914045&range=A1:H68&pagenumbers=false&gridlines=false&...
0
votes
1answer
98 views

applying a formula to the data from google forms

I have a google spreadsheet which is connected to a google forms I wanted one formula to be automatically copied to the new lines(Column R) when google form response is submitted the spread sheet: ...
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 ...
5
votes
4answers
35k 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 ...
4
votes
2answers
2k 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 ...
4
votes
1answer
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 ...
1
vote
1answer
2k 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 ...
0
votes
1answer
53 views

Problem with Nested IF() statement on google sheets

I am trying to create a formula for a monthly expense report using the following nested IF() statement: =IF(B2="Weekdays", C2*20, IF(B2="Monthly", C2, IF(B2="Weekly", C2*4, IF(B2="Bi-weekly", ...
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 ...
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 ...
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 ...
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='"&...
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"...
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 ...
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?
3
votes
2answers
4k 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 ...
5
votes
3answers
6k views

Sorting Google spreadsheet by month and day but not year

I made a Google spreadsheet with a list of birth dates, anniversaries, etc. that I would like to sort by when they occur in the year, not when they first happened. If you sort by the date normally, it ...