Skip to main content

# Questions tagged [formulas]

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

2,079 questions
Filter by
Sorted by
Tagged with
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: ...
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,199
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
923 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 ...
8 votes
2 answers
27k 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 | --+-----...
• 81
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 | ...
• 823
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 "...
2 votes
1 answer
663 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 ...
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 ...
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.7k
5 votes
1 answer
17k 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 ...
• 375
8 votes
3 answers
30k 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,867
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,867
3 votes
1 answer
954 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,867
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
13k 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,867
4 votes
1 answer
703 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,867
1 vote
2 answers
577 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 | ...
• 313
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 ...
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
5k 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 ...
5 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 ...
• 81
13 votes
5 answers
13k 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?
• 165
3 votes
1 answer
463 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 ...
2 votes
1 answer
223 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 ...
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
244 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
5 votes
1 answer
445 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,239
2 votes
3 answers
960 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
349 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
10 votes
2 answers
24k 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 ...