Questions tagged [google-sheets-arrayformula]

Use for questions about the use of the Google Sheets built-in function ARRAYFORMULA.

1
vote
1answer
8 views

Issue with finding the averages of multiple rows at once

I am currently tasked with hundreds of rows of data for a project. I need to take the average of each row, but there are way too many rows for me to do by each individual row. Is there any way to take ...
0
votes
2answers
6 views

Using IF functions with formatting in Google Sheets

I'm working on a Google Sheet and want to reformat all instances where the number in a cell is "2" and the color of the cell is pink. Is it possible to use information about cell formatting (here, e.g....
1
vote
2answers
27 views

How to generate all combinations of letters in a word?

Using Google Sheets, how would I show all possible ways to type a word, for example: word Word WOrd WORd WORD wOrd woRd worD without any length word? The given word would go on Row A and the ...
0
votes
1answer
23 views

Automatic merging of duplicate cells under common ID

I have data in column A with IDs and a column B with data for those IDs. Right now I have multiple (usually 2-3) rows with the same ID but different data. What formula to use to merge those into one ...
0
votes
0answers
63 views

Change an entire google sheets to UPPER case, not with an add-on or onEdit(e) [on hold]

I need to change all the text in Google Sheets to upper case, the problem is it's filled in by Zapier which doesn't register as an edit for onEdit functions. It's also automatically converted to .xlsx ...
0
votes
1answer
9 views

Google Sheet Query returns first 3 rows in one row

I have made a simple query that combines data from 4 separate sheets: =query({'1.MA'!A13:O200;'2.PF'!A13:O200;'3.BM'!A13:O200;'4.C'!A13:O200},"Select * where Col2 is not null ") The objective is to ...
1
vote
1answer
11 views

Google Finance Historical Data with Multiple Attributes?

=GOOGLEFINANCE("NSE:"&A2, "all",(TODAY()-($N$2)),TODAY(),"DAILY") The above code returns OHLC data for specified days with the date column. What I want are only high, low and close columns. =...
1
vote
1answer
17 views

LOOKUP for data from other sheet

So I have a drop-down menu in order sheet and I have an info sheet. In the info sheet, I have 2 columns, column A has the customer name, column B has the customer e-mail. So what I'd like to do is, ...
2
votes
1answer
403 views

Aggregate results by week and month for charts

Is there a way to convert daily values into weekly averages for charting? Sample source sheet --------------------------------------------------- | Data 1 | 1/1/2018 | 1/2/2018 | 1/3/2018 | etc......
0
votes
1answer
8 views

How to paste a comma separated string into one column in Google Sheets?

For example, if I have (bob, china, bing, bong). I want to put bob on top of china on top of bing on top of bong.
1
vote
1answer
24 views

How can I track the time it takes for someone to fill out a Google form?

I am running a study for my thesis, and would very much like to track the time it takes to complete a Google form to compare against how long it takes to fill.
1
vote
1answer
329 views

Range notation and current row

I wonder how does work the following and is it documented somewhere? I have the sheet: A B ----------------- 1 2 3 =$A:$A 4 5 =$A:$A For column B (row 3 and 5) I see ...
0
votes
1answer
16 views

Repeat formula n number of times in Google Sheets

Apologies, I'm very new to Google Sheets and spreadsheets in general. I'm trying to fill some cells with repeating data, and then analyze it. The data is pulled from another large spreadsheet and has ...
33
votes
3answers
23k views

Cumulative Sum without Script

For this question, I've created a Google Apps Script function, that calculates the cumulative sum of a range. Martin Hawksey on Google+ made a comment about a more efficient way of calculating the ...
0
votes
1answer
24 views

Fetch value of a cell based on dropdown menu items results on another sheet

I have 2 Sheets named as Sheet1 and Sheet2... Sheet1: Contains Drop down list on B4 Column. Based on the selection of an item, it pulls the result of the open price shown on D4 column. Sheet2: ...
1
vote
1answer
13 views

How to get cell's parent ID?

Firstly thank you for helping me out. I'm in the construction industry and don't have any clue how to best do this - not my forte! I'm trying to bulk add a bunch of building product categories to my ...
1
vote
1answer
11 views

Adding mm:ss times when expressed as TEXT

