Questions tagged [worksheet-function]
This tag is for spreadsheet questions where the question or answers involve an in-the-cell worksheet function.
0
votes
1answer
22 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)...
0
votes
1answer
11 views
How to reference a Google Sheet by filename?
How to reference a Google Sheet by filename?
I would like to use IMPORTRANGE function, but it can only reference a file by URL not by filename.
Example filename:
2018-11.xlsx
2018-12.xlsx
0
votes
0answers
15 views
How do I interleave rows of data in Google Sheets or Excel
I have data on several different worksheets and I want to combine into single sheet. The data is text, not numeric. I can do this work in either Google Sheets or Excel.
I have:
Sheet1 R1
Sheet1 ...
0
votes
0answers
15 views
Google Sheet - Help returning 'Top Earner' each month
I have a sheet logging participation each month by members with multiple accounts, initially used the top account to track the top earner but after adding a tab to consolidate accounts by member I'm ...
1
vote
0answers
34 views
Difficulty appending to cell in Google Sheets
My goal is to automate making a calendar on Google Sheets. I used =DATE(C1,A1,B1) to make a date and then each following cell, I added 1 to the previous cell.
However, now I want to also automate ...
0
votes
2answers
45 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
29 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
37 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
39 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
46 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
453 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
2answers
34 views
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
1k 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
79 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
72 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
32 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
66 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
421 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
203 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
26 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
90 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
42 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
1k 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
33 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
164 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
35 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
62 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
45 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
63 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
10 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
26 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
91 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
72 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
284 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
627 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
380 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
33 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
459 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
726 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
92 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
265 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
554 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
2k 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
667 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
116 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
37 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, ...
