Formulas used in Spreadsheets

learn more… | top users | synonyms (1)

1
vote
1answer
43 views

Advanced formula in Google Sheets

I'm trying to make a worksheet that gives graphs of data in other worksheets. I had the idea that the user could choose which worksheet they want to use. For example, there are 3 worksheets with data ...
3
votes
1answer
24 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 ...
1
vote
1answer
104 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 ...
1
vote
1answer
20 views

For range X, when empty grab column A and list them in a single cell, coma separated

I'm compiling stats for our Boom Beach team, which means I have to log attacks by each players for each operations. An operation is launched every day and all players should perform their attack in ...
1
vote
2answers
261 views

How can I make a Google spreadsheet Function reference one column's value and adjust it to a different Value?

I am trying to create a spreadsheet that will calculate varying sizes. I need it to reference a key where a letter is an established size such as H=1984, S=672, etc. so as that that column can be ...
2
votes
1answer
19 views

How to apply formula to multiple cells without it adjusting part of the formula

I'm having a little bit of a problem and was wondering if your expert knowledge could help me out. Formula in question: ...
1
vote
1answer
17 views

Complex Sum Function in Google Sheets

Apologies if this is lame, but I can't get a proper value using =SUM to perform order of operations on an employees hours (he has gotten two raises since starting after surpassing expectations). The ...
4
votes
1answer
233 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 ...
1
vote
2answers
371 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 ...
0
votes
1answer
32 views

Applying formula to lookup value in array of cells in Google Form Responses

I have a Google Form response sheet, part of which contains several columns (say B:G) that will only have a single value between them. The form asks respondents for their county of residence and then ...
0
votes
1answer
288 views

Google spreadsheet cell outcome dependent on another cell’s range

Example: column A1 is $35.00. I want column B1 to be a percentage markup value dependent on the value in A1 (i.e.: 75%). So if A1 is between $1 and $10, B1 = 75%; if between $11 and $20, B1 = 60% and ...
1
vote
3answers
2k views

Return a blank when cell reference is blank—Google Spreadsheet

I am using the following formula in a Google spreadsheet to calculate the number of days between two dates (dates are in columns G and AL). The problem is that some of the cells in col AL are blank, ...
1
vote
2answers
39 views

Average two numbers IF the second number exists

I have 2 columns (Google sheets) of numbers similar to: 10.453 12.178 12.654 0 The third column is: if number higher than 0 exists in both columns, display an average of the two else just ...
1
vote
1answer
28 views

Decimal conversion of time values

What is or can it be done to add 2 cells, that return a time equation? I deal with multiple time entries and having to enter them in a standard format of 4:45 hours or 4:25 hours. I have a decimal ...
0
votes
1answer
56 views

How to get the count of the current sheet?

I have a spreadsheet that I have spread out over many sheets. However, I could automate the filling out of some simple data by doing some simple calculations. I could program something simple like ...
1
vote
0answers
13 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
3answers
201 views

Add a time picker to Google Docs Spreadsheets?

Is there an option to add a time picker on Google Docs Spreadsheets? I don't need timezones or anything complicated. Just a string on the HH:MM format, that matches the custom format available. I ...
32
votes
4answers
65k 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 ...
0
votes
0answers
52 views

Formula query to perform dynamic sum

I am creating a spreadsheet which looks up values from a second sheet. I need to SUM various sections in the sheet but these are variable - the row that needs SUMing can change at every fresh import ...
1
vote
1answer
75 views

Can I use ArrayFormula with CountBlank or IsBlank if so how?

=IF( OR(COUNTBLANK($F3)>0, COUNTBLANK($G3)>0, COUNTBLANK($H3)>0, COUNTBLANK($I3)>0), "Missing Values", $E3+(index(LSI_Index!$B$4:$B$40,MATCH(VLOOKUP($G3, ...
0
votes
0answers
20 views

Query in google spreadsheets

I have the following problem: In Sheet2 I have some dates and names like: A B C D E F G .... March April May .... 1 2 3 4 5 .... 1 2 3 4 ...
1
vote
1answer
91 views

Google Sheets - Auto update table and auto add new data entries

I have a table in wich raw data is entered manually, with info about clients and the requests they made. And then I have this table in which I'm trying to analyze a bit the raw data (# of requests ...
1
vote
1answer
45 views

How can I add hours in Google Sheets to show days?

I have 2 cells in Google Sheets with values: 22:00:00 22:00:00 and I have a cell which has the function sum to sum these values and it shows the value: 20:00:00 I am guessing it is trying to ...
1
vote
1answer
281 views

How to skip blank cells in Google Spreadsheet/Excel when copying a range from source to destination by Formula

I have three levels of hierarchical data in Google Spreadsheet that looks something like this: As data is being entered in here, I want it collect in three separate sheets like this: And The ...
2
votes
1answer
192 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 | | ...
1
vote
1answer
131 views

ArrayFormula to compute Running Average for groups of rows

Is it possible to write an Array Formula which calculates the running average of the Amount column for each of the Name groups in the following sheet? The rows are sorted by Name. In Column C, I ...
2
votes
2answers
134 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. ...
0
votes
0answers
387 views

Complex Index and Match with three criteria

I need to create a formula that will match three criteria in a range on another sheet, and where they match, return the value in an adjacent cell. The problem I have is that one of the criteria is a ...
5
votes
4answers
337 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
2answers
1k 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
1
vote
1answer
106 views

COUNTIF in Google Sheets that checks 'if' a string is in one column before counting in another column

I'm trying to use the COUNTIF function in Google Sheets. The Goal I want the 'dashboard' sheet to display the count of items in column F submitted to the responses sheet 'if' a specific string ...
2
votes
1answer
50 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
50 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
1answer
142 views

Can I subtract a cell value in another cell that already have one in Google Spreadsheets?

Let's say that I have two cells A1 (time needed) and B1 (time spent). I want that when I enter a value in B1 the value of A1 will be reduced automatically. I want that in some way I could enter in ...
0
votes
1answer
158 views

Return value in column A in preceding cells in column B using an Array Formula

I've been looking for a solution to this for days now. How do you fill cells in column B (using an array formula) that are preceding the cell in column A with the value of column A only when cell in ...
1
vote
1answer
263 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 ...
0
votes
1answer
32 views

Counting specific words

I want to count how many times in a column certain words from a predefined set appear. In this example those specific words will be days of the week. For example: Mon =3 Tues ...
2
votes
1answer
120 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
2answers
600 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
1k 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. ...
1
vote
1answer
452 views

How do I multiply column values?

I simply want to multiply column h with column j, and store the result in column k in each row. What do I type in as the formula to do this?
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 ...
12
votes
5answers
53k 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 ...
0
votes
2answers
52 views

Which formula sums all values from a column in a line that contains a specific word?

Let's use this random spreadsheet to illustrate my problem. What I want to do is a sum of all Page Views (F) that contains the Category (B) named /Online Computers. Which formula can I use to do ...
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
2answers
82 views

How to summarize many-to-one table in Google Sheets

I've got two separate sheets we'll call 'Sheet A' and 'Sheet B' with a table on each. The table on 'Sheet A' has a Many-to-one relationship to the data on the second sheet. On the second sheet, I'd ...
1
vote
1answer
45 views

How do I refer to a range in another sheet?

Sheet 1 has numeric values in the B column. I'd like to sum them into a single value within Sheet 2, preferably without having to copy them individually first. According to the docs, I can copy a ...
1
vote
1answer
789 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 ...
5
votes
2answers
3k 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 | | ...
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 ...