Tagged Questions

Formulas used in Spreadsheets

learn more… | top users | synonyms (1)

0
votes
0answers
31 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 ...
7
votes
2answers
630 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
40 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
24 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
34 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
54 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
73 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
38 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
25 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
30 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
95 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 ...
0
votes
2answers
37 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
45 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
30 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
152 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
82 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
99 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
1answer
95 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 ...
1
vote
2answers
217 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 ...
3
votes
1answer
410 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 ...
0
votes
0answers
54 views

Import info from multi tabs in a spread sheet to one sheet

I'm looking to move data from "sub sheets" to the main sheet. I know I've done this before but it's been 5 years since. So here's what I'm looking for: ...
0
votes
1answer
56 views

A function opposed to SUMIF?

I have a huge list of matches between clubs and I want to calculate the goals for and the goals against. I could find a way to calculate the goals for, using: =SUMIF (A35:A46, "Liverpool", B35:B46) ...
2
votes
1answer
99 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) ...
-1
votes
1answer
115 views

Using “if” (or other function) to recognize a string and sum values

I started programming just a few weeks ago, got a grasp on the concepts and been evolving on it, since I have certain activities with spreadsheets, I decided to take it to the next level and got stuck ...
2
votes
1answer
474 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. ...
5
votes
4answers
206 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?
4
votes
2answers
216 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 ...
1
vote
0answers
30 views

Is there a way to do collaborative equation editing in a Google Presentation?

Since Google Docs now forbids equation in presentations (you cannot drag equation from doc anymore according to this question). Is there a way to include equations in a Google Presentation?
3
votes
1answer
44 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 ...
0
votes
0answers
103 views

Google Spreadsheets formula error

I have the following formula in a sheet that works in Excel but comes up with an error in Google Spreadsheets. =(IF(A6<=TODAY(), INDEX($AS$2:$AU$12,MATCH(A6,$AS$2:$AS$12,1), ...
2
votes
1answer
556 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 ...
0
votes
0answers
140 views

Use of importdata with arrayformula

I’m using importdata to retrieve Yahoo stock information, which works fine on indivdual rows, but when combined with arrayformula to fill down the page when additional symbols are added does not ...
1
vote
2answers
224 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
2answers
373 views

Google Spreadsheets sum only shown rows

Is it possible to sum a range of cells while ignoring rows which are not hidden? Thanks
0
votes
1answer
229 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 ...
0
votes
2answers
683 views

Google Spreadsheet - total of column if adjacent cell is true and minus another column

I have data like in the following table: +---+-----+-----+ | A | B | C | +---+-----+-----+ | 0 | 5 | 30 | | 1 | 0 | 30 | | 0 | 15 | 40 | | 1 | 0 | 40 | | 1 | 10 | 50 | ...
1
vote
2answers
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, ...
4
votes
1answer
2k 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 ...
2
votes
2answers
608 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
7k 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
441 views

“Yes” if a value partially matches in a range of values, blank otherwise

I have a column of values (column A for this example) like such: Phytophora sojae Phytophora ramorum Cryptococcus neoformans Coccidioides posadasii And I'd like to create a new formula column (B) ...
0
votes
2answers
2k views

Google Spreadsheets formula question: do not include cell in a formula if blank

I am a music teacher and I use the following formula to calculate my students performance grades: =100-SUM(F3-5)*-7 How can I change the formula so it only calculates a grade if there is data in ...
2
votes
3answers
8k 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 ...
5
votes
2answers
2k 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 | | ...
9
votes
1answer
14k 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 ...
0
votes
0answers
16 views

Google Spreadsheet: reference seperate spreadsheet [duplicate]

Possible Duplicate: How do I link a cell in Google Spreadsheets to a cell in another document? Is it possible with a Google Spreadsheet to reference a cell to another spreadsheet using a ...
0
votes
1answer
6k 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 ...
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
1answer
399 views

Text conditionals in Google Spreadsheets

I want to write a formula where I can add values to a cell whether another cell states approves the "transaction". It will be easier to explain with an example. Consider a spreadsheet containing the ...
0
votes
1answer
192 views

How can I have column E copy its data into the right column based on the initials in column M?

So what I have so far is a spreadsheet that will auto time stamp column N when my technicians enter their initials into column M. Now the 2nd thing I would like the spreadsheet to do is. When I ...