1

I want to use SUBTOTAL to calculate the mean within a range without hard coding in the cell references.

I an currently using the standard:

=SUBTOTAL(1, A2:A11)

But I want Excel to recognise which cells in column A start and end possessing values. So I can specify cell A11 in a separate cell using:

=ADDRESS(MATCH(9.99999E+307, A:A), 1)

Which returns "$A$11". However, If I combine the above equations thus:

=SUBTOTAL(1, A2:ADDRESS(MATCH(9.99999E+307, A:A), 1))

I get an error. I have also tried INDIRECT in combinations with these but that does not work either.

pnuts
  • 58,317
  • 11
  • 87
  • 139
TSG
  • 13
  • 4

1 Answers1

1

Use the INDEX function to supply the latter half of the cell range.

=SUBTOTAL(1, A2:INDEX(A:A, MATCH(1e99, A:A)))