Another dynamic range question

[Marles McDonald]

I have a data set which needs to have multiple ranges calculated
independently. The entire data set is 100 columns wide but the total row
cont and the row count of the sub sets is conditional.

For example I need to average the values in column D for the contiguous
number of rows calculated in cell HJ7 , which is not the total number
of rows with values in column D

The following formula seems like it should work but returns a value of 0

I understand why that is so since within the () the adress is returned
as "$D$10" with the quote marks

AVERAGE(ADDRESS(10,4)&":"&ADDRESS($HJ$7,4))

Is there some way around the "" marks or a better way of doing this.

I would prefer that this be done as a spreadsheet function rather than a
macro.

Marles

 

[Art Farrell]

Hi Marles,

Try it this way:

=AVERAGE(INDIRECT(ADDRESS(10,4)):INDIRECT(ADDRESS(10+$HJ$7,4)))

The Indirect will give the value of the Address function. Also, if I'm
interpreting correctly the $HJ$7 contains the number of rows to be added to
the start row of 10. If not, make your changes.

CHORDially,
Art Farrell

 

[Marles McDonald]

Thanks Art,

Worked perfectly,Knew it had to be realively easy, just couldn't wrap my
old brain around this one.

Marles