Using cell values to determine formula range

[milkychips]

Hi

Please see this imageFormula question.png

In cell H1 I would like to use the subtotal formula to calculate the average of all values ranging from the Letter/Number that I typed in cell G1, so in this case A17:A1000.

Is there an easy way of doing this without having to type the A17 manually into the H1 formula?

Thanks
Marco

[Pete_UK]

I can't see what formula you have, as not everyone can view .png files on this forum - attach an Excel workbook instead.

You would generally use the INDIRCT function for this.

Hope this helps.

Pete

[BadlySpelledBuoy]

Have a look for how to use INDIRECT.

BSB

[milkychips]

Attached, thanks Pete

[Pete_UK]

You can use this formula in H1:

=SUBTOTAL(1,INDIRECT(G1&":A1000"))

Hope this helps.

Pete

[milkychips]

Certainly does, I was trying to do it myself but couldn't quite get it to work, thanks!

[Pete_UK]

Glad to help - thanks for the rep.

Pete