I have a series of numbers that, for a reason out of my control I can not change, are expressed as plain text strings on google sheets. The numbers are supposed to be minute second. mm:ss. For the ...
0
votes
1answer
13 views

Merging data sets from two tables with origin reference [duplicate]

In a Google Sheet, I have multiple tables containing a list of dates. I want to create a single (sorted) list containing all the dates and the table they came from. Table foo: 1.1.19 3.1.19 Table ...
1
vote
1answer
26 views

How to join VLOOKUP results of multiple sheets into one cell

I am trying to use TEXTJOIN across multiple sheets, IF a cell is is the same I would like it to return the value from another cell in all sheets that are associated with it. In this example, I am ...
1
vote
2answers
18 views

How to make sequential list of months?

I want to fill column names with a sequential list of the last day of each month. For example, 3/31/2018, 4/30/2018, 5/31/2018, etc. Is there a formula that can do this? If cell B1 = 3/31/2018, I ...
0
votes
1answer
28 views

How can I use ARRAYFORMULA (or something similar) with GOOGLETRANSLATE?

I'm learning a new language and I recently discovered Google Sheets' GOOGLETRANSLATE function. I thought it'd be neat to have a personal dictionary of translations which I can even annotate. So I ...
2
votes
1answer
611 views

How to block automatic row and column insertion in Google Sheets?

While playing around with array formulas, "sometimes" when the result has more rows and columns than the previously available in the sheet, Google Sheets automatically adds the row needed. I said "...
0
votes
2answers
23 views

Add columns based on rows

I'm currently working on setting up a sheet, but I want to add columns in one worksheet based on rows in another worksheet. I have no idea if this is even possible, I've tried googling it. Might be ...
1
vote
1answer
22 views

Filter and display duplicates in a table

Here's the link to my Google sheet: https://docs.google.com/spreadsheets/d/1SBD0_Ca0r_p3810u1ZNlbG6MXsvhFlLcwEJndVQJRP0/edit?usp=sharing The master data will show: And I would like to have a table ...
0
votes
1answer
11 views

Importrange (or maybe another part of formula) behaves differently after a short time period

