Questions tagged [formulas]

Formulas used in spreadsheets edited by web applications like Excel Online, Google Sheets, among others.

Filter by
Sorted by
Tagged with
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 ...
user avatar
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 ...
user avatar
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, ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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, ...
user avatar
  • 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,...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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?...
user avatar
  • 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-...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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?
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
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) +...
user avatar
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) ...
user avatar
  • 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 ...
user avatar
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. ...
user avatar
  • 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?
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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?
user avatar
  • 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?
user avatar
  • 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("...
user avatar
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 ...
user avatar
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?
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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| |...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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-...
user avatar
  • 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?
user avatar
  • 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 ...
user avatar