Questions tagged [formulas]
Formulas used in spreadsheets edited by web applications like Excel Online, Google Sheets, among others.
1,866
questions
2
votes
1
answer
943
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 ...
- 123
2
votes
1
answer
1k
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 ...
- 53
11
votes
4
answers
22k
views
Is there any way to use a formula in a Google Sheets filter view?
I have a sheet with a column of numbers. I'd like to create a filter view that only shows rows with a value greater than 500, but I can't find any way to do that. There are lots of different numbers, ...
- 212
0
votes
1
answer
971
views
Return value in column A in preceding cells in column B using an array formula
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 column C is not blank?
In other words, after column C ...
- 53
4
votes
1
answer
4k
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 ...
- 53
1
vote
1
answer
109
views
Search for multiple variables in multiples rows at the same time in Google Sheets/Drive
I'm using Google Sheets (only option, sorry, know this should be a database).
Sheet 1: List of 200 employee names, and up to 10 location codes they are frequently assigned to.
Sheet 2: List of 50 ...
Ariel
1
vote
1
answer
62
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
...
- 11
3
votes
3
answers
6k
views
Import current price from website to spreadsheet
As you can probably gather from the title, the goal is to plug the current price of this product into a spreadsheet.
Perhaps I'm over-complicating things, but there must be an easy way to do this.
The ...
- 31
2
votes
1
answer
4k
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 # column, ...
- 143
11
votes
4
answers
36k
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 value,...
- 683
1
vote
2
answers
1k
views
Conversion from Excel to Google Spreadsheet - sum across sheets
I'm importing an Excel spreadsheet into Google Drive. Its a huge spreadsheet, and overall Google spreadsheets has converted everything, except the following:
There are a few cells with formulas like ...
- 716
0
votes
1
answer
2k
views
Using VLOOKUP with dynamic range array
I have a sheet with a list of email addresses that is being auto-updated (more email addresses being added on a daily basis). I have a second sheet with a list of email addresses that have been ...
- 103
0
votes
2
answers
139
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 that?...
- 195
2
votes
2
answers
2k
views
Appending text to each column in Google Sheets
I have a Google Sheets that has about 200 columns. Each one is a name, and to each one, I want to append -banner.jpg. For example:
abcde
fghij
klmno
will all be updated to:
abcde-banner.jpg
fghij-...
- 530
2
votes
3
answers
9k
views
Error with exact date 21/02/1971 - function MINUS expects number values
I am getting this really bizarre error. In cell A1 I have 21/02/1971 (Brazilian-style date), in A2 I have =today()-A1. I receive the following error:
#VALUE! Error: Function MINUS parameter 2 ...
- 21
3
votes
2
answers
2k
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 ...
4
votes
1
answer
8k
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 ...
- 539
25
votes
1
answer
80k
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 ...
- 353
4
votes
2
answers
150
views
Identifying and working with any cells that match
I am trying to find a function, or a series of functions, that allow me to:
(1) Identify if any cell in column A matches any other cell in column A,
(2) add numbers in Column B that occupy the same ...
- 41
2
votes
2
answers
4k
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 ...
- 21
2
votes
2
answers
3k
views
How can I fill the diagonal of a matrix with zeros?
I have an empty matrix:
I would like to fill the diagonal of a matrix with zeros:
Is there any clever way to do it?
The matrix can be pretty large and it's tedious to do this manually. I could use ...
6
votes
1
answer
15k
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?
25
votes
3
answers
60k
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 ...
- 251
3
votes
3
answers
3k
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 ...
25
votes
9
answers
61k
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 ...
- 16.6k
2
votes
2
answers
7k
views
Add 1 if there is data in cell
I have a spreadsheet of data which lists the inspection results of schools, in which I have a column that states the result or is empty if that particular school has not yet been inspected.
I want ...
- 21
0
votes
1
answer
204
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) +...
4
votes
1
answer
7k
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) ...
- 43
-1
votes
1
answer
210
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 ...
3
votes
1
answer
8k
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. ...
- 31
13
votes
3
answers
4k
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?
- 253
1
vote
3
answers
6k
views
Daily running average in Google Sheets possible or not?
I work at a job wherein I receive tips. I have set up a sheet to keep track of the tips I make throughout the year. I have all the formulas set up that I want, save for one. I am trying to figure out ...
4
votes
2
answers
1k
views
How can I use row values to index into another sheet?
I have Google Sheets 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 number of row indices ...
- 95
7
votes
3
answers
32k
views
Calculate days between now and a future date
I need to calculate how many days are left from today to a certain date in the future in Google Sheets.
How can I do that?
- 585
1
vote
0
answers
107
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?
- 111
3
votes
2
answers
9k
views
Import Google Spreadsheet range if a column contains a particular value
I want to import a range of data from one Google Spreadsheet (1) into another (2), but only when Column J in spreadsheet 1 contains a 'yes'.
I'm using the following formula:
=query(ImportRange("...
- 31
3
votes
1
answer
2k
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 ...
- 33
9
votes
5
answers
44k
views
Count the number of words in a string in Google Sheets
I have a simple Google spreadsheet with a number of text strings. I just want the number of words contained in each cell. Is there an in-built Google spreadsheet function I may use?
Nishant
2
votes
1
answer
2k
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,787
4
votes
5
answers
22k
views
How automatically to add spaces inbetween each character in a text string?
Teachers I work with, enter four scores for a student’s assessment in a single cell, like this: 2113, with no breaks or spaces.
How can I separate those characters so as to be able to create a chart ...
- 41
0
votes
3
answers
7k
views
Counting unique values based on multiple columns
I am working in Google Spreadsheets and trying to do some counting that takes into consideration cell values across multiple cells in each row.
Here's my table:
|AUTHOR| |ARTICLE| |...
5
votes
1
answer
5k
views
Google Spreadsheets: IF not working
I have problems with my IF command in Google Sheets. If gives #ERROR! all the time. See this simple test:
Not even if I put in the number directly and avoid references:
Am I using the IF wrong? Or ...
- 530
4
votes
2
answers
4k
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 ...
- 49
1
vote
2
answers
6k
views
How to use an IF statement over several cells?
I'm making a budget for myself. Currently, my Google Spreadsheets looks something like this:
----A---- -----B----
food 50
alcohol 20
food 120
dance ...
- 13
1
vote
2
answers
335
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 ...
- 11
1
vote
2
answers
3k
views
How can I set the increment value for Google Spreadsheets to auto-increment?
OP Edit: I think I want Sheets to behave in a way it's not intended.
I expected Sheets to recognize the incremented value in my formula the way I believe it recognizes the increment value in a ...
- 113
0
votes
1
answer
666
views
How do I select a range programatically in Google Spreadsheets?
I have a spreadsheet like this:
active date name
1 1-Jun-2013 Paul
1 4-Jun-2013 Sheela
1 8-Jun-2013 Paul
1 2-Jul-2013 Henrik
3-Jul-2013 Henrik
10-...
- 105
2
votes
2
answers
3k
views
Google Spreadsheets sum only shown rows
Is it possible to sum a range of cells while ignoring rows which are not hidden?
- 137
0
votes
2
answers
1k
views
Spreadsheet to calculate weekly increase
I'm trying to create a spreadsheet with my weekly cardio and weight training average times and a total of time spent during an average visit.
I would like the final column to display the in/decrease ...
todd_dsm