Getting a relative cell reference using ARRAYFORMULA?

1
votes

An ArrayFormula is used to repeat the same formula in several different (contiguous) locations - right?

What I'm trying to do is use OFFSET to get the value of a cell that is positioned relatively to the current ArrayFormula cell.

So, for example, each cell in the ArrayFormula should be able to display the value of the cell to its left as follows (assuming the ArrayFormula is from A2 to A20):

=ArrayFormula(offset($A$2:$A$20, 0,-1, 1, 1))

This doesn't work - it just displays the value of the cell that's left of A2, for all cells in the ArrayFormula.

How can I make this work? Any sort of relative position-based collection is fine, I've even tried indirect("R[0]C[-1]", FALSE), and indirect(address(row(),column()-1)) but it's the same result again for both.

An example of what I'm trying to achieve can be found here: https://docs.google.com/spreadsheets/d/1qfGuiTLxPfUCjFh6tVtgofUNYvFm5jpqry--uxHEmaI/edit?usp=sharing

In this example I'm trying to use OFFSET to get a range of 3 values, starting from the cell that's one up and one left of the current cell, and ending with the cell that's one down and one left. Then I'm trying to take the COUNTA of that range.

Column Data contains a mixture of empty and non-empty cells. Column Formula contains my ArrayFormula. Column Expectation contains static values which are the correct value for the respective cell in the column to the left.

As you can see, all cells in Formula output 3, which is the correct value for B2.

(Note that in this example I've added if(row(A2:A29), to the start of the formula, to force ArrayFormula to work)

1
if-statementgoogle-sheetsoffsetgoogle-sheets-formulaarray-formulas
I used the same formula, just without the ArrayFormula part, and dragged it down to iterate it across the rows (y'know, the manual way). The formula used for that was =counta(offset(B2, -1, -1, 3, 1)). It's the count of the number of values in Column A from the current row, 1 row above, and 1 row below. I'd like to make that into an ArrayFormula. – snazzybouche

1 Answers

1
votes
=ARRAYFORMULA(IFERROR(
  IF(INDIRECT("A1:A"&MAX(IF(A:A<>"", ROW(A:A), )))<>"", 1, )+
 {IF(INDIRECT("A2:A"&MAX(IF(A:A<>"", ROW(A:A), )))<>"", 1, ); ""}+
 {IF(INDIRECT("A3:A"&MAX(IF(A:A<>"", ROW(A:A), )))<>"", 1, ); ""}))

0