Formulas used in Spreadsheets

learn more… | top users | synonyms (1)

4
votes
1answer
5k 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 ...
1
vote
1answer
17 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: ...
2
votes
1answer
89 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
votes
0answers
20 views

Sorting and Analysing Cell Data

I was wondering if it is possible to analyse data in a Google spreadsheet cell, and then sort that analysed data into different cells on a different sheet? Let's say the cell to be analysed contains ...
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 ...
1
vote
1answer
16 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 ...
7
votes
2answers
3k 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
26 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
39 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
33 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
21 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
35 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
22 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
35 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
46 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
52 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
65 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
55 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
27 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
16 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
264 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
59 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
36 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
75 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
905 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
145 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
31 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
96 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
137 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
878 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
110 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
26 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
41 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 ...
38
votes
5answers
91k 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
25 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: ...
3
votes
2answers
701 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
59 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, ...
1
vote
1answer
36 views

Cross referencing 2 sheets and pulling in values in Google Sheets

I have a Google sheet with multiple sheets in a single document. A generalized version of what I have is something like this: Sheet 1: A B C | AA | | | | BB | | | ...
3
votes
1answer
33 views

How to order category by numerical order with text in cell

I want to arrange my C column by numerical order, however the cells themself have text in something like "503 in Kitchen & Home", I want it to detect the number but ignore the string. Is ...
1
vote
2answers
753 views

Referencing a cell range relative to the bottom of a sheet

I am developing a "Daily Personal Inventory" spreadsheet based off a related Lifehacker article. I've successfully made a few modifications based on my own circumstances/goals, but there is one ...
1
vote
1answer
41 views

Add cells based on the content of another column

I have this sheet and I am trying to add the values of June but the issue that I have is that these values will update often with so I need to find a formula that accounts for that. The other issue is ...
2
votes
1answer
55 views

substitute() multiple terms in Google Spreadsheets

I'm trying to simplify semi-complex text strings into much simpler ones - specifically names into hashtags. For example: Tomáš Rosický into #TomasRosicky or Alex Oxlade-Chamberlain into ...
2
votes
2answers
208 views

Add new row while keeping functions in new Google Spreadsheets

In the new Google Spreadsheets, I'm trying to make a simple spreadsheet which will assign points to my salesmen regarding some actions they complete. I would like to be able to add a new row below ...