averaging particular blocks of data

[robert111]

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

[Don Guillett]

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

--
Don Guillett
SalesAid Software
[email protected]
"robert111" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>
>
> --
> robert111
> ------------------------------------------------------------------------
> robert111's Profile:
> http://www.excelforum.com/member.php...o&userid=31996
> View this thread: http://www.excelforum.com/showthread...hreadid=560642
>

[Bondi]

robert111 wrote:
> 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
>
>
> --
> robert111
> ------------------------------------------------------------------------
> robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996
> View this thread: http://www.excelforum.com/showthread...hreadid=560642

Hi Robert,

Maybe you can use something like this:

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

Regards,
Bondi

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

"robert111" wrote:

>
> 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
>
>
> --
> robert111
> ------------------------------------------------------------------------
> robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996
> View this thread: http://www.excelforum.com/showthread...hreadid=560642
>
>

[robert111]

many thanks to both of you

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

"robert111" wrote:

>
> many thanks to both of you
>
>
> --
> robert111
> ------------------------------------------------------------------------
> robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996
> View this thread: http://www.excelforum.com/showthread...hreadid=560642
>
>

[Don Guillett]

glad to help

--
Don Guillett
SalesAid Software
[email protected]
"robert111" <[email protected]> wrote in
message news:[email protected]...
>
> many thanks to both of you
>
>
> --
> robert111
> ------------------------------------------------------------------------
> robert111's Profile:
> http://www.excelforum.com/member.php...o&userid=31996
> View this thread: http://www.excelforum.com/showthread...hreadid=560642
>