Formulas used in Spreadsheets
1
vote
1answer
24 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 ...
1
vote
1answer
16 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
71 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) ...
1
vote
2answers
92 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
630 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 ...
3
votes
1answer
156 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 |
| ...
1
vote
1answer
252 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
14 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
575 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 ...
1
vote
1answer
279 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
278 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 ...
1
vote
1answer
117 views
How to see individual answeres in Google Forms
I have made an survey with Google Forms. My problem is that I can't see the individual answers. Is that possible or can I only see the overview?
0
votes
1answer
116 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 ...
0
votes
1answer
322 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 ...
2
votes
1answer
2k 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
572 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
votes
1answer
577 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 ...
1
vote
0answers
1k views
Change value in one cell through a formula in another cell
I am trying to capture a timestamp for when a spreadsheet is first edited, and ONLY keep that first timestamp. However, NOW() updates each time a spreadsheet is edited. I found a working formula ...
2
votes
2answers
3k views
How can I calculate how often one string appears in a column compared to another string?
I have a column in my Google Docs spreadsheet which contains either "OK" or "NOK" (acceptance testing).
How can I calculate the percentage for how often "OK" appears compared to "NOK".
2
votes
2answers
4k 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
930 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.
...
0
votes
1answer
3k 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 ...
2
votes
1answer
84 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 ...
11
votes
3answers
24k 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
2k 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. ...
0
votes
1answer
2k views
Applying two filter functions in Google Docs Spreadsheets
I am trying to apply two filter functions in Google Docs.
Column (A) contains new data
Column (B) contains filter data, i.e. cells with text that should not be included in column C
Column (C) is the ...
1
vote
1answer
938 views
Validation based on multiple sheets in Google Docs
I have 2 sheets that each contain a column of data that I would like combined and used as a validation list for a column in another sheet. What would the formula look like for this?
3
votes
3answers
811 views
Tool for teaching maths online
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 ...
2
votes
2answers
256 views
Using formula (i.e. number calculation) in Gmail filter
I've been getting coupon offers from half a dozen of sites.
Is it possible to use a formula like "If there is a number that is larger than 60, then remove". This means that all coupons which cost ...
9
votes
1answer
66k 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 ...
1
vote
1answer
4k 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 ...
3
votes
4answers
409 views
Webservice which allows chat with math formulas
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 ...
20
votes
3answers
18k 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 ...
