After getting sick and tired of having to copy formulas back into my sheet anytime I needed to add a row (one of my gripes of Google Sheets where Excel is much better). I've decided to try using ARRAYFORMULA in row 2 of all my sheets to basically make column formulas. Google Support pages suggests this is an exact replacement for the functionality in Excel - it's not). Note that I don't think either Excel or Google does Column formulas well - but Excel definitely does it better then Google Sheets in this case.

Background

Just using ARRAYFORMULA with a known range works well anytime I add a row in the MIDDLE of that range. However, it doesn't work well when I add a new row to the end of my range that I want to be included. I have to manually change the last row in my ARRAYFORMULA formula if I add a row to the end, or I have to make my last row a "dummy" row with a note that says - don't add new rows, always add to the middle and hope other people using the sheet (or even myself) remember to abide by it. Using large sheets with lots of data, one person not following the rule can majorly screw it up for everyone sharing it. I like to have as much automated as possible to minimize costly mistakes.

I tried using ARRAYFORMULA using whole columns (e.g. A:A, B:B, etc.) but if it's a formula where I need a result output to each row (simple example: = ARRAYFORMULA ( C:C - 1), I get an #N/A result in the cell and the following error text:

Result was not automatically expanded, please insert more rows

My workaround for that was to add a cell in a hidden column on my sheets with the following formula:

= ARRAYFORMULA( MAX( IF( LEN(A:A), ROW(A:A), ) ) )

Note: Whole columns work here because I'm using the MAX function which then returns a single value.

I then name that cell something to the effect of last_XXXX_row where XXXX is a short version of the name of the sheet so I have a constant I can reference and know what the last active row of the sheet is. Then I protect the cell and hide it.

It gets a little annoying as now I have to use INDIRECT everywhere and the formulas get long, but for the most part it works. For example:

 = ARRAYFORMULA( ( $C$2:INDIRECT( "$C" & last_unit_row) = 1 ) )

on my "unit" sheet returns TRUE or FALSE based on whether the value in column C is equal to 1 or not and returns the corresponding result in each row of the column I put this in. It's kind of long, but now at least I don't have to enter the formula in every row and then re-enter the formula every time I add a row - whether in the middle or the end of the sheet, it automatically updates the column as I add them. Yay.

Issue

Some formulas do not work as a direct analog when using ARRAYFORMULA. For instance, I've learned that the INDEX function just does not work with ARRAYFORMULA at all - so have to avoid that. There's probably a few others I haven't tried yet.

My particular issue is in a column that needs to know something in the column above it. In both Excel and Google Sheets I often use a count up / reset column to track how many entries there are in a given category. For example, such a formula in column B dependent on a category value in column G typically looks like this:

= IF (G2 <> G1, 0, B1   1)

Then when I fill down with that formula, it automatically changes all cell references to the needed rows. It's checking a category label in column G - and if that label changes, it resets to 0 (sometimes I reset to 1, depending), otherwise it increments the value in column B. This is helpful when there isn't a uniform number of entries for each category and each entry needs a subindex.

I can't seem to get this to work using ARRAYFORMULA.

I tried this:

= ARRAYFORMULA( IF( $G2:INDIRECT( "$G$" & last_item_row ) <> $G1:INDIRECT( "$G$" & ( last_item_row - 1 ) ), 0, $B1:INDIRECT( "$B$" & ( last_item_row -1 ) ) ) )

And I get a #REF result in the cell with the error text:

Circular Dependency Detected. To resolve with iterative calculation, see File > Settings

So... it sort of makes sense as it appears that there's a reference to the cell the formula is in inside the range that's created by INDIRECT. However, if the formula was executed properly, it's would always calculate based on the cell ABOVE it and never actually use its own cell as part of the calculation.

If I could use INDEX instead of INDIRECT I ought to be able to avoid this, but I can't.

Am I overcomplicating this? Is there a simpler solution I'm not seeing? I'm trying to us COUNTIF as well [ Non-Array version of the formula that works when filled down: =COUNTIF($G$1:$G1,$G2) ], but haven't gotten it to work.

Closest ARRAYFORMULA version I have is this:

=ARRAYFORMULA( COUNTIF($G$1:($G1:INDIRECT( "$G$" & ( last_item_row - 1 ) ) ), $G2:INDIRECT( "$G$" & last_item_row ) ) )

I'm surprised that even worked at all - it returns array values, but it gets me the total number of times that category appears in every row, instead of just the ones leading up to that row.

CodePudding user response：

I tried using ARRAYFORMULA using whole columns (e.g. A:A, B:B, etc.) but if it's a formula where I need a result output to each row

you can always freeze it like:

=INDIRECT("A:A")

this way you can add rows anywhere you want (if you of course not add new row above the row that holds the formula - that would be troublesome to fit in A:A into A2:A)

that the INDEX function just does not work with ARRAYFORMULA at all

INDEX is already ARRAYFORMMULA type of formula. the analogy being here as: you need a car to get from A to B where INDEX is a blue car with 3 doors and ARRAYFORMULA is a red car with 5 doors - it doesn't matter what color you have, you just need a car

= IF (G2 <> G1, 0, B1 1)

while this is direct logic there are several ways how to achieve the same thing. proper usage would require not to use such formulas as ARRAYFORMULA in column G or B to avoid circular dependency errors. for a simple resetting count up try:

=ARRAYFORMULA(COUNTIFS(B1:B7, B1:B7, 
 SEQUENCE(ROWS(B1:B7)),"<="&SEQUENCE(ROWS(B1:B7))))

feel free to change B1:B7 to open range or frozen range...

CodePudding user response：

You should use the INDEX function with MATCH instead of INDIRECT in your ARRAYFORMULA For example, if your data is in the range A1:A11 , and you want to retrieve the value at row 5 through INDIRECT , you would use: =INDIRECT("A" & 5) If you want to retrieve the value at row 5 through INDEX , you would use: =INDEX(A:A, 5) Now, if you want to use the INDEX function with ARRAYFORMULA , you will need to use the MATCH function to find the row number of the value you want, since INDEX only takes a row number as its second argument. For example, if you want to retrieve the value at row 5 through ARRAYFORMULA and INDEX/MATCH , you would use: =ARRAYFORMULA(INDEX(A:A, MATCH(5, ROW(A:A)))) This formula will return the value in A5

Note: In your example, you're trying to match $G2 with $G1:$G1 . If you want to match $G2 with $G1:$G3 , you would use: =ARRAYFORMULA(INDEX(A:A, MATCH($G2, $G1:$G3))) This formula will return the value from A1 , A2 , or A3 depending on the value of $G2