array-formulas Questions
5
Solved
I have (what I thought was) a simple lookup table holding some exchange rates. There I am looking up values based on row and column indices.
How can I do that in an ARRAYFORMULA like this?:
=ARRA...
Gatha asked 7/1, 2017 at 22:7
5
Solved
With data in A1 - B5:
A 1 //remove
A 2
B 3 //remove
B 2
C 1
How do you remove duplicates in column A, keeping the last set of values in other columns? Results should look like this:
A 2
B 2
C 1
I...
Flighty asked 25/4 at 20:14
7
Solved
In Google spreadsheets, I need a formula to extract all digits (0 to 9) contained into an arbitrary string, that might contain any possible character and put them into a single cell.
Examples (Inp...
Rotenone asked 19/1, 2017 at 21:36
4
Solved
I have a Google spreadsheet with basically a dictionary of key/value pairs. Keys are unique. In a second sheet I need to add one or more rows for every key in the dictionary with some data. In this...
Hallel asked 3/9, 2015 at 17:47
25
Solved
I have a spreadsheet with some values in column G. Some cells are empty in between, and I need to get the last value from that column into another cell.
Something like:
=LAST(G2:G9999)
except t...
Wessex asked 12/11, 2010 at 23:21
7
Solved
I have a set of columns set up in Google Sheets that display the difference between two dates using the DATEDIF function:
=DATEDIF (AS2, TODAY(), "D")
(number of days passed today since a...
Antinucleon asked 18/3, 2019 at 12:14
4
Solved
Usually don't need help with sheets but I think my brain is imploding from thinking on this too much.
Trying to fill an entire column with an array formula that sums values from a separate column b...
Abutment asked 6/5, 2015 at 15:42
6
Solved
I have a simple array formula in excel that doesn't work in the way I wish. In columns A and B there is data (A1 is paired with B1 and so on) while in column F there is the calculation based on the...
Mccarthyism asked 21/8, 2015 at 17:32
4
I tried to use the MATCH function in an array formula to return multiple matches (by default it only returns the first match). However, this doesn't seem to work. How can I solve this problem witho...
Lodge asked 4/7, 2015 at 16:38
2
Solved
I'm using VBA, and I need to insert an array formula (the one that if I'm writing it manually, I'll press Ctrl+Shift+Enter and not just Enter). When I'm inserting it like a regular formula it doesn...
Glottalized asked 22/12, 2014 at 13:53
17
I'm changing all the zip codes from Column A into Column B with the formula:
=TEXT(A1,"00000")
Like this:
I want every cell in Column B to be transformed using the formula above. But I ...
Penthouse asked 24/7, 2014 at 18:20
4
Solved
In my spreadsheet, I have a running script, which is using the getLastRow() function as an essential part of its logic.
Ever since I applied the array formula in one of my columns, the getLastRow...
Nepali asked 23/10, 2017 at 7:22
3
Solved
Hy Guys,
i'm triyng to merge two different Tab of the same Google SpreadSheet.
I'm using this formula in the first cell
={indirect(dynamicRange1);indirect(dynamicRange2)}
where dynamicRange 1...
Derinna asked 5/4, 2018 at 17:33
6
Solved
Here's my way of calculating running count by groups in Sheets:
=LAMBDA(a,INDEX(if(a="",,COUNTIFS(a,a,row(a),"<="&row(a)))))(B4:B)
The complexity of this formula is R^2 =...
Gyrostatics asked 10/11, 2022 at 12:55
5
Solved
I have a list of numbers entered manually:
1
100
100
10
1
1000
10
1000
1
1000
100
10
I'd like to get the running max - the max value for each sub-array of the initial numbers array. By sub-array I...
Drachm asked 4/1, 2022 at 12:20
4
In Google Sheets, when using ArrayFormula with AND formula, I don't get the results as it should be.
A
B
6
7
In C1 I put formula as: =and(A1>5,B1>6) then I get True. If in D1 I pu...
Alida asked 30/4, 2017 at 20:3
5
Solved
I'm using Google sheets for data entry that auto-populates data from my website whenever someone submits to a form. The user's data imports into my sheet with a timestamp (column A).
Using the Arr...
Becharm asked 4/4, 2016 at 21:39
6
In column A I have list of different names. In column B, I have values either 0 or 1.
I want to get a count of all the unique names from column A which have 1 in column B.
Using below array formu...
Pediment asked 11/4, 2013 at 8:39
4
Solved
Using array formula, how to divide each character into a cell.
Input Output
cat c a t
dog d o g
horse h o r s e
tiger t i g e r
Geometrid asked 26/9, 2022 at 10:27
6
Solved
as per example:
A B C D E F G ∞
|======|=======|=====|=====|=====|=====|=====|=====
1 | |AVERAGE| | | | | |
|======|=======|=====|=====|=====|=====|=====|=====
2 | xx 1 | | 1 | 2 | 0.5 | 10 | |...
Yardman asked 24/12, 2020 at 7:33
2
Solved
I would like an array formula to sum up columns G26:AD per row, since the amount of rows is dynamic.
=ArrayFormula(sum(G26:26))
does not work. I tried using MMULT but did not manage to get it w...
Wooton asked 1/9, 2017 at 12:35
3
Solved
First of all, i'm not a powerful sheets user :)
I'm trying to use GOOGLEFINANCE to calculate amounts in multiple currencies.
I use this formula:
=IF($A2;
IF(
$C2:C;
$C2:C;
IF(
$D2:D;
$D2:D*I...
Haemoid asked 10/12, 2019 at 18:13
2
Solved
=ARRAYFORMULA(IF(ROW(B:B)=1, "Share Price", IF(ISBLANK(B:B), "", googlefinance(B:B, "price"))))
I am trying to make row 1 (my header row) have a name, and then below that, if a valid ticker symbo...
Cassy asked 26/3, 2020 at 19:6
4
Solved
Have just noticed isDate does not work in arrayformula.
Case
Want to filter all values if dates:
Used the formula:
=FILTER(data,ISDATE(data))
Expected result:
8/28/2018
Got:
#N/A
Ques...
Universally asked 28/8, 2018 at 12:9
5
I've seen some examples of using SUM() inside an ARRAYFORMULA() in Google Spreadsheets (and oddly enough, they all seem like workarounds) but I can't figure out how to apply them to using MIN() ins...
Potamic asked 27/4, 2015 at 4:4
1 Next >
© 2022 - 2024 — McMap. All rights reserved.