averaging particular blocks of data

R

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
 
B

Bondi

robert111 said:
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
Click to expand...

Hi Robert,

Maybe you can use something like this:

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

Regards,
Bondi
 
J

JLatham

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

JLatham

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