# Questions tagged [formulas]

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

164 questions
Filter by
Sorted by
Tagged with
1 vote
531 views

### Finding the longest time duration in Google Sheets

I'm trying to find the longest time value (hh:mm:ss) and I'm looking for it in column I, starting at row 5. I've already tried using the LARGE function but that outputs a decimal, not a time. =LARGE(...
35k views

### Cumulative Sum without Script

For this question, I've created a Google Apps Script function, that calculates the cumulative sum of a range. Martin Hawksey on Google+ made a comment about a more efficient way of calculating the ...
7k views

### Forms Data Manipulation In Google Sheets

I'm trying to develop a solution using Google Forms with Google Sheets that will collect Google Forms data and then automatically divide that one form submission's data into multiple rows of data in ...
• 2,779
76k views

### Stop a '+' from generating a formula

I'm having a bit of a struggle. I'm creating a spreadsheet which uses plus signs (+) regularly. I want a semi-permanent fix for +'s turning into addition formulae.
9k views

### Combine two columns into a list of all possible combinations of entries

I have two columns such as: a 1 b 2 c and I need to combine them like: a 1 a 2 b 1 b 2 c 1 c 2 Is it possible with a formula?
• 115
23k views

I want to be sure all my Google Spreadsheet work can be converted OK to Excel. How many of the Excel formulas match the formulas of Google spreadsheets?
• 227
1 vote
2k views

### multiple IF statements with between number ranges alternative

How to efectively deal with multiple IF-statements and with multiple "between number" ranges in Google Sheets' spreadsheet (e.g. C column based on B column) with a rule set as follows: 01-10 11-20 ...
• 24.6k
13k views

### Image in a cell with a hyperlink

I need a cell that contains an image to also contain a hyperlink. Following formula is the only one I found but doesn't work (error analyzing formula) =HYPERLINK("URL", image("URL")) Following ...
1 vote
6k views

### Google Sheets, formula, reference to variable sheets

Google says in their web help how to reference data from another sheet, but what happens if what I need is to make the reference depending on my fields on the current sheet? Let's say I have a sheet ...
50k views

### Short number format in Google Sheets; 1,024 → 1K, 1,816,724 → 1.8M

Any recipes out there for a "smart" number formatting formula for Google Sheets that's "scale-aware," a la Rails ActionView's distance_of_time_in_words method? I would expect to enter a number like: ...
• 455
59k 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
35k 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
11k views

### Show all combinations for a selection of columns

I have a sequence of letters, in Google Spreadsheets: | A B C D E F --|---------------------- 1 | U Y 0 4 X N 2 | H X Y 3 | W The first row contains a character ...
• 171
28k views

### Google Sheets QUERY won't display cell text if other cells have numbers

I'm not sure what is happening in my Google Sheet query and could really use some help. I have a quick screencast that demonstrates and explains the issue. Basically, I'm using a query to pull data ...
• 2,779
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
1k views

### Google Sheets Split row in multiple rows

I'm doing a spreadsheet for an event and I need to split a row into multiple rows. It's getting data from a form. I need a script that can arrange the data like in the blue rectangle.
335 views

### Formula breaks subtly in ARRAYFORMULA in Google Sheets

I have a spreadsheet document with a formula comparing a list of URLs to lists on two other sheets within the document. I desire to know if each URL on the first list is present on the blocklist, ...
• 23
1 vote
1k views

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 ...
• 706
696k views

### Google Sheets formula for "if contains"

I'm trying to figure out how to identify IF a list of items in one cell contains a value or string. EXAMPLE Cell A1 contains sites, sheets, docs, slides. I want cell B1 to display a 1 'if' cell ...
• 2,779
9k views

As a programmer, I find myself somewhat frustrated when trying to use Google Spreadsheets, because there seems to be no definitive description of the syntax that I can use for formulae. For instance, ...
• 527
27k views

### Split content of one row into multiple rows?

I have a table where each row contains data like this: Team name | First member | Second member | Third member | Team description Would it be possible to transform/break this data so that each row ...
• 1,833
1 vote
3k views

### ArrayFormula to compute running average for groups of rows

Is it possible to write an array formula that calculates the running average of the Amount column for each of the Name groups in the following sheet? The rows are sorted by Name. In Column C, I have ...
• 13
18k views

### Sum of all cells in Google Spreadsheet where matching row fits a value

I want to return the sum of all cells in ColumnA only if the value of a cell on the same row is equal to a specific value. For example, sum C4 if D4 = "Bob". Assuming there are six rows whereby "Bob"...
53k views

### Return a blank when cell reference is blank

I am using the following formula in a Google Sheets to calculate the number of days between two dates (dates are in columns G and AL): =ARRAYFORMULA(if(row(A:A)=1, "Readmission Days", (G:G)-(AL:AL))) ...
• 101
6k views

### QUERY error for BY column

I'm using the following query to pull data from one sheet into another. =query(data!B2:CL,"select B, C, D, E, F, G, H, I, J, BB, BZ, CA, BA where J = 'Industry Certification'",false) The above query ...
• 2,779
24k views

