Formulas used in Spreadsheets

learn more… | top users | synonyms (1)

38
votes
5answers
84k 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 ...
19
votes
1answer
27k views

IF OR Statements

How do you do an IF ... OR ... THEN statement in a Google spreadsheet? In PHP I would do this: if($C7 == "U.S.A." || $C7 == "Canada"){ ... How to do the same as a spreadsheet formula? At the ...
12
votes
5answers
55k 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 ...
10
votes
2answers
91k 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 ...
8
votes
2answers
19k views

Adding one Google Spreadsheet column to a string to dynamically create a URL in the next column

I have a spreadsheet of product id codes that I'd like to turn into links, and from everything I've read, the concat or concatenate functions in Google Docs should work for that, but so far everything ...
8
votes
4answers
725 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?
7
votes
3answers
2k views

Which formula extracts the domain name from an email address (bob@example.com -> example.com)?

Which Google Spreadsheets formula do I use to get the domain part from an email address? Example: bob@example.com → example.com
6
votes
3answers
5k views

Concatenating only filled cells

I currently have a spreadsheet to keep track of scores in a card game. There can be between 2 and 5 players. I have the following: | Players | |----------| | Dave | | Paul | | John | | ...
5
votes
1answer
328 views

Formula for smart, scale-aware number-formatting in Google Sheets? (e.g. 1,024 ā†’ 1k, 1,816,724 ā†’ 1.8M)

Any recipes out there for a "smart" number formatting formula for Google Sheets that's "scale-aware," a la Rails ActionView's distance_of_time_in_words method? I would expect to enter a number like: ...
5
votes
1answer
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 ...
5
votes
4answers
3k views

Tool for teaching maths online [closed]

I'm trying to find a good tool for teaching someone maths online. Some kind of a online whiteboard that the student can be invited to. I need to be able to draw graphs and handwrite using my drawing ...
5
votes
1answer
551 views

How to nest ImportRange into the query function in the new Google Sheets

We have applicants who come in and fill out a form via Google Forms. I have fields that auto-populate from the Response tab into a separate tab that is organized to fit our trackers. I am looking for ...
4
votes
2answers
306 views

Google Sheets - How can I use row values to index into another sheet?

I have an arbitrary google spreadsheet Data. alpha | 0.1 beta | 0.2 gamma | 0.3 delta | 0.4 epsilon | 0.5 And another sheet in the same file, Data Subset that holds an arbitrary ...
4
votes
1answer
38 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 ...
4
votes
1answer
4k views

In a Google spreadsheet, how do I list unique items, modified by a regexp?

I have a Google spreadsheet with items and prices. Column D has the prices, column C has item names, some of which contain multiple words. When an item has multiple words, only the first one is ...
3
votes
1answer
3k 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 ...
3
votes
2answers
49 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
2answers
2k views

How to interpolate data in a range in Google Sheets

I have an array with data: X Y 3 50 5 60 9 120 11 130 18 90 20 150 The data is entirely non-linear. X is guaranteed to be sorted. Now for any given ...
3
votes
1answer
62 views

In Google Sheets, can you apply a regex to a generated range?

I have a list of UK postcodes, looking roughly like this. Entries may not be unique. A1 1AA A1 2BC A1 3PN B2 4XY B2 5LH C3 6NJ They're in a range named Postcodes. I've set up the following ...
3
votes
2answers
635 views

Google Spreadsheets sum only shown rows

Is it possible to sum a range of cells while ignoring rows which are not hidden? Thanks
3
votes
1answer
23 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 ...
3
votes
1answer
126 views

Extending dget to a column in Google Spreadsheet

Given the following formula: =dget(Sheet2!$A:$C,Sheet2!B$1,{"USER ID";$A2}) Is there a way to alter this formula so that it fills all the values in a column? I've tried with arrayformula, changing ...
3
votes
1answer
95 views

Remove word values from the output of UNIQUE(SourceArray) in a Google spreadsheet

I am trying to list all the "other" options of a Google form. As such, I am using UNIQUE to list all results from a row so: =UNIQUE('Form Responses'!S2:S) I would the want to exclude the following ...
3
votes
1answer
239 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 ...
3
votes
1answer
25 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
1k views

How do I output a string containing quotation marks?

I am not super computer literate. I am trying to use the equations to grade my Google quiz, the problem is that there is a quote in the answer. Is there a different symbol I need to use? This is what ...
3
votes
1answer
2k views

Can I have formulas in a Google Form, not just the worksheet?

We use a Google Form for registration for our charity road race. I need to have two fields that contain formulas so people registering do not have to do the calculating. They can order t-shirts. ...
3
votes
3answers
2k views

How to apply a function to all the rows preceeding the current one in an arrayformula?

So I have the following situation: In the first column I have certain values, in the second column, given a certain row, I want it to contain the result of a function applied to all the preceeding ...
2
votes
4answers
610 views

Webservice which allows chat with math formulas [closed]

The google charts API supports creating good looking formulas based upon latex input: Does someone know an online chat service which makes use of that API to display discussed formulas? Maybe with ...
2
votes
1answer
8k views

Take SUM of colored cells in a column in Google Spreadsheets?

I have a column of numbers, where I've changed the background color of some of the cells to blue. All of the other cells in the column have the regular white background. Is it possible to create a ...
2
votes
3answers
857 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
2answers
418 views

Find the closest value (equal or greater) in a range in Google Spreadsheets

I have a range with a list of values (depths). I'd like to be able to select the row where the depth matches a value in a cell I provide. If no value matches, the greater value should be selected. ...
2
votes
1answer
759 views

Conditional Cumulative Sum using Arrayformula

I saw a nice solution for the cumulative sum problem here: Cumulative Sum without Script My question is how do you make this conditional? I mean cumulatively sum only numbers that belong to a certain ...
2
votes
1answer
1k views

How do I prevent that references in my formula change when I copy the formula to a new cell?

I'm trying to use submitted Google Form data in one spreadsheet to locate and retrieve data from another spreadsheet (tab in same document). The Google Spreadsheet file is here. The =QUERY statement ...
2
votes
1answer
22 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 ...
2
votes
1answer
45 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
votes
1answer
479 views

How can I COUNT rows with ARRAYFORMULA?

I have a sheet that lists incidents in an Incidents sheet: | Date | Name | Incident | |------------|-------|---------------------| | 01.01.2015 | Vidar | Car broke down | | ...
2
votes
1answer
278 views

Google Sheets formula every x rows, assign incrementing cell value

I have a Google Sheet, with the first row container headers (so row references will be -1) and rows are "grouped" by every 7 rows. See below. I've manually typed the numbers into the Image # ...
2
votes
1answer
12k views

Using INDIRECT() in sheet reference range

I have a separate sheet with some data where a user's input will reference the row number of a sheet. I'm trying to reference that sheet by inserting the column letter and taking a number input from a ...
2
votes
1answer
5k views

JOIN formula in Google Spreadsheets

I want to use the JOIN formula in Google Spreadsheets to transform all values in one column into a list, with , around each value. Has anyone used this formula before? The only information I can ...
2
votes
2answers
4k views

Is there a way to search and replace a regular expression in Google Docs spreadsheets?

The problem is that I entered a lot of formulas like =round(x+y) into a Google Docs spreadsheet. Now I need them exported to MS Excel and the latter only accepts =round(x+y,0) as a valid format. ...
2
votes
1answer
27 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
votes
1answer
41 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?
2
votes
1answer
61 views

Subtract values in a list that fit certain criteria within Google Sheets

I've got this sheet where I do some basic accounting, and I want to be able to input the balance in my pocket every once in a while. The idea is that the expenses I record until the next balance will ...
2
votes
1answer
253 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 ...
2
votes
1answer
93 views

Complex Formula in Google Spreadsheet

I have a 'dashboard' sheet and a 'log' sheet. I'm trying to pull data from the 'log' sheet and perform calculations on the data before displaying it in the 'dashboard' sheet. The 'log' sheet will have ...
2
votes
1answer
87 views

Problem with SUBSTITUTE function in Google Spreadsheets

I'm having problems using the SUBSTITUTE function as part of a formula. On it's own, SUBSTITUTE returns the desired output (i.e. in cell O3), but when combined in a formula, 0 is outputted (i.e. cell ...
2
votes
2answers
556 views

Dynamic generation of cell list / array from range value in Google Spreadsheets

I want to create an array/ a list of cells (i don't know the exactly terminology), in rows or columns form, covering the range between 2 numbers Fox example, I have 2 cells, A1 = 3; B1 = 10 I want ...
2
votes
1answer
982 views

Multiple conditions look up

I am having trouble building a spreadsheet, the problem its my data table. I want to be able to pull data from the table, something that will match data from column A, and column B (on the same row) ...
2
votes
3answers
10k views

COUNTIF but when multiple conditions are met

I'm trying to create a GDocs spreadsheet to collate data from a survey. I'm fairly new to the advanced features of GDocs/Excel but here's what I'd like to achieve. I've tried Googling but it's a tough ...