Formulas used in Spreadsheets

learn more… | top users | synonyms (1)

1
vote
1answer
28 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: ...
1
vote
1answer
15 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
votes
1answer
17 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 (...
1
vote
2answers
40 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' ...
0
votes
1answer
9 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 ...
4
votes
2answers
1k views

Perform basic maths on contents of Google Document (not spreadsheet)

In Microsoft Word, it is possible to use very simple formula e.g. =SUM(ABOVE) to display the total of a column or =SUM(LEFT) etc. Is it possible to do something similar in Google Docs? Obviously this ...
4
votes
2answers
54 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 ...
0
votes
2answers
19 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 ...
2
votes
1answer
24 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 ...
5
votes
1answer
6k views

How do I combine a cell value with a string in a Google Spreadsheets formula?

I have the following IF condition in some cell: =IF(A1>A2, "value is <C5>", "value is <D5>") where <C5> and <D5> should be the values of cells C5 and D5. I know it is a ...
2
votes
1answer
106 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 ...
1
vote
1answer
3k 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 ...
7
votes
2answers
4k 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 ...
1
vote
1answer
30 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 ...
1
vote
1answer
55 views

arrayformula (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 ...
1
vote
0answers
20 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 ...
2
votes
1answer
42 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. ...
1
vote
1answer
22 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 ...
1
vote
1answer
12 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 | ...
3
votes
1answer
18 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 ...
2
votes
1answer
43 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 ...
2
votes
1answer
28 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 "...
1
vote
2answers
42 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 ...
3
votes
2answers
49 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 #...
4
votes
2answers
82 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 ...
1
vote
2answers
73 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 ...
4
votes
1answer
66 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 ...
2
votes
1answer
40 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 ...
1
vote
0answers
17 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 ...
3
votes
1answer
278 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 ...
4
votes
1answer
78 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 ...
3
votes
1answer
38 views

Google Sheets Conditional Formating “IsEqualTo” 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 ...
1
vote
1answer
107 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 ...
8
votes
4answers
1k views

Looking for an “isFormula” formula for Google Spreadsheets

Is there a function that will tell if a specific cell has a plain (manually entered) value or has a formula?
2
votes
1answer
191 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 ...
3
votes
1answer
33 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 ...
3
votes
2answers
112 views

Concatenating two columns using only filled cells in one of them

In my table, column A has row names and other columns have values: +------+---+ | M100 | D | | M130 | B | | M340 | | | P304 | F | | P400 | | | P499 | C | +------+---+ I'd like to join ...
3
votes
1answer
166 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 ...
2
votes
3answers
892 views

How to count the occurrences of 'STRING' in a row, but only if that row begins with 'IFTEXT'

I have a sheet with employee time logs, as follows: Name Day1 Day2 Subtotal of 'OFF' days this period Jess PRESENT OFF 1 Bob PRESENT PRESENT 0 Name Day3 ...
2
votes
1answer
178 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?
10
votes
2answers
92k views

How is reddit's link karma calculated?

I couldn't find it easily in their FAQ: When does your link karma increase 1 point? I don't think it's a one-one relationship with votes, as some of my stuff got upvoted and it didn't change ...
12
votes
5answers
56k 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 ...
1
vote
1answer
28 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 ...
2
votes
1answer
9k views

How do I use the INDEX or OFFSET functions in Google Spreadsheets?

I have my spreadsheet set up as follows: first 10 columns full of descriptive data followed by 24 columns of month data (2 columns per month). The last 24 columns are static and the values are ...
4
votes
1answer
42 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 ...
39
votes
5answers
94k views

Google Spreadsheets ISEMPTY() formula 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 ...
3
votes
1answer
26 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 ...
1
vote
0answers
12 views

ArrayFormula changes the result [duplicate]

The following formula returns the correct result: =if(S3,if(J3>=NRealGames,0,min(max(NRealGames-J3,0),S3)/S3),) This one returns the same result: =if(S3:S,if(J3:J>=NRealGames,0,min(max(...
3
votes
2answers
728 views

Google Spreadsheets sum only shown rows

Is it possible to sum a range of cells while ignoring rows which are not hidden? Thanks
1
vote
0answers
69 views

Counta Function Counting One Extra Filled In Line

I am creating a register for some workshops to introduce people's personal information, organized by when these workshops are taking place. I have six different spreadsheets (Monday, Tuesday, ...