### Find the closest value (equal or greater) in a range in Google Spreadsheets

I have a range with a list of values (depths). I'd like to be able to select the row where the depth matches a value in a cell I provide. If no value matches, the greater value should be selected. ...
• 1,264
5k views

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
12k views

### Duplicate and rename sheet in one action on Google Sheets

I have a script which references the name of a sheet in Google Spreadsheets. When I duplicate a sheet with a cell that uses that function, the function is called right away and returns something like ...
• 325
4k views

### Partial cell formatting with CONCATENATE in Google Sheets

Is there a way to format partially using CONCATENATE in Google Sheets? For example, =CONCATENATE("*", Sheet1!A1, Sheet1!B1), and I want to make the * bolded or blue in color or something else. I ...
• 121
1k views

### How do I find the address of last non-blank cell in a row (while skipping columns)

Basically, I need to do what the title says on my spreadsheet here. So for row 3, I need to find the address of the last non-blank cell in that row, while skipping the grayed out columns (with the ...
74k views

### FILTER(condition OR condition) syntax?

I am using FILTER() to display a value from Sheet 2 where the Name columns match. My problem is where I want: =FILTER(Sheet2!A:F, Sheet2!A:A="Combat Medic" OR Sheet2!A:A="Universal") as OR results ...
• 411
51k views

### Concatenating only filled cells

I currently have a spreadsheet to keep track of scores in a card game. There can be between two and five players. I have the following: | Players | |----------| | Dave | | Paul | | John ...
• 706
59k views

I am using Google Spreadsheets to create a matrix (time/activity) as a daily schedule for staff to sign into. Is there a formula to calculate the number of cells in a column that are shaded (or not ...
• 131
34k views

### How to get the intersection of two sets

I have two lists (unique sets) in a spreadsheet and I want to get the intersection of the two. Is there an easy way to do this? Here's an example of what I want to achieve: List 1 List 2 ...
• 363
57k views

### How can I create a unique list of data across multiple rows AND columns?

Google Sheets has a UNIQUE() function which when given a column of values, will output a column of all unique values. This works well for column-specific data, however, you have multiple columns and ...
• 277
24k views

### Change date format [YYYYMMDD] to [MM/DD/YYYY]

I generated a report that formats date as 20160509 and my Google Sheets locale is already set to United States. I've played around with various options within Format > Number > Date Format but I can't ...
• 243
221k views

### How to Fill a Column with Sequential Dates in Google Sheets

In Excel, you can right-click drag a date downwards to fill a column with the next dates in the sequence. For example, if you enter 9/1/2014, select that cell, then right-click drag down over the ...
• 501
32k views

### LEFT JOIN two and more tables in Google Sheets

Say I have two tables. Table A (material requirements): product_id | material | required 1 | A | 0.5 1 | B | 0.7 2 | A | 0.2 3 | A | 0....
• 187
4k 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 ...
• 139
55k views

### How to stop NOW function from auto-updating?

I'm using Google Spreadsheet's NOW() function as a shortcut to insert the current timestamp now. However, the value in the cell changes whenever the spreadsheet is modified in any way, even when the ...
• 5,633
1k views

### Do named ranges not work under certain conditions?

Been tearing my hair out today with this one. I'll be as concise as I can. I have a number of named ranges, and I would like to reference them in a long, ugly formula that works without them just ...
• 999
5k views

### Google Sheets: How can I combine multiple ImportXML results for multiple URLs into a single column? [duplicate]

I want to grab some lists of data from various pages on the web and I'm using IMPORTXML to do it. Assume I have a list of URLs in column D. Depending on the URL, the length of the results can be ...
• 223
1 vote
368 views

### Export PDF link sheet ID auto change

I have the link for export as PDF a specific range on a sheet =HYPERLINK("*****export?exportFormat=pdf&format=pdf&gid=962914045&range=A1:H68&pagenumbers=false&gridlines=false&...
• 215
13k views

### How do you replace a formula with its result?

I created a Google Spreadsheet that is made up of 20+ sheets (all formatted the same way). The company I work for loads containers. One of those sheets calculates a particular parameter on each of ...
5k views

### Add missing dates to list of dates with values

Current Data: 9/20/2018 6 9/22/2018 3 9/23/2018 1 Desired Output: 9/20/2018 6 9/21/2018 9/22/2018 3 9/23/2018 1 I would like to have the missing dates added to Column A while the ...
45k 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 ...
• 153
5k views

### Conditional formatting with color scale for many rows independently once

I create reports with different cohort tables. Each row of a table must have conditional formatting with color scale (depending only on current row values, i.e. regardless of other rows min/max values)...
• 145
10k views

### Cell row reference coming from another cell [duplicate]

I need to make Google Sheets evoke the row number for a cell reference by pointing to another cell. I have a form with different cells that point to another sheet (Sheet1!). All the cells always point ...
• 41
1 vote