Search code examples
excelexcel-formulacellsubtotal

Use calculated cell reference in formula

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.

Solution

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

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