Questions tagged [google-sheets-arrayformula]
Use for questions about the use of the Google Sheets built-in function ARRAYFORMULA.
0
votes
1answer
13 views
Simple Scorecard Conditional Formatting
I simply want to create a scoresheet comparing scores to par. If less than par then a red number, if more than par then highlight red, if equal to par then highlight green. Here is what I'm trying ...
0
votes
1answer
19 views
Grabbing only non-empty cells from a row range for every row
I have a survey that sends responses into a Google sheet. The raw data that is inserted is messy, so I have another sheet that processes the incoming raw data.
One of the things I need to do is: from ...
0
votes
2answers
13 views
Format cell if all the cells in a row are not empty
I tried all kind of formula but they all seem to work the other way around for me. I have a very large sheet with lots of columns. I'd like to have the first cell in the row to be a certain color if ...
1
vote
1answer
20 views
How to convert multi-value answers into single column in Google Forms' response sheet?
I have a Google form that asks for Name and up to 3 Score. The data is dumped into a sheet like this
But that data is not very convenient. I want to have the data like this...
Can I do that by ...
1
vote
1answer
11 views
Formating a number with specific spacing
I'm attempting to pull a particular option value from Yahoo! into Google Sheets. The base URL is: https://finance.yahoo.com/quote/GOOG190418C00800000
When breaking the formula up, I have to ...
1
vote
2answers
19 views
How can I create a recursive formula in Google Sheets?
I'm making a task tracker in Google Sheets and am trying to implement subtasks.
I am trying to create a formula that checks a row's "Parent Task" column, and if there is a parent task, indents the ...
1
vote
1answer
18 views
Total Wages Calculations
https://docs.google.com/spreadsheets/d/13HdNN_qa9rhLFyMe0N9I9EusmBudi_dcdBkc6UeLHMM/edit#gid=878850570
I am trying to total Normal Pay but the formula does not Multiply Regular Hours by Normal Rate ...
0
votes
1answer
23 views
Find previous non-blank cell, but without a specified range in Google Sheets
I've come across plenty of formulas to find blank cells within a certain specified range, but can't seem to figure this out / find an answer.
I have a spreadsheet kind of like this:
.
Where column ...
0
votes
1answer
7 views
How do I insert different cell data from a table into a hyperlink?
I have a table of different cell data that I want to insert into different hyperlinks for each item:
=HYPERLINK("https://www.otcmarkets.com/stock/CELL DATA TO BE INSERTED/security","SECURITY")
Cell ...
0
votes
1answer
7 views
How to ignore certain strings when creating unique list of words in Google Sheets?
In Google Sheets, I am using the formula to create a unique list of words:
=unique(transpose(split(ArrayFormula(concatenate(B2:E"+")),"+")))
But I want to filter out certain strings.
My unique list ...
0
votes
2answers
7 views
How to create a unique list from a selected range of cells
I have a list in cells A1:A16 of foods and want to create a unique list with each item only appearing once in the list:
Eggs
Bacon
Hashbrowns
Baked Beans
Avocado
Coffee
Bread
Bread
Sandwhich meet
...
0
votes
1answer
18 views
Method to preserve cell reference in a formula upon adding rows in reference sheet?
I have a Google Sheets with two(2) tabs. The first sheet is just a running tally of raw data and is updated with new rows at the top weekly.
In tab 2 there is just a single query(arrayformula)... ...
1
vote
1answer
16 views
Selecting row with best value for items with several versions/upgrades
I'm sure my problem is quite simple to solve, but I've been on it for a while and I could use some help.
I have a huge table, but the problem boils down to this: some items have 2 versions (one ...
1
vote
1answer
11 views
Autopopulate a RANGE of Cells from one sheet (tab) to another in a single workbook
All I want to do is have cells in one sheet (tab) in a workbook auto-populate with data and formatting of a different sheet (tab) in the same workbook. I know how to do this one cell at a time, BUT is ...
0
votes
2answers
12 views
How can I group by one column to join values in multiple rows?
Let's say I have a survey of people in teams, and what fruits they like:
User | Team | Fruits
----------------------
Dave | Mine | Banana, Apple
Steve | Mine | Apple, Orange
Matt | Yours | ...
0
votes
5answers
26 views
Layperson's grid to normalized table
Introduction (too long, don't read)
I have a data-arrangement problem. I'm now up to three qualifying files from coworkers, and for that matter, I've run into a couple of questions here on SE ...
1
vote
1answer
8 views
Finding the number of instructors available on different weekends in Google Sheets
I'd like to find a generic way to see when resources are available, in order to help plan when activities can be run. In this case, the resources are people with certain instructor qualifications (...
1
vote
2answers
19 views
Google Sheets monthly rolling average
I have a table in Google Sheets with data for different people by month. I want to display a monthly average that will be progressive as the year goes by. So they can have an idea of how they are ...
0
votes
1answer
7 views
Odd result in Google Sheets calculation
I am editing an existing spreadsheet in Google Drive and the formula below is giving me an answer I cannot figure out.
=if(isBlank({H6}),ifError(1/0),(( ArrayFormula( ROUND((P6*1.2)))-0.05)))
where ...
0
votes
1answer
7 views
How do I copy over a value from another worksheet depending on the adjacent column?
I have two checklists of the same items in a different order in two separate sheets, and I want to only update one. Basically, I have two pages that look like this
And I want the? to be automatically ...
0
votes
1answer
13 views
Replace value in field automatically (auto-correct?)
I'd like to replace occurrences found in Column A (cells A2:A200) so that if I enter 2/02/002 or 343 or 98/098 it auto-replaces it with UKD002/UKD343/UKD098.
Is this possible?
0
votes
1answer
14 views
Collates the number of depots that don't have manifests and trunk headers
Trying to do a work project that collates the number of depots that don't have manifests and trunk headers.
Column A: Depot Numbers
Column B: No Manifest record (i.e if Colum B 2 - 200 contains "...
0
votes
2answers
9 views
Update each cell in column from upper right adjacent cell
How can I make the start mileage in each cell update from the end mileage in the upper right adjacent cell? Preferably using something like a single array formula that can be updated.
I think I'm ...
1
vote
2answers
10 views
How do I automatically enter sequential numbers without using the mouse in Google Sheets?
I would like to create a series of number in Google Sheets, without dragging with the mouse.
When using the Shortcut Cmd+R (Fill Right) it just copies the value of the first cell.
Initial State:
...
1
vote
2answers
21 views
How to pull last value in a Google Sheets column and populate it across another worksheet column?
This is my first post here- I hope this is the right place for it, apologies in advance if it isn't.
Context:
I have a password protected presentation that I need to give users access to, but only ...
1
vote
1answer
18 views
How to filter only the highest value for each date based in a criteria?
I have a table with 3 columns. I want to filter only the highest value of column B for each date (A) if column C contains the code "red".
My sheet:
date entries code
08/04/2019 1 red
08/04/...
0
votes
1answer
10 views
How do I arrange raw data in Google spreadsheet in group way?
I have a list of orders, like this:
Paid $28.00 | 2019-04-08 15:14:20
Unpaid $00.00 | 2019-04-08 15:13:48
Unpaid $00.00 | 2019-04-08 15:12:58
Unpaid $00.00 | 2019-04-08 15:12:10
Paid $18.00 |...
0
votes
1answer
5 views
How to split a cell into key/value rows?
Apple,3,Orange,4,Mango,10
How can I split this cell into key/value rows?
Apple (a1), 3(a2)
Orange (b1), 4(b2)
If I just call split(), I get:
Apple
3
Orange
4
0
votes
1answer
10 views
Outputting a cell x number of times in an array format
Using Google Sheets:
Need to display a vertical list, where each occurrence in the A column of sheet1 is repeated based on the number in the B column of sheet1.
I've created a dummy file to show what ...
0
votes
1answer
8 views
How can a forumula be converterd to array formula in Google Sheets?
How can you convert these to array formula?
=IF(COUNTIF($I:$I, $A:A)=0, "No", "Yes")
and
=CONCATENATE(C3," ",D3)
0
votes
1answer
12 views
Value transformation in Google Sheets
I'm working with a table of data as following:
User ID: Event name:
111 Event 1
222 Event 2
333 Event 3
111 Event 4
444 Event 5
111 ...
0
votes
1answer
5 views
Creating a formula based on TODAY's day
I have column A (list of stock prices at close of day)
A1=4/1/2019 | stock price
A2=4/2/2019 | stock price
A3=4/3/2019 | stock price
each day will be added to the sheet automatically
I want to take ...
1
vote
1answer
21 views
How to get the largest initial values of multiplied columns?
I'm tracking performance for some athletes and we need a way to see their "Best" workout numbers meaning the highest numbers of (rep × weight).
The table looks like this:
The reps and the weight are ...
1
vote
2answers
16 views
Google Sheets - Convert entire column of prices by 50%
I have a large column of prices, however, sometimes the word [FIXED] also appears in these columns (see screenshot below).
I need to convert this price list to wholesale. All the products wholesale ...
0
votes
1answer
19 views
How do I keep leading zeros in Google Sheets after split?
This is my data:
43558#DEYINGCHEN20190403#097855111593#Logitech MX Master Wireless Mouse (910-004337)
Then I used split(#). Result:
The result I want is：09785111593
This cell I have formatted into ...
1
vote
1answer
30 views
Subtract 8 hours from time, if time is more 8 hours
I am trying to create a Timesheet using Google Sheet but this formula =IF((F8>8),(F8-8),"0") returns zero even on cells with Total Hours greater than 8
I have set both Formats to Duration
0
votes
1answer
17 views
ArrayFormula date vlookup -30days in first 30 rows of Google sheets
I am trying to use an array in Google Forms Response sheet for a time sheet. I want to have a rolling 30 day total for hours worked. The first 30 days is not able to lookup 30days prior.
={"Previous-...
0
votes
1answer
16 views
Importrange doesn't import data from extensive sheets
The formula works on lower column number that I need to get data from but nothing is happening when I need to import data from columns with a higher number.
Following formula works (data imported ...
1
vote
1answer
10 views
Rounding in an array equation?
I'm hoping someone can help me out...
Is there a way to round:
=ARRAYFORMULA( (If(ISBLANK(A2:A),"", (H2:H*10^-3)/((E2:E*10^-3)*(F2:F*10^-3)*(G2:G*10^-3)))))
to two decimal places in the equation ...
1
vote
1answer
19 views
Using a single cell serving as an ID source for formulas to import data from
I have my master Google sheet and I need a weekly updated Excel report to be imported into that sheet. Right now I use
=ARRAYFORMULA(IFERROR(VLOOKUP(A2:A; IMPORTRANGE("XYZ"; "'Overview'!A4:FZ"); 10; ...
0
votes
3answers
23 views
Summing every other row depending on the classification of the row above
I've got a confusing question, I can't figure it out. I have this spreadsheet of states population and their political control, each on separate alternating rows (political control is designated with ...
0
votes
1answer
29 views
Summing hours spent on a subject in Google Sheets' “Assignment Tracker”
Hi everyone!
I love the idea of this Assignment Tracker in Google Sheets. But my goal for time management is to find out how much time I'm spending on each subject.
Is there a way to automatically ...
0
votes
1answer
24 views
Linking a dynamic inventory price list to another file in Google Sheets
I've got a master inventory price sheet for all the ingredients we use in our recipes that I want to reference in all our recipe files to dynamically calculate costs. This way if we change suppliers ...
1
vote
2answers
48 views
How do I get the count number of weekdays in a datetime column in Google Sheets?
I have a timestamp in mm/dd/yy hh:mm:ss format from January to March. How do I count "how many days are Monday" in the range of A1:A199? (Or How Many are Tuesday, Wednesday, Saturday?
This gives me a ...
0
votes
1answer
8 views
Cross Comparing values between two columns for voting purposes
The current content of our Google sheet, is formatted as such:
- PERSON 1 | PERSON 2 | YES
- PERSON 1 | PERSON 3 | YES
- PERSON 2 | PERSON 1 | YES
- PERSON 2 | PERSON 3 | YES
- PERSON 3 | PERSON 1 | ...
0
votes
1answer
29 views
Auto sorting / copying data based on inputted data?
Sorry if this is a bit confusing or if I’m not explaining this well enough. Using my provided picture as an example how would one auto copy data that gets inputted into that table based on estimated ...
0
votes
1answer
14 views
SUM across other worksheets (without hardcoding their names)
I've got a spreadsheet for my organisation's management accounts. Each project has a worksheet, setting out income and expenditure by financial year. This is aggregated together in a few sheets ...
0
votes
1answer
15 views
Convert string to URL in column
I just want to convert IDs as strings to URLs automatically. I tried this:
=HYPERLINK(CONCATENATE("http://myjira.com/projects/BUWDI/issues/";A2);A2)
I got an error...
What do you think?
0
votes
1answer
19 views
ImportRange not pulling in Dates from first sheet in correct format
I'm trying to pull in a date cell from one sheet to another with an importrange function:
=iferror(query(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1UClhpev01z-...
0
votes
2answers
33 views
Typing in a particular cell and convert it in upper case [closed]
After I typed in a cell a text value (this is in lower case), what do I need to do (after typed it), to become it as the upper case?
How do you change the case to upper case for a whole column in ...
