Formulas used in Spreadsheets

learn more… | top users | synonyms (1)

0
votes
1answer
74 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 ...
1
vote
1answer
6k 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
1answer
163 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. ...
0
votes
1answer
190 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
1answer
23 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
42 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?
0
votes
0answers
5 views

How do I calculate the first Monday of next year in Excel? [migrated]

I need a formula to calculate the first Monday next year (or first Tues, Weds, Thurs, etc.) based on the date in cell A1. So for instance, if the date in cell A1 happens to fall on a Thursday, ...
0
votes
2answers
195 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 ...
0
votes
0answers
47 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: ...
2
votes
1answer
30 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 ...
27
votes
3answers
41k 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
69 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 ...
2
votes
1answer
65 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
73 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 ...
0
votes
1answer
33 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) ...
5
votes
1answer
70 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
153 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 ...
3
votes
2answers
1k 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 ...
1
vote
0answers
27 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?
1
vote
1answer
5k views

Circular dependency detected even while using IFERROR

I have the following function in the cell B2: ARRAYFORMULA(SUM(IFERROR(Purchases!B3:B, 0))) And the following function in cell C2: ARRAYFORMULA(SUM(IFERROR(Purchases!C3:C, 0))) Basically, the ...
8
votes
1answer
10k 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 ...
3
votes
1answer
35 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 ...
12
votes
4answers
46k 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
0answers
66 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
250 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
209 views

Hiding Formulas on Excel Spreadsheet and Google Docs

I've created some formulas in my Excel Spreadsheet. I've locked and hidden them so that employees don't accidentally mess up the formulas by clicking on them. However, when I send the file thru email, ...
3
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 ...
0
votes
0answers
111 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 ...
3
votes
4answers
2k 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 ...
1
vote
1answer
5k 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
2answers
334 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
2answers
514 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
784 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
453 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
3answers
7k 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 ...
-1
votes
1answer
782 views

Simple row references in Google Spreadsheets and resorting data

This is my scenario Users are entering data into rows with several columns Last column is calculated on values of few previous columns (so it's row number independent). While entering new and ...
2
votes
1answer
337 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
1k 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 ...
0
votes
1answer
5k 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 ...
2
votes
1answer
374 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 ...
4
votes
1answer
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 | | ...
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 ...
6
votes
2answers
12k 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 ...
0
votes
1answer
181 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 ...
2
votes
1answer
102 views

Performing calculations using filters within a GMail account

Is it possible to perform calculations using filters within a GMail account? I've been received receipts for various tickets, and I want to calculate the number of people who paid a certain amount of ...
0
votes
1answer
470 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 ...
10
votes
1answer
86k 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 ...
2
votes
1answer
4k 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 ...
1
vote
1answer
1k 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 ...
0
votes
1answer
1k views

Google docs spreadsheets: Copy data down into next set of blank cells, if any, for whole column?

I have some text that I've taken in from a pdf that isn't exactly synced up with the right lines as much as I'd like: I want to duplicate the column A and D values down into any empty spaces/rows. ...