This tag is for spreadsheet questions where the question or answers involve an in-the-cell worksheet function.
1
vote
2answers
36 views
Multiple IF statements: IF, AND, ISDATE, THEN formula
I'm trying to get a nested formula to work, but I keep getting an error. I tried using the generic formula =IF(AND(A1="this",B1="that"),"x","") but couldn't figure out how to make sure Q6=date. I had ...
1
vote
1answer
16 views
Spreadsheets: Sum points per row based on if expected value is unique within row
Given a Google spreadsheet of quiz answers (with rows for questions, and columns for people's answers), I want to work out a person's total score. The scoring system is that if only one person gives ...
1
vote
2answers
54 views
Google sheets function IF, AND conditional formatting
EDIT 2: link to test sheet: https://docs.google.com/spreadsheets/d/1IWQWDK9piw_xFVm_48XZ8YAS_LQqXi6sd2GITLIfoAQ/edit#gid=903221057
EDIT 1: added a column, so now J=K, K=L, L=M | updated formulas to ...
0
votes
0answers
28 views
Array Formula Auto-Calculation in Google Forms
I have a google form which is returning data into Column A and Column B and I need to perform calculations on that in Column C, D & E. I have been using Array Formula so that C & D auto-...
0
votes
1answer
42 views
Google Sheets: replace formula with its value after evaluation
In Google Sheets, I'd like to replace a formula with its evaluated value immediately after evaluation.
Specifically, given two empty cells A1 and B1, I'd like B1 to be filled with the timestamp of ...
-1
votes
2answers
27 views
Google Sheets - How do I find the sum of the last row of data?
I have a spreadsheet with a row a numbers added every day automatically. I want a function to find out the sum of the last row of numbers, as that last row contains the most up-to-date information.
...
2
votes
3answers
28 views
Copying data from one cell in another based on date
I have a spreadsheet, using google sheets, that I have formed into the follow metrics:
Column A is the dates of the month
Column B is the data
I want to be able to copy the data from Column B into ...
0
votes
1answer
59 views
Why is my =VLOOKUP function returning #N/A for the first cell?
I'm trying to build a spreadsheet in Google Sheets to calculate a list of charges that should be oncharged to child companies (expenses are paid for by a holding company, and oncharged).
My idea for ...
0
votes
0answers
10 views
Sumifs summing when conditions evaluate false?
I have a sheet with five columns: ID1, ID2, date, quantity, and the cumulative sum that match both ID1 and ID2. The last is a sumifs shown below
And even though the ID1 (4) and ID2 (2) first occur ...
0
votes
1answer
52 views
Regex compare two strings
I have two strings
"*j?a?c?k*c?o?o?p?e?r*a?v?o?n*l?a?k?e*o?h*"
and
"*j?a?c?k*c?o?o?p?e?r*t?r?a?n?s?p?o?r?t*a?v?o?n*l?a?k?e*t?e?r?m?i?n?a?l*"
These seem like they should be equal in regex. However, ...
0
votes
1answer
21 views
Finding Min duration for a set of cells
Lets say for example I have 6 durations:
mm:ss.00
4:03.2
4:08.2
4:03.1
4:06.4
4:01.3
4:03.3
And I want to find the minimum duration. How would I do that? It doesnt work if I say =min(A1:A6).
I've ...
1
vote
0answers
33 views
Google Sheets VLOOKUP with wildcards on range
SETUP
Say for a simplified example I have a table of raw data from my bank displaying each store I've spent money, and I want to categorize these into either high-street, website, or pub venues.
The ...
0
votes
2answers
36 views
Automatically apply new formatting and remove time from a dynamically added Date-Time in google sheets
I am new to Google sheets and don't know how to write excel/google sheet formula to apply automatic formatting to a date+time value added through an external script.
I have a CRM integrated with ...
0
votes
1answer
344 views
Adding multiple rows by script
I have this simple Google Sheets script that adds a row after every already existing one. What do I have to change to for example add 12 rows?
function addRows(){
var sheet = SpreadsheetApp....
-1
votes
1answer
31 views
Join two cells data - COCATENATE not working properly
I'm using the following formula to get the data of two cells:
=CONCATENATE(C2,"Dia",E2)
But it's not working properly. Take a look at the screenshots:
It should be returning the Traffic + Date, but ...
0
votes
0answers
99 views
DATEDIF of latest date of a range (Google Spreadsheet)
I'm trying to get the latest date for a range of cells and Calculates the number of days between two dates using the function DATEDIF.
As you can see from my screenshot example, my calculation should ...
1
vote
1answer
17 views
Google Sheet formula on finding smallest value larger than 0 does not work inside conditional formatting
I wanted to find and highlight the smallest value larger than 0, but the formula I am using does not work in the conditional formatting.
=MIN(FILTER(L4:L100, (L4:L100)>0))
Namely, if I use it ...
1
vote
0answers
31 views
How to get new Google Form responses to take on non-form functions in Google Sheets
I developed a Google Form that copies responses to a linked output spreadsheet in Google Sheets.
In addition to the columns created by the form question/responses themselves, I've added additional ...
0
votes
0answers
12 views
How to query a tab that is specified by a cell
I have a function in place (Let's say in cell H8) that I want to change the destination of a query based on what another cell says.
=IF(ISBLANK(D8),"",iferror(QUERY(GU_GA_Y!$A$8:$D,"SELECT sum(C) ...
0
votes
1answer
39 views
Adding a value at the end of this Filter formula Google Sheets
I have the below formula to remove blanks in a list and would like to add a separate cell value at the end of it (let's call it A1), not related to range V9:V22, but I would like A1 to show up below ...
1
vote
2answers
53 views
Get day from date, Google Sheets
I haven´t found much searching on the net.
The case:
One cell is filled out in date-format by the TODAY() function. How can I update another cell with the actual day as string? I´ve seen older ...
-1
votes
1answer
76 views
Count times user won where user is in team
I have a list of teams with a list of players next to it
(Would it be better/easier if the players are in a single cell?)
+Team Name+--------+--------+
| Team 1 |Player 1|Player 2|
| Team 2 |...
0
votes
0answers
9 views
Counting cells with a certain background?
I wish to count the number of cells in a certain range with a certain hex color as a background in Google Sheets.
This is so that I can automatically get the number of cells that have it, display it, ...
2
votes
1answer
20 views
How do I get sum totals for BUY and SELL entries for specific dates in Google Sheets?
I'm looking to get the difference (profit) between BUY and SELL for each day (column A). I've managed to get overall difference between BUY and SELL for all days but would like have each day ...
0
votes
1answer
58 views
Can't get number of month for a specific month in the Portuguese version of Google Sheets
I am obtaining the number of months for the Portuguese names of the months using Google Sheets. For most months, the formula below works:
=MONTH("01/"&A1&"/01")
Except for Setembro (in ...
3
votes
2answers
51 views
Remove UTM code from the end of HTML link in Google Sheets
I have a link that I pull in elsewhere. Let's say it looks like this:
https://www.mysite.com/site/interesting-content/name-of-article.html?utm_source=source&utm_campaign=ab&utm_medium=medium&...
0
votes
0answers
205 views
Concatenate in side an arrayformula with google sheets
I want to concatenate column A to the end of a url for every row A isn't null. This is just adding A:2 to the end over and over any ideas?
=ARRAYFORMULA(IF(ISBLANK(A2:A),"",CONCATENATE("http://www....
3
votes
2answers
530 views
Search a text for specific text, then multiple if statements depending on what's there
I've got a weird question about Google Sheets.
First, here's what I've got so far, but it returns an error "Function REGEXEXTRACT parameter 2 value "My Text here" is not a valid regular expression."
...
3
votes
1answer
39 views
Formula or script for highest number starts with alpha
I am looking for a formula or script that can show me the highest number within a range of values that have a leading alpha character. For example:
D101
D102
D103
D104
J101
J102
J103
I would like a ...
0
votes
1answer
332 views
Parse error with nested IFs in an array formula using SUMPRODUCT and VLOOKUP
I am trying to create a nested IF formula to calculate the value of a VLOOKUP-ed range of cells in a separate spreadsheet using if, arrayformula, sumproduct, vlookup, and importrange. There are three ...
1
vote
1answer
23 views
I want to parse a cell in a spreadsheet and replace certain letters with a multiplier
Someone submitted a neat price list of medieval goods to Hacker News today, and the list is a little hard to read because the prices are in their typical values (e.g. "4s" for 4 shillings, or "13s 4d" ...
4
votes
1answer
363 views
Indirect named range
In Excel, at B3, I use data validation of List and entered:
=INDIRECT(B2)
In B2 I put the name of the range I defined. This works well. I enter the name of the range in B2, and B3 is updated with ...
1
vote
2answers
251 views
Apply conditional formatting to the top four values in a range
I have a range of cells B11:B18 and I want to conditionally format them to highlight ONLY the top four values. I have tried different formulas I found online but they rank ALL eight cells!
1
vote
1answer
69 views
Split Text and reorganize and concatenate back together
I want a formula to split data "052 089652" and to reorganize it as "089652/A052/9" in Google Sheets. The formula needs to add "A" to the start of "052", add a "9" at the end, keep the leading zero, ...
-1
votes
2answers
182 views
How to use relative references in an ARRAYFORMULA with DSUM
I have a formula:
Total Hours:
=DSUM(data!A1:B, "Number of Hours", {"Provider Name";A2})
The column A is a dynamic list of all the providers. How do I use an array formula to fill in all the rows ...
3
votes
2answers
383 views
How can I calculate compound interest for an entire range of percentages?
I have a range of percentages in cells A1:A5. I want to calculate how much all of these percentages compound up to and give a final factor.
e.g., 1.00 times A1 (10%) times A2 (6.5%) is 1.1715.
I can'...
2
votes
1answer
772 views
Select subrange of an array-returning formula like GOOGLEFINANCE()
In Google Sheets, GOOGLEFINANCE returns an array of values, like so:
The formula is entered in A1 but the result extends into other rows and columns. I would like to "select" (not sure what the exact ...
0
votes
1answer
180 views
Help with ordering a Google Sheets =QUERY
I'm trying to add an ORDER by F to the following query, but every time I do it returns a syntax error.
ORIGINAL QUERY (working)
=QUERY(data!A2:Z, "select G, E, F, I, J WHERE A = " & C1, "")
...
2
votes
2answers
94 views
How to make IF function in Google Spreadsheets output nothing?
How do I output nothing with an IF function?
I have created this nested function that takes the average of three cells.
However, if one of those cells is empty, it should only take the average of the ...
0
votes
1answer
35 views
How do I create a formula that will tell me if something is paid, or days overdue?
I need a formula for my Google Sheets that marks a cell as PAID once another cell is filled but counts the days overdue if that particular cell ISN'T filled. Currently I'm using:
=IF(DAYS360(M31, ...
0
votes
2answers
494 views
Return reference to last occurrence of value in a column?
I tried the solutions from this question but couldn't get it to work.
If C1:C5 = { 1, 1, 1, 2, 3 }
And B1 = 1
How can I return a reference to the last value of B1 (1), in this case, "C3" on Google ...
-1
votes
1answer
175 views
Function to return days passed in month
I'm looking for a function which will return HOW MANY DAYS have passed in the month -1 e.g. on 07/08 would return value 6, on 08/08 return value 7 etc. or on 31/10 return value 30.
It would also be ...
1
vote
2answers
31 views
Sort non-unique names by sum of another column
I have a spreadsheet (technically a Google Sheet) I transcribed from a script of a musical. Each line is its own row, and has the name of the person who said it and the number of words in the line in ...
1
vote
1answer
314 views
Problem with CONCATENATE when used inside of arrayformula
I'm trying to update this formula so it will work inside of an 'arrayformula'.
This works on its own and when I fill it down.
=if(E2>0,CONCATENATE(B2, " | ",C2," | ",D2), "")
This does not ...
1
vote
1answer
123 views
How to convert currency at a specific day's exchange rate?
I need to convert 1 USD = INR on specified date (23/07/2017)
Using
=D5*GoogleFinance("CURRENCY:USDINR", "price", "23/07/2017")
But it is throwing an error, where I am going wrong?
0
votes
1answer
30 views
Create a pie chart with a formula
I'm using google's monthly budget sheet to track my expenses.
I need to have a pie chart but I don't want it to be floating around in the sheet and be in a specific cell(s). So I thought a formula ...
1
vote
1answer
64 views
Google Spreadsheets SUMIF look to previous row
I want to make SUMIF in Google Spreadsheets look at the previous row for its evaluation. So if my sheet looks like this:
trigger, value1
something, value2
I want it to add value2, because the ...
0
votes
1answer
49 views
How to return maximum numeric value if text is in array with MAXA()?
UPDATED
MAXA() not returns maximum numeric value if text is in array, as expected according https://support.google.com/docs/answer/3094016
MAXA
Returns the maximum numeric value in a dataset.
and ...
-1
votes
1answer
38 views
Formula for adding columns of a row till the last column in Google Spreadsheet
I want to sum the columns of a row and display the sum in the last column. As shown in the example below sum of each row is displayed in column TOTAL
COL1 COL2 COL3 COL4 TOTAL
...
1
vote
2answers
146 views
How do I use regexmatch inside sumproduct?
This works:
=sumproduct($J$6:$J,($A$6:$A="Order")*($G$6:$G="T8"))
This does not.
=sumproduct($J$6:$J,($A$6:$A="Order")*(Regexmatch($G$6:$G,"T8"))
produces formula parse error.
This
=regexmatch(...
