averaging particular blocks of data

[robert111]

hi,
I have 50 consecutive numbers in column A. I want to find the averag
of any required consecutive block, ie rows 3 to 11, or rows 8 to 51
The numbers of the start and end row are in cells D1 and E1, and chang
according to data on another sheet. I prefer a formula solution rathe
than macros, if it can be done. I am trying to understand indirect an
address functions as I suspect they may be involved.
thanks

Rober

 

[Don Guillett]

where d1 is 3 and e1 is 11
=AVERAGE(INDIRECT("a"&d1&":a"&e1))

 

[Bondi]

hi,
I have 50 consecutive numbers in column A. I want to find the average
of any required consecutive block, ie rows 3 to 11, or rows 8 to 51.
The numbers of the start and end row are in cells D1 and E1, and change
according to data on another sheet. I prefer a formula solution rather
than macros, if it can be done. I am trying to understand indirect and
address functions as I suspect they may be involved.
thanks

Robert

Hi Robert,

Maybe you can use something like this:

=AVERAGE(INDIRECT("A"&D1&":A"&E1))

Regards,
Bondi

 

[Guest]

Where you want the average to show up, enter this formula:
=AVERAGE(INDIRECT(D1):INDIRECT(E1))

INDIRECT() takes the address of a cell that contains yet another address and
uses the contents to determine where to really go. So if you had A1 in D1
you end up starting at A1 for your average values.

Another way of looking at INDIRECT - you want to send a letter to
someone(Bill), but you don't know their address. But you know that another
friend, Andy, does know the address, so you go to Andy and ask him for Bill's
address, which he gives to you.

 

[robert111]

many thanks to both of yo

 

[Guest]

I believe the formula that Don Guillett put up will do it for you - I didn't
realize, until I looked at his, that you'd said you just had row numbers in
D1 and E1. I'd let myself get distracted by the word 'address' and thought
the whole thing was in each of those.

 

[Don Guillett]

glad to help