Define end of range based on another cell value (offset?)

[Vexorg]

Hello,

I am trying to average a range of cells (F37:F'X'). I want 'X' to be defined by the number in cell B16. For example, if B16=10, the range that is averaged should be: Average(F37:F47).

How do I do this?

I assume its something along the lines of Average(Offset(F37,-B16,0) however this does not seem to work?

 

[DanteAmor]

Try:


=AVERAGE(INDIRECT("F37:F" & 37 + B16))

 

[Ron Coderre]

To avoid volatile functions, try something like this:

=AVERAGE(F37:INDEX(F:F,37+B16-1))

Is that something you can work with?

 

[Vexorg]

Try:


=AVERAGE(INDIRECT("F37:F" & 37 + B16))

This works thanks!

 

[Vexorg]

To avoid volatile functions, try something like this:

=AVERAGE(F37:INDEX(F:F,37+B16-1))

Is that something you can work with?

This worked too, thanks! I ended up adding the -1 to Dante's formula.

 

[DanteAmor]

This works thanks!

Youre welcome