Questions tagged [google-sheets-arrayformula]
Use for questions about the use of the Google Sheets built-in function ARRAYFORMULA.
814
questions
1
vote
1
answer
4k
views
Using SUMPRODUCT with ARRAYFORMULA
I have the following random data in sheet one
A orange
D yellow
C yellow
B orange
B orange
C blue
A yellow
D blue
C orange
C blue
In sheet two I use the following query function:
A1=...
- 11
1
vote
1
answer
3k
views
ArrayFormula to compute running average for groups of rows
Is it possible to write an array formula that calculates the running average of the Amount column for each of the Name groups in the following sheet?
The rows are sorted by Name. In Column C, I have ...
- 13
2
votes
2
answers
2k
views
Appending text to each column in Google Sheets
I have a Google Sheets that has about 200 columns. Each one is a name, and to each one, I want to append -banner.jpg. For example:
abcde
fghij
klmno
will all be updated to:
abcde-banner.jpg
fghij-...
- 530
2
votes
2
answers
3k
views
How can I fill the diagonal of a matrix with zeros?
I have an empty matrix:
I would like to fill the diagonal of a matrix with zeros:
Is there any clever way to do it?
The matrix can be pretty large and it's tedious to do this manually. I could use ...
- 16.6k
145
votes
3
answers
135k
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?
- 1,567
3
votes
1
answer
2k
views
Unique Values From a Matrix
I have a spreadsheet that looks like this:
Column A | Column B
| An unimportant value
1 | Value 1, Value 2
2 |
3 | Value 3
4 | Value 3, Value 1
| Don't ...
10
votes
5
answers
45k
views
Count the number of words in a string in Google Sheets
I have a simple Google spreadsheet with a number of text strings. I just want the number of words contained in each cell. Is there an in-built Google spreadsheet function I may use?
Nishant
4
votes
5
answers
25k
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 ...
- 41
1
vote
3
answers
4k
views
How can I set the increment value for Google Spreadsheets to auto-increment?
OP Edit: I think I want Sheets to behave in a way it's not intended.
I expected Sheets to recognize the incremented value in my formula the way I believe it recognizes the increment value in a ...
- 113
5
votes
2
answers
9k
views
Filter() - referencing source array in criteria
The filter() function looks through a source array, and filters it according to condition
=FILTER(A:A,ISNUMBER(A:A)).
My source array is a rather long formula, so I end up repeating the same formula ...
- 73
9
votes
6
answers
29k
views
Split content of one row into multiple rows?
I have a table where each row contains data like this:
Team name | First member | Second member | Third member | Team description
Would it be possible to transform/break this data so that each row ...
- 1,854
42
votes
6
answers
36k
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 ...
- 22.5k
36
votes
9
answers
244k
views
How to Fill a Column with Sequential Dates in Google Sheets
In Excel, you can right-click drag a date downwards to fill a column with the next dates in the sequence. For example, if you enter 9/1/2014, select that cell, then right-click drag down over the ...
- 521
2
votes
2
answers
40k
views
Google Spreadsheet formula, MINUS?
I have a spreadsheet with 3 columns:
| ITEM | COST | SUBTOTAL |
Subtotal, for example, is 1000 to start, like:
--------------------------
| ITEM | COST | SUBTOTAL |
--------------------------
| ...
- 289