Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [formulas]

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

1
vote
1answer
6 views

If a dropdown value is selected, how do I add it to another column and then gather the sums?

So I have a dropdown in Col K with "Apple, Oranges, Grapes, Cherries". If a kid wants to order a specific fruit, I want it to auto-populate the section of Col N in green and then sum the total ...
0
votes
1answer
19 views

Arrayformula for running MAX of separate column

I'm trying to use ARRAYFORMULA in Google Sheets to get the max figures from a separate column only up to the row being calculated each time but can't for the life of me figure out how. It sounds more ...
1
vote
1answer
14 views

Combine Custom Formula with Colour Scale in Conditional Formatting

In Conditional Formatting in Google Sheets, I'm wondering if it is possible to combine the Format Cells if - Custom Formula is with a Colour Scale? It would appear not, but is there a clever ...
0
votes
1answer
11 views

How to increase formula by increment of 10

I have a spreadsheet with one value per year. I would like to take the average of decades (with, obviously, 10 cells per decade), so I set up a formula along the lines of =AVERAGE(A(1+10):(A(10+10), ...
-1
votes
1answer
21 views

How to test performance of a Google sheet formula?

I was wondering, whether ARRAY_FORMULA() enclosed by COUNT() work much slower than COUNTIF() without ARRAY_FORMULA(). How to test/compare performance of formula that uses a range as parameter?
1
vote
1answer
12 views

Google Sheets COUNTIFS Range of cells as criteria possible?

In the Google Sheets Formula =Countifs, is it possible to have a range as a criterion, for example: =COUNTIFS(ExampleSheet!A1:A10, "Some criteria", ExampleSheet!B1:B10, List_of_Names!C1:C) I couldn'...
0
votes
2answers
16 views

How to work over rows from all other sheets in Google Sheets?

I have a 'summary' sheet and 12 other sheets named 'Jan20', 'Feb20', etc. In the summary sheet I have this formula (to calculate Uber costs): =FILTER('Jan20'!F2:F, REGEXMATCH('Jan20'!D2:D, "UBER")) ...
1
vote
1answer
15 views

Sort by a Column While Maintaining Information in a Row

I had a previous question with lots of support and it solved my problem. The next issue I have is sorting the data while bringing the information in the corresponding row with it. A link to the ...
0
votes
3answers
27 views

How Can I Sort a Data Range in Google Sheets Based on an Data Range?

I have been looking for a way to sort this information. I need to sort these items in this order: BK, BB, BV, BIV, BIII, BII, BR. I was trying to make a Google Script to get it, but I could only ...
0
votes
2answers
35 views

A formula to get total number of cells in an open-ended range

I need a formula to calculate the total number of cells in the range, including empty and not empty cells (i.e. the "size" of the range). It is more useful for open-ended ranges, like A:A, $3:$3, or ...
-1
votes
1answer
14 views

Are Boolean expressions short-circuit evaluated in Google sheet formulas?

Suppose I have the following expression repeated in a long column: =IF(OR($U2=""; COUNT(aHugeRange)=0); ""; SUM(aHugeRange)) I would like to avoid performing COUNT() and SUM() as much as possible to ...
0
votes
1answer
14 views

How can I use GOOGLEFINANCE to return a share price in a certain currency?

I have shares in different currencies that I want to track in only a single currency. I currently have a cell that returns the currency and one that returns the price, I then multiply them. However, I'...
1
vote
2answers
14 views

Formula for Calculating Percentile Race Finish in Google Sheets

I've Googled and found many ways to calculate percentile, but most seem to be in regards to an array of numbers as opposed to a single number. I'd like to know in what percentile I finish in a given ...
1
vote
1answer
9 views

Report the date/column header for the first and last non-zero entries for each client/row

I have a sheet with client names (Column A) and the number of visits to the business by mm-yyyy (Columns B - AC). I'm trying to find the first month that they visited the business and the last month ...
0
votes
2answers
16 views

Function to search columns and tally data

I am wondering if I can write a function to search a column in my spreadsheet for a specific name, then return x number of values within the row(s) that contain the name. For example... M4 holds the ...
0
votes
1answer
12 views

MATCH a cell value, then return the value of the cell next to it

Is there a way to use a MATCH formula to look up where one value appears and then use that returned index as the point to reference to find another value? Right now I have a spreadsheet set up where ...
4
votes
4answers
46 views

Joining two rows of data with alternating delimiters

I'm working with two rows of values that are related to each other and trying to build a formula that will compile the data into one cell, with syntax that uses two delimiters. If a cell in the ...
2
votes
2answers
4k views

Query Multiple Ranges with Results in Row

I am trying to query five ranges with the results displayed across a single row. To do this, I was using { } with commas separating each range, but it's only giving me the first range in return. ...
1
vote
2answers
14 views

How to combine a formula with text in one cell?

My spreadsheet contains data divided into weeks in a year and I would like to combine the following two pieces of information in one cell: =ISOWEEKNUM(TODAY()) Calendar week 'Calendar week' being ...
1
vote
1answer
12 views

vlookup sum to calculate total hours assigned next to dates

I'm attempting to calculate a total number of hours based on entering 2 different dates. Each day between the dates has assigned 'hours worked'. I'm trying to use Sumproduct or Arrayformula and Sumif,...
0
votes
1answer
31 views

JOIN two columns in one cell

What should I do if I want every time I fill out column C, to all data from B6 to C14 would appear on the notes?
0
votes
1answer
8 views

Concatenate using an array formula to result in last name.firstname

We have a form that asks for the students first name and last name. I am trying to using concatenate and an array formula to display "Lastname.Firstname". I used: =ARRAYFORMULA(CONCATENATE($B$52:$B,...
0
votes
1answer
21 views

Concatenating URL with a value to create link, but links only works partially

I have a column of cells in Google spreadsheet with values such as: b2397 b9504 b3333 b8655 bxxxx I would like to combine each value with a URL, for example: http://websitex.com?id= ...
1
vote
1answer
10 views

Join multiple ranges when using conditional formatting

I have a simple conditional formatting custom formula which highlights duplicate values in a column: =countif(A:A,A1)>1 I have another column on another sheet (same workbook) which I would like ...
0
votes
1answer
11 views

Average value using different sheets on Google Sheets

I have a data set that's separated by year (so, the first year is Sheet1, the second year is Sheet2, and so on. There are 18 years in total). I want to average the data from different years. At first, ...
1
vote
1answer
21 views

How can I list rows meeting a criteria and limit the amount of rows by a sum of the list?

I am trying to create a scheduling assistant within Google Sheets. I need to list out tasks currently scheduled for a specific date but there is a volume cut off that needs to happen. Client ...
1
vote
1answer
8 views

Google Sheets Circular Dropdown Dependency

Suppose we have 2 columns Id & Name which contained in sheet1. Now in sheet2 have dropdown for Id & Name which user can select and it Lookup value for each other (select Id from dropdown, Name ...
1
vote
2answers
17 views

Formula for PROPER Case with Apostrophe

I'm using Google Sheets glued together (not be me) from multiple sources where some of the names are in all caps. In trying to clean web-ready content, I have good success with the PROPER() function ...
0
votes
1answer
44 views

Spell check with formula or apps script

I have text in the table. I want to check the text whether there is a typo or not. Usually to check my typo using the default Spelling Tool from Google Sheet. The question is: Can the spelling check ...
0
votes
2answers
42 views

QUERY function randomly and invisibly corrupts dates [on hold]

I found this BUG while trying to use MATCH using Queried data against the actual data. This Google Spreadsheet shows the BUG in action. When you format the data as Number the problem is fixed. This ...
0
votes
1answer
17 views

How to apply conditional data validation when a cell with a defined value is changed?

I have a spreadsheet that's basically a grid checklist, let's say 10 columns and 20 rows. There are multiple cells that have N/A as the value, and I'm worried going through the checklist that one ...
0
votes
0answers
10 views

Formula isn't working in Spreadsheet [on hold]

=SUMPRODUCT(COUNTIFS(INDIRECT("'"&A$100:A$107&"'!A:A"),A14,INDIRECT("'"&A$100:A$107&"'!B:B"),"NO")) It's not calculating the "No" value across the sheet where it matches the "C2" ...
0
votes
1answer
28 views

Convert Number and Text to Number

I have one spreadsheet in Google Sheets with data like: In sheet 1 in column pricelist $100 in text 102 in Number $103 in text 104 in number 105 in number When I do the vlookup and try to multiply ...
-2
votes
3answers
30 views

Parsing a non-nested JSON string in Google Sheets using formulas only

I have a cell with the following JSON data: {"rev2_0": "2,058", "rev2_1": "2,099", "rev2_2": "2,141", "rev2_3": "2,184", "revchg3_0": "3%", "revchg3_1": "6%"} In another cell, I want to get the ...
1
vote
1answer
13 views

How to combine dynamic columns in Google Sheets while retaining data in other rows?

I have a Google Form that asks people to fill out their name, gender, and then their size preference for shirts and pants. The form automatically outputs the 2 sizes in separate columns. I want to ...
0
votes
1answer
30 views

How to count how often a value is submitted to a Google sheet via Google Forms per day, and put that count in different Google Sheets workbook

I have two Google Sheets workbooks, Workbook A and Workbook B. Workbook A is generated from a Google form that collects email addresses submitted from users. Workbook A has two columns next to each ...
10
votes
3answers
18k views

Perform basic maths on contents of Google Document (not spreadsheet)

In Microsoft Word, it is possible to use very simple formula e.g. =SUM(ABOVE) to display the total of a column or =SUM(LEFT) etc. Is it possible to do something similar in Google Docs? Obviously this ...
0
votes
2answers
21 views

Insert column into Google sheet generated from a Google form to calculate the difference between two columns of data

I have a Google form where people track their company mileage and input "Mileage In" and "Mileage Out." I want to insert a column next to Mileage In and Mileage Out that shows the difference between ...
0
votes
1answer
13 views

How to get a Sample Standard Deviation values that match condition?

I am trying to get the standard deviation of a subset of data. I have two sheets Name Value a 1 a 2 a 1 b 5 b 5 In a separate sheet I have: Name StdDev a b GOAL: I am trying ...
0
votes
2answers
12 views

How to count duplicate names as one value in Sheets?

I have a large dataset in Sheets and I'm grouping together the data in a pivot table and have a hard time figuring out how to avoid counting every instance of a name instead of counting the name as ...
1
vote
4answers
30 views

How to add every Sunday in sequential order?

So I tried using the simple formula: =date(2019,3,10) =date(2019,3,10)+7 =date(2019,3,10)+14 =date(2019,3,10)+21 ... etc. But when I highlight the first top 3-4 cells to drag down and formulate ...
0
votes
1answer
10 views

Using CHOOSE with ArrayFormula has different results from Excel

Function CHOOSE within an ArrayFormula seems to yield different results in Google Sheets, compared to Excel. Note how only the first column is shown. Should I report it as a bug, or am I missing ...
0
votes
1answer
20 views

Use of IMPORTRANGE to juxtapose variable-length lists

Let's say I have two Google sheets, Doc1 and Doc2, both having only one sheet featuring two lists with the same schema, say two columns Name and Age. The lists have an unknown number of entries, but ...
1
vote
3answers
48 views

Convert Text String into Date/Time Value

I'm fetching data into a Google Sheet. The date column contains multiple cells with the following text string pattern: Mon Mar 11 19:05:32 +0000 2019 The pattern always repeat itself: [day] [month] ...
1
vote
1answer
20 views

Combing two ranges with identical column structure from two different sheets (in same spreadsheet)

I'm doing a simple ticketing system, where I have two sheets, "Form Responses" and "Email Responses" both of which are populated automatically as their names imply (one from a form, one from an email ...
0
votes
1answer
15 views

How do I calculate weighted rates in Google Sheets?

I would like to calculate the cost on $D "Rent" with a weighted cost when the respective cell in $B="PB". The calculation would be based on the formula 7x+1.3x=1252 where 7 is dependent on COUNTIF(A2:...
0
votes
2answers
16 views

Extract and graph information from a days-hours-minutes-seconds format [on hold]

I'm working in Google Sheets, and I have data for race finish times in "8d 3h 40m 13s" format and I'm want to extract that information so I can plot it in a chart. Any ideas for how to achieve this? ...
0
votes
1answer
11 views

Drop-down list from cell formula (not data validation)

I am trying to make a triplet dependent drop-list. The first is a typical drop down but the next two are dynamic. Since you cannot enter a formula into the data validation criteria, how can you ...
0
votes
1answer
103 views

QUERY error NO_COLUMN:col1

=query(importrange("12HAbnTv4g_6m1UeCOs9ZO0gAYvy8tlaXr-cc2ecPEic","Master!A:ba"), "select col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,col12,col13,col14,col15,col53 where col53<> ...
0
votes
1answer
28 views

Retrieve fund price from Vanguard website using importxml in Google Sheets

This was working in Google Sheets but now I get #N/A message: Imported Content is Empty A1: https://institutional.vanguard.com/web/c1/product-details/fund/1680 A2: //*[@id='dailyNavValue'] A3: ...