Questions tagged [worksheet-function]
This tag is for spreadsheet questions where the question or answers involve an in-the-cell worksheet function.
0
votes
2answers
32 views
Conditional formatting if entry is final entry of the day
I have a Google Sheet in which I keep track of transactions, kinda like this:
| Date | Funds | Running total |
|---------|---------|---------------|
| 5/23/18 | 10.00 | 10.00 |
| 5/23/...
0
votes
1answer
22 views
Is it possible to calculate in the background of a data validation cell?
Is it possible to calculate in the background of a data validation cell?
I am creating a quite comprehensive spreadsheet that shall help me calculate dice pools in an RPG, but to not trouble you with ...
0
votes
0answers
14 views
Google Sheets Combining multiple IF statements into one formula (as an Array)
Hope someone can help, got the formula below but I want to modify it so that the first IF formula, IF(F3:F="Delay","Delay", returns a “Delay” if it finds a match (not if it finds "Delay") and if it ...
0
votes
1answer
23 views
Examine for overlap in Google Sheets?
Let's say I have the following start and end days/times:
SHEET1
Resulting in a range of time from Mon 10am through Tues at 11:59pm.
I'd like to know whether this range of time overlaps another ...
0
votes
1answer
36 views
Change COUNTIF range based on the current day of the week, with 3 days using the same dataset
I work as a delivery driver and have been futzing around trying to create a google sheet that automatically updates with the current day's drops. However, there is a rub with the data set. The ...
1
vote
2answers
130 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 ...
2
votes
1answer
27 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
329 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
45 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
57 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
30 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
35 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
186 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
115 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
25 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
1answer
64 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
39 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
688 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
32 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
128 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
18 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
1answer
46 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
43 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
57 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
25 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
80 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
59 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
239 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
583 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
41 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
366 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
27 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
407 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
489 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
79 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
237 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
471 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
1k 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
402 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
104 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
648 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
245 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
406 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
151 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?
