Thanks to all... Here's what I have at the moment:

=AVERAGE(INDIRECT("Z"&ROW()&":Z" & ROW()+$F$4))

The problem now is that if I insert a column before the Z column, everthing
gets screwed up... I need the "Z" reference in the INDIRECT statement to be
adjusted if I add or delete columns...

"Teethless mama" wrote:

=AVERAGE(Z5:INDIRECT("Z"&ROW(Z5)+$F$4))


"TBONE6540" wrote:

Goal: Average a number of cells based on a variable (located in $F$4) that
tells the function how many rows to average.

Currently the cells look like following and obviously average the next 10
rows, which is fine until I wish to change the average to include the next 15
or 20 or 100 rows instead of the hardcoded 10 rows.

cell y5 =AVERAGE(Z5:Z15)
cell y6 =AVERAGE(Z6:Z16)
cell y7 =AVERAGE(Z7:Z17)

So I'm trying to create a function that will allow me to use the value at
$F$4 to determine the "size" of the range
pseduo code is: =AVERAGE(Z5:Z(5+$F$4))

I get about this close: =AVERAGE(INDIRECT("Z" & FUNCTION(current_row) &
":Z" & FUNCTION(current row)+$F$4))

any and all help is appreciated...