Get Current Row for use in INDIRECT function

[Guest]

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...

 

[Héctor Miguel]

hi, ?

Goal: Average a number of cells based on a variable (located in $F$4)
that tells the function how many rows to average [...]
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...

assuming 'F4' includes 'starting' row for the 'sizeable' range...
how about: =average(offset(z5,,,$f$4,))

hth,
hector.

 

[T. Valko]

Another way:

=AVERAGE(Z5:INDEX(Z5:Z1000,F$4))

Adjust for the end of the range.

If F4 is empty the entire range will be calculated.

Biff

 

[Guest]

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

 

[Guest]

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...

 

[Héctor Miguel]

hi, !

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...

AFAIK indirect function uses *frozen*/text references
it does not care if you insert/delete rows/columns... it will point always to the quoted text

you have other approaches as for to give them a try
or you can play with other information functions [colum, address, cell, etc.]
and -probably- you will get a *kilometrical* formula at the end -?-

hth,
hector.

 

[Guest]

If any column insertion is AFTER column F, use Biff's formula:

=AVERAGE(Z5:INDEX(Z5:Z1000,F$4))

Insertion before F will change $F$4 reference.

 

[Harlan Grove]

Another way:

=AVERAGE(Z5:INDEX(Z5:Z1000,F$4))

Adjust for the end of the range.

If F4 is empty the entire range will be calculated.

....

Which could be trapped using

=AVERAGE(Z5:INDEX(Z5:Z1000,MAX(1,F$4)))

which also implicitly handles F4 containing non-numeric text.