Questions tagged [formulas]
Formulas used in spreadsheets edited by web applications like Excel Online, Google Sheets, among others.
1,840
questions
2
votes
3
answers
2k
views
A formula that does Excel "countifs" function involving dates
I have a Google spreadsheet in which multiple users are tracking a list of dates and class names (math, reading, writing, etc). On a separate sheet in the same document, I want to be able to give a ...
- 29
43
votes
4
answers
81k
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 ...
- 541
1
vote
1
answer
2k
views
Nested IF statement on Google Spreadsheet, second part same as the first
I have a spreadsheet for my budget. Payments are either drawn from my bank or my Amex card and then my Amex card is drawn from my bank as well. So I add up all my monthly total like this:
=sumif(I3:...
- 368
0
votes
2
answers
4k
views
Sum of numerical values excluding formulas in a Google Spreadsheet
I have a long column of numbers in Google Spreadsheet, and occasionally there exists a cell that sums some of the cells below it. For example:
=SUM(2:4)
5
10
30
=SUM(6:8)
40
10
10
I'...
Mario
0
votes
2
answers
4k
views
Is there a limit on functions in Google Spreadsheets? [duplicate]
As to answer a question on Super User, I was making a sample spreadsheet on Google Docs. I made it first in Excel 2010 on my computer to copy/paste it later on. When I tried to copy a formula that ...
- 111
2
votes
1
answer
6k
views
JOIN formula in Google Spreadsheets
I want to use the JOIN formula in Google Spreadsheets to transform all values in one column into a list, with , around each value.
Has anyone used this formula before? The only information I can ...
34
votes
9
answers
221k
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 ...
- 501
15
votes
4
answers
41k
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 ...
2
votes
2
answers
1k
views
How to put the name of a cell into another cell?
In Google Sheets, is there a simpler way to put the name of a cell reference into another cell than this?
=concatenate(char(code("A")+COLUMN(D3)-1),row(D3))
- 121
1
vote
2
answers
3k
views
Return an offset cell value, where last occurrence in range is matched
I have a worksheet like this:
|| A | B | C |
---++--------+-------+-----|
---++--------+-------+-----|
1 || 1 | a | |
2 || 2 | b | |
3 || 3 | X ...
- 138
1
vote
1
answer
9k
views
How to query for count of dates in Google Docs?
I have a Google Spreadsheet of line items. One of the columns is a Date field. How can I query (or use a formula) to, for example, get the count of records where the Date field is within February of ...
-4
votes
1
answer
2k
views
Simple row references in Google Spreadsheets and resorting data
This is my scenario
Users are entering data into rows with several columns
Last column is calculated on values of few previous columns (so it's row number independent).
While entering new and ...
- 2,175
13
votes
2
answers
59k
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
-1
votes
1
answer
4k
views
Google Docs ImportXML can't scrape anymore?
ImportXML lets you get information from filetypes such as xml, html, csv, tsv, as well as RSS and Atom feeds.
This example in Google Docs Help for importXml function -
=importXml("toysrus.com";; "/...
- 2,835
2
votes
6
answers
12k
views
Sum of the values of the 10 cells above me?
I'd like a formula for Google Sheets that will return the sum of the values of the ten cells above the current cell.
That is, if I'm on C31, I want the equivalent of =SUM(C20:C30). However, I want ...
- 185
10
votes
2
answers
20k
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 ...
- 248
2
votes
1
answer
8k
views
Copy data down into next set of blank cells, if any, for whole column?
I have some text that I've taken in from a pdf, however the columns are not lined up the way I need them to be:
I want to duplicate the column A and D values down into any empty spaces/rows. So in ...
- 248
1
vote
1
answer
8k
views
Circular dependency detected even while using IFERROR
I have the following function in the cell B2:
ARRAYFORMULA(SUM(IFERROR(Purchases!B3:B, 0)))
And the following function in cell C2:
ARRAYFORMULA(SUM(IFERROR(Purchases!C3:C, 0)))
Basically, the ...
- 113
2
votes
1
answer
214
views
Update a cell based on two matching fields in separate tabs within a range
I'm trying to update a cell in Google Spreadsheets where its value comes from another sheet and based on the date range entered with it.
Here is what the two worksheets look like in the file.
...
- 123
14
votes
2
answers
18k
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"...
- 149
12
votes
1
answer
11k
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 ...
- 121
-1
votes
2
answers
132
views
Create a formula based on the number of columns in Google Spreadsheets
I have two rows of data
Row B = price of product
Row C = quantity of producst
I need to get the summary price:
(B1xC1)+(B2xC2)+(B3xC3)+..+(Bn+Cn)
I don't know n (quantity of columns) because n is ...
- 131
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 ...
- 305
1
vote
2
answers
998
views
How do I select Column1's value if an input value is between the integers in Columns2:3 for a range?
In a Google Sheets I have two tables:
Date BMI BMI Outcome
2011-06-21 22.1 ?
2011-06-22 21.8 ?
2011-06-23 21.5 ?
and
Name From To
Severely underweight ...
Nick Malyon
7
votes
1
answer
1k
views
Google Forms erroneous deletes rows in second sheet
I created a form in Google Docs with quite some questions. For a good overview I created a second sheet to pick the important parts from the survey. There I'm also able to add extra columns to add ...
- 594
4
votes
4
answers
5k
views
Returning the SUM of a range of cells from user-input START to END
In Google Docs, I am trying to sum the values of selected rows in a table, from position START to END.
Imagining this sample data:
Index VALUE
1 300
2 400
3 750
4 850
5 ...
jcinacio
3
votes
2
answers
5k
views
Is there a way to search and replace a regular expression in Google Docs spreadsheets?
The problem is that I entered a lot of formulas like =round(x+y) into a Google Docs spreadsheet. Now I need them exported to MS Excel and the latter only accepts =round(x+y,0) as a valid format. ...
- 133
4
votes
2
answers
2k
views
Google Spreadsheet conditional counting function
I am having trouble writing a function for this need (in Google spreadsheets):
I have 2 columns A and B.
I want to be able to get a percentage based on those two columns. I want to count the number ...
- 181
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
9
votes
3
answers
23k
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
4
votes
1
answer
202
views
How do I use a computed range in formula?
I currently use this formula:
=sumif($E$2:$E$363,L3,$D$2:$D$363)
I'd like to change it to instead of having the $D$363 hard coded, that it pick up the "363" part from another cell that I specify. ...
- 143
4
votes
1
answer
1k
views
What formula would I use to calculate monthly cost?
I'm trying to make a Google Sheets that will list my total expenditures in the form of recurring fees/subscriptions. I've successfully enabled the basic 'SUM=(C2:C24)', but I would like to add a ...
- 158
6
votes
1
answer
2k
views
How do I reference existing formulas from custom formulas in Google Spreadsheet Scripts?
I want something essentially like this:
function contains(arg1, arg2) {
return NOT(ISERROR(search(arg1, arg2)));
}
But NOT and ISERROR are undefined even though using =ISERROR(...) works in the ...
- 283
2
votes
1
answer
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?
2
votes
2
answers
40k
views
Google Spreadsheet formula, MINUS?
I have a spreadsheet with 3 columns:
| ITEM | COST | SUBTOTAL |
Subtotal, for example, is 1000 to start, like:
--------------------------
| ITEM | COST | SUBTOTAL |
--------------------------
| ...
- 289
1
vote
1
answer
11k
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 ...
- 159
57
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 ...
- 2,175