Questions tagged [formulas]
Formulas used in spreadsheets edited by web applications like Excel Online, Google Sheets, among others.
2,029
questions
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,179
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
95
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
914
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
25k
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
2k
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
| ...
- 813
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 ...
- 133
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 "...
- 44.6k
2
votes
1
answer
656
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 ...
- 468
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 ...
- 468
1
vote
2
answers
1k
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
4k
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 ...
- 52.3k
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
8
votes
3
answers
29k
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,857
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,857
3
votes
1
answer
946
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,857
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 ...
5
votes
3
answers
12k
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 ...
- 51
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
4
votes
1
answer
676
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,857
1
vote
2
answers
516
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 | ...
- 303
2
votes
3
answers
178
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
133
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
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
3
votes
1
answer
453
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
2
votes
1
answer
1k
views
Combining data from two columns to one (in separate rows - not comma separated) and removing blank lines
I have a Google Sheet which receives information from a google form.
I would like to collate some of this data in a separate sheet (within the same document). I would like the new sheet to combine ...
- 23
2
votes
1
answer
210
views
Match and lookup function on Google Sheets match different Japanese Kanji
Checkout this google sheet:
Dafuq 箇 策 Match 1
Lookup Dafuq
Exact FALSE
Despite the fact that those two kanji characters are completely ...
etherealeden
1
vote
1
answer
62
views
Conditional formatting over wide area of cells, referencing other cells
As data validation does not seem to be able to reference cells for numerical conditions (instead only being able to hardcode numbers); I have fallen back on conditional formatting. However, it does ...
- 125
1
vote
3
answers
4k
views
How to calculate with cells that contain two values separated by linebreak?
I've got a Google spreadsheet that looks like this:
Start time | End Time | Time difference
-------------+---------------+-----------------
11:30:00 | 12:45:00 | 1,25
-------...
1
vote
1
answer
237
views
Use the order of sheets in which they appear in a Google spreadsheet
Goal
What I would like to accomplish is to have my aggregation-master-schedule-sheet (see context) put schedules in the same order that the sheets are. I.e. the left most sheet is 1st, the 2nd ...
- 853
4
votes
1
answer
440
views
DGET is not successfully matching criteria in Time format
I'm trying to use DGET() to match values across several sheets.
If I try and match criteria that are times, it always fails. If I replace the criteria with words, it is successful. Here is an image ...
- 1,209
2
votes
3
answers
927
views
Google Sheet Cell Link to Google Form test score
I have a pre-made Google Sheet which has cells to be marked as "pass" or "fail" depending on the person's score. Is it possible to link a Form response to record a pass or fail mark depending on the ...
- 21
1
vote
2
answers
325
views
Formula result depending on the week of the day
I've got a bunch of cells that have formulas to display the day of the week according to the date above it. (I'm using Google Sheets by the way)
I want the cell underneath the day of the week that ...
- 11
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
2
votes
1
answer
5k
views
How can I import the whole sheet to another Google spreadsheet?
I have a sheet (sheet1A) in Google spreadsheet A, and another spreadsheet - spreadsheet B
Now I want to have another sheet on B - called sheet1B - that's totally the same as sheet1A, with data get ...
- 21
9
votes
2
answers
23k
views
Comparing times on Google spreadsheet
I want to check if a set of fields indicate times of the day that are before 11am. The dates are stored in this format: 8:42:00 AM
I tried A1<"11:00:00 AM" but it always returns True, even if it ...
- 335