I am using this formula to look for data in column F related to data in column A between two spreadsheets. =ARRAYFORMULA(IFERROR(VLOOKUP(A2:A; {IMPORTRANGE("spreadsheetID";"sheet1!A4:A")\ IMPORTRANGE(...
1
vote
1answer
16 views

ArrayFormula stops working after shuffling rows

I have a pretty extensive sheet wherein many columns I use an array formula to automatically fill data in from various other spreadsheets. The problem I have is when I need to rearrange rows then what ...
1
vote
1answer
32 views

How to import/merge data from from 2 separate sheets from another spreadsheet

I need to import data from "Spreadsheet A" to "Spreadsheet B" but all the data is in two separate sheets "MY" and "NY" in "Spreadsheet A" I basically need exactly this but with a function to import ...
1
vote
1answer
23 views

Comparing two cells and adding text to matching values

I have two columns, one with all items in a set and another with the items I currently have. I would like to compare those and for the matching results to have a bit of text added if they match. So ...
1
vote
1answer
13 views

Dynamically update cell contents by merging incoming Google Form submissions

I have a google form where multiple students will anonymously submit comments/feedback for another peer. Currently I am using the formula below to essentially concatenate all of the student comments ...
0
votes
1answer
15 views

Assigning rank in another sheet based on Average

I have a list of names with an average beside it for every day Monday-Friday. A subgroup of names within this list will be highlighted. I want a macro to assign ranks of the highlighted individuals ...
0
votes
2answers
14 views

Sum of LAST Column in Google Sheets

I'm wondering if there's a way to sum the last Column in a sheet. I add on data every month and have to re-do multiple formulas every time I do, as they're always based on the sum of the last column. ...
0
votes
1answer
37 views

Increment number in array formula based on a condition

I have a data set with Sub-Total rows at various intervals. As the data can have rows inserted or deleted, I am trying to use an array formula to give each group of data an incremented integer as a ...
0
votes
1answer
24 views

Autofill a cell when dates are entered in 3 other cells

I'm trying to create a way to keep track of our jobs by creating a Google spreadsheet. We can only order our final product when the digital and hard copy proof is approved and we have an order from ...
1
vote
1answer
19 views

IMPORTRANGE from a separate sheet based on closest date to today's date

Sheet1 will have data with different past dates Sheet2 - I want to import data from the 1st sheet and find the closest date to today's date. https://docs.google.com/spreadsheets/d/...
0
votes
1answer
9 views

Having a formula output to a different cell

I have a Google Sheet in the following format: ID | VAL ------------- 1 | dataH 2 | dataW 3 | dataX 4 | dataC 5 | dataG 6 | dataL ... | ... ID and VAL are on cells A1 and B1, ...
0
votes
1answer
10 views

How can I have a 2nd function start in the cell after the output of the 1st function?

So the problem I am trying to solve is where I have Google finance fetching the price of a stock and outputting it, but the length of cells of its price history is increased by 1 each day. In the ...
0
votes
2answers
6 views

Look for a value that might be in many columns

Say I have a value in $A, to match to another sheet of the same file. That value (in this case, a student id) might appear in many columns of the other sheet. In the following example, I am looking ...
0
votes
1answer
29 views

Create sequential dates with each date repeated multiple times

For every day of the year, I need the day repeated in 10 rows. How can I accomplish this efficiently? e.g. 01/01/2019 01/01/2019 01/01/2019 01/01/2019 01/01/2019 01/01/2019 01/01/2019 01/01/2019 01/...
1
vote
1answer
19 views

Using ARRAYFORMULA and QUERY to produce results in multiple columns

I would like to be able to group rows based on unique values and then output the AVERAGE and STDEV values of two different variables for those groups. Using the table below as an example, I would ...
1
vote
1answer
24 views

Google Sheets Conditional Formatting not working for all cells

This is my first question using StackExchange, so please bear with me. I am making a spreadsheet for tracking due dates for training. Sheet 1 shows due dates that people with view only capabilities ...
0
votes
1answer
7 views

How to convert an array of unique records into an array where records are repeated N times?

I have a Google sheet that records items and their quantities. In order to input them into our SalesForce system, I need to split each record into repeated rows, according to the quantity of the item. ...
1
vote
1answer
10 views

Concatenate - Show blank cell result if a cell is empty

Could someone help me? I am trying to create a social media keyphrase strategy using keywords. I would like to know if Google Sheets could return a blank cell if one of the cells in the concatenate ...
0
votes
1answer
42 views

How to use ARRAYFORMULA and UNIQUE together in Google Sheets

How can I expand the UNIQUE function using ARRAYFORMULA? Example: Here is my data set, also can be found here. I want to see the unique values of each row in next columns. CL1 | CL2 | CL3 | CL4 JAN |...
1
vote
1answer
707 views

Compare two lists and produce a list of names not duplicated

I have one list of total available people and the second list of people who have been assigned. I would like to auto-populate a third list of the people (from the first list) who have not been ...
0
votes
1answer
11 views

Display results of success over attempts in X/Y format

I am tracking the number of successes out of the total number of attempts. So, for example, Column A would be the number of successes. Column B would be the number of fails. I would like Column C to ...
0
votes
2answers
232 views

Match word in a cell with a column of words

I need to match a word to a list of words and then insert the category name in the cell next to it. Eg. I will in Column A type a list of words like Apple, Red Apple, Coke, Carrot The formula must ...
1
vote
1answer
8 views

Help using 2 parameters to output a specific category

I'm looking to be able to enter an "AGE" and a "V02" in a formula that tells me what "category" the person would be in ie; good, excellent, etc.
0
votes
1answer
13 views

How to auto-calculate without a need to drag down the formula?

How can I fix formula to a row so that when data is inputted in any cell within that column, it auto-computed in Google Sheets? the % doesn't work Without having to drag the formula down. Example: ...
40
votes
3answers
33k views

How to paste a comma separated string over multiple cells in Google Sheets?

Say I have a string of: l,f,x,a,s,f I would want to paste this every comma-separated value over 6 adjacent cells. How would I do it?
2
votes
4answers
7k views

How automatically to add spaces inbetween each character in a text string?

Teachers I work with, enter four scores for a student’s assessment in a single cell, like this: 2113, with no breaks or spaces. How can I separate those characters so as to be able to create a chart ...