Questions tagged [formulas]
Formulas used in spreadsheets edited by web applications like Excel Online, Google Sheets, among others.
1,988
questions
1
vote
1
answer
3k
views
Google Sheets Filter Conditions from List
Context:
I have a huge spread-sheet such that rows/records are considered "jobs," where columns are aspects of the job like location. One such column P, titled File-Name, that lists the pdf-file-...
- 853
1
vote
1
answer
1k
views
VLOOKUP problem with value from =arrayformula(iferror(regexextract
I'm trying to use VLOOKUP to pull data from one tab in a Google Sheets workbook into another tab. The VLOOKUP formula works if the value being searched for is 100103, but not the cell reference G2 (...
- 2,807
3
votes
1
answer
6k
views
Autoincrement in Google Sheets based on Google Form submission
I'm trying to create an auto-generated unique ID# for each meetings being submitted via a Google Form.
The unique ID# solution must also meet the following requirements.
The ID# must be static (...
- 2,807
147
votes
9
answers
730k
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,807
0
votes
1
answer
80
views
Conditional Formatting Referencing another Cell (and using addition)
I am tracking high school credit acquisition. Each trimester a student must earn 2 credits to remain on track. If Cell A1 is "trimester 1 total credits", and cell B1 is "trimester 2 total credits", I ...
- 1
7
votes
2
answers
2k
views
Why is a blank string greater than 0 in Google Spreadsheets?
Why does
="" > 0
Evaluate to TRUE?
And how can I input a value that would evaluate to FALSE that is not a number?
- 173
3
votes
2
answers
575
views
Count occurrences of names so that only one in each row counts
I have a set of data in one Sheet tab, and a counter on another tab in the same document.
I want the counter (in sheet 2) to read through the text in the table in sheet 1, and update how many times ...
- 139
1
vote
3
answers
2k
views
Track highest number in a cell over a series of recalculations
How do I keep track of the highest number that was in a cell in Google Sheets?
I'm running random numbers through a cell and I would like to track the highest number reached. Specifically, I am ...
- 21
1
vote
2
answers
1k
views
Grouping Multiple Data to Output Common Values in Google Sheets
Apologies if this has been covered before...
I'm creating a Google Sheet spreadsheet to group the amount of occurrences of a particular product, based on the country that it is assigned to. Imagine ...
- 11
0
votes
2
answers
40
views
Three nested IFs in CONCAT throwing error
So I have this formula:
=CONCAT(=IF(F26 = " Kb","0",IF(F26 = "16 Kb","16",IF(F26 = "64 Kb","64"))))
I'm trying to return the result "0" or "16" or "64". Three possible inputs, and three outputs. ...
Will
2
votes
1
answer
3k
views
Combining columns horizontally with an arrayformula
I need to join columns with a formula (not a script) in Google Sheets in a different way than others I've found--here is my original data, supposing that the Avengers train by playing racquetball:
...
- 29
4
votes
3
answers
7k
views
How to sum cells in Google Sheets that contain numbers and text?
This has already been addressed in Excel but I was wondering how to do the same in Google Sheets.
So suppose the B cell has this formula:
="frontend (days)
total: "&sum(B2,B10,B14,B20)
and the ...
- 1,129
2
votes
1
answer
3k
views
Compute the total of every row with arrayformula
In my spreadsheet, I need to add up each individual row. It does that if I drag the formula down in Y, but then when a new row is added to the data from a Google form, it won't copy the formula. So I ...
- 49
2
votes
2
answers
94
views
Formula to use to add up all rows that returned a value of '1' for several cells that meet a specific criterion
I need cell B21 in the Reports sheet to add up all rows that meet this criterion:
return a value of 1 if columns M, O, T, U, W, X, Z, AA, AB, AC have
the answer No, I do not have psoriasis on ...
- 101
1
vote
1
answer
894
views
Array formula (or other) to copy down formula in adjacent cell to last row with data?
I have a spreadsheet in Google Sheets in which the formulas in E2 and F2 need to be copied down to the last row that has data. Here are rows 1 and 2:
Name of Teacher Date Absent During Start ...
- 11
2
votes
1
answer
3k
views
Google Sheets formula for weekly column totals
I'm a bit of a newbie to Google Sheets formulas and such.
I have a set of data entered daily with the dates converted by WEEKNUM() to display and allow me to filter by week no. I want to add the ...
- 49
1
vote
0
answers
40
views
Dynamic dropdowns - for sales contact management exercise
I am trying to figure out a method in Google spreadsheets where I would be able to display information of contacts after I have selected 2 more criterias before.
So imagine it this way:
You see a ...
- 11
7
votes
2
answers
20k
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 |
--+-----...
- 71
2
votes
1
answer
1k
views
Split one column into two equal halves
GOAL:
Split one column of items into two half-sized columns of items. So if there are originally 1 column by n rows I want two columns by (n ÷ 2) rows with exactly the same items as the 1 x n column.
...
- 853
0
votes
1
answer
47
views
Show false if field empty, function results if not [closed]
I am trying to fix the following condition:
=if(AND($L$1>=F9,F9="");"exsteeded";"")
This will actually do, if a certain date is over the current date, the exceeded will show. but it have to ...
- 9
1
vote
1
answer
2k
views
Google sheets formula to countunique rows with a condition
I have a sheet where I keep track of some expenses. the data is formatted like this:
Vendor A | Plumbing | $250
Vendor B | Cleaning |
Vendor A | Cleaning | $19
Vendor C | Washing | $20
| ...
- 803
3
votes
1
answer
1k
views
Sort Column but keep associated value in Google Sheets
I have an Google Sheets spreadsheet that looks something like this:
|Team #|Rank|
| 100 | 4 |
| 200 | 3 |
| 234 | 6 |
I want to sort the teams by Rank, from best to worst, and then assign them ...
- 33
1
vote
1
answer
3k
views
How to block automatic row and column insertion in Google Sheets?
While playing around with array formulas, "sometimes" when the result has more rows and columns than the previously available in the sheet, Google Sheets automatically adds the row needed. I said "...
- 43.2k
2
votes
1
answer
648
views
Can I use the IRR formula without listing out each investment?
Let's say I've invested ₹1 in a mutual fund every month for the last 88 months, with a resulting corpus of ₹236.
I want to find the annualised rate of return using the IRR formula. To do so, I have ...
- 460
6
votes
2
answers
22k
views
What's the formula to select a range excluding a sub-range?
I want to say A1:A60 excluding A20:A30. Is there a minus operator for use with ranges, in Google Sheets?
I know that you can take the union of two ranges, like {A1:A19;A31:A60}. I would like to know ...
- 460
1
vote
2
answers
901
views
An arrayformula to find the previous non-empty cell in another column and find the last row to work with
I would like to do something similar to what was solved in the question:
An arrayformula to find the previous non-empty cell in another column
The problem was:
If I have a column "input", I would ...
- 47
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
5
votes
2
answers
3k
views
Formula based on cell positions from target cell
I have a spreadsheet that I use to keep track of my paid time off (PTO). I accrue a certain number of hours each pay period (which I know in advance). When I take some time off, I add a row and keep a ...
- 51.8k
5
votes
1
answer
14k
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 ...
- 335
8
votes
3
answers
27k
views
A spreadsheet formula to assign letter grades based on numeric ranges
I'm trying to figure out how to write an IF statement that will be 'true' if a value is in-between two values.
Example:
If value is between 60 and 69.9 return "D"
If value is between 70 and 79.9 ...
- 2,807
0
votes
0
answers
40
views
Sum the same cell in a range of sheets [duplicate]
I have an Excel spreadsheet that has this formula:
=IF(ISBLANK(B6),"",SUM(Sheet1:Sheet10!O6)+Sheet11!P6+Sheet12!T6)
I would like to use this spreadsheet in Google Sheets so I can get it on other ...
- 472
4
votes
1
answer
3k
views
Is it possible to 'reset' the value of a cell in one sheet from another sheet?
I have two separate google sheets (GlobalReport & IndividualReport). Both share information between each-other, but I need to be able to "reset" a cell value in the IndividualReport sheet.
Cell ...
- 2,807
3
votes
1
answer
910
views
Conditional formatting "is equal to" value in referenced cell
What do I add to the text field directly below the "is equal to" selection if I want the test to be the value of a specific cell?
I want the condition to pass and format a cell "IF" it is equal to ...
- 2,807
1
vote
1
answer
3k
views
Automatically record daily values in a new column
So I have this spreadsheet I use to control Content funnel.
I've come up with a few formulas to use something else than a pivot table.
But in order to get the trends and alarms I'm looking for, I need ...
4
votes
3
answers
11k
views
Query or filter the data where the start time is past 3:00 pm
I've started using Google Sheets for our logbook and it's been a great help. I have data with a time component in it (start and end time) and I'd like to query or filter the data where the start time ...
- 41
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,807
4
votes
1
answer
598
views
QUERY data based on Alpha Range in Google Sheets
I'm trying to figure out if I can pull data from a sheet in a Google Sheets Workbook based on an alpha range.
For example. I only want data associated with 'last name' that start 'A' and goes ...
- 2,807
1
vote
2
answers
384
views
Function name to add rows in Google Sheets
Is it possible to add rows with a built-in function (such as MIN() or INDIRECT()) in Google Sheets?
I would like to fill rows in the main sheet such as
+---+----+--------+
| A | 18 | Atext1 |
| A | ...
- 283
2
votes
3
answers
170
views
Set a formula for all cells in a column
I would like to use a same formula for cells in a column. Instead of writing it for all cells, is there a way to set it in a cell and tell to apply this formula to all cells in a column (or any set of ...
- 123
2
votes
2
answers
3k
views
Have formula copied to entire column in Google Sheets
I'm trying to make a spreadsheet that displays the average of a row, i.e. =average(c3:al3) and the function is in column AN3.
I want to copy that function down a row (but need to do this for 560 rows)...
- 23
2
votes
2
answers
1k
views
Google Sheets LEFT function trim text generated by IMPORTHTML function
When I use this formula to import data
=iferror(INDEX(IMPORTHTML("http://www.thestreet.com/quote/GE/details/company-profile.html","table",3),8,1))
The result is Sector: Industrial Goods. I'd like to ...
- 21
3
votes
1
answer
131
views
Google Spreadsheet Formula to show heading
I have a Google Spreadsheet in which I am comparing projectors. Basically it has formulas to divide the bulb life/cost and contrast ratio/cost. Then it adds the total of the two and gives me a ...
- 105
3
votes
2
answers
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 ...
- 853
12
votes
4
answers
4k
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 ...
4
votes
2
answers
3k
views
Dropdown list based on the result of another cell's dropdown
I want to populate a dropdown list based on a selection of another cell's dropdown list.
Here is my spreadsheet.
E.g., if the user does not make a choice from D20 dropdown menu, therefore F20 ...
- 71
10
votes
4
answers
10k
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?
- 135
3
votes
1
answer
439
views
How to create multiple columns from one single column?
I've got information that I gather from emails, from where I just take the lines, pass them through a filter and drop them into a Google Spreadsheet sheet.
It ends up looking like this:
Element 1: ...
- 305