0

Some columns in my sheet are filled manually, but then two columns are created dynamically from an ARRAYFORMULA (i.e. there is nothing in the column if the manually populated cells are empty, but once they have some data, the other columns get their values populated automatically). To find the last value in a manually populated column, I can use a function like this:

=INDEX(H:H,COUNTA(H:H),1)

But if I use the same formula (adjusted for the right column) on the ARRAYFORMULA column, I don't get an error, but I also don't get any results.

I also tried to do the same with this:

=INDEX( FILTER( H:H , NOT( ISBLANK( H:H ) ) ) , ROWS( FILTER( H:H , NOT( ISBLANK( H:H ) ) ) ) )

This again works fine on a column that is manually populated, but those calculated from an array formula do not populate

In case it is useful, my array formula in I2 is this:

=ARRAYFORMULA(IF(B2:B, B2:B-H2:H, ""))

How do I find the last value in column I?

1
  • change your formula in I2 to:

    =ARRAYFORMULA(IF(B2:B, B2:B-H2:H, ))

  • then use this where you need to get last value:

    =QUERY(I2:I, "where I is not null offset "&COUNTA(I2:I)-1, 0)

    0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.