We have an excel spreadsheet with constant data entered. We are trying to
formulate a constant average of these points and having no luck. Is there a
way to set up a formula that will consistantly average points? An example of
what we are looking for would be averaging (A1:A180) and then the next
average would be (A181:A361). Can somebody help me figure out how to make
this a consistant formula without having to constantly put in the next round
of cells?
Lori

tiggs96 wrote:
> We have an excel spreadsheet with constant data entered. We are trying to
> formulate a constant average of these points and having no luck. Is there a
> way to set up a formula that will consistantly average points? An example of
> what we are looking for would be averaging (A1:A180) and then the next
> average would be (A181:A361). Can somebody help me figure out how to make
> this a consistant formula without having to constantly put in the next round
> of cells?
> Lori

Assuming you meant (A181:A360) for the second average, put this in B1 and copy
down as needed:

=AVERAGE(INDIRECT("A"&(ROW()-1)*180+1&":A"&(ROW()-1)*180+180))

I'm assuming you want an average of 180 cells all the time (your second array
of 181:361 is actually 181 cells)

=AVERAGE(INDIRECT("A"&(ROW(A1)-1)*180+1&":A"&ROW(A1)*180))

As you copy this down, it will average 1:180, 181:360, 361:540, etc.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"tiggs96" wrote:

> We have an excel spreadsheet with constant data entered. We are trying to
> formulate a constant average of these points and having no luck. Is there a
> way to set up a formula that will consistantly average points? An example of
> what we are looking for would be averaging (A1:A180) and then the next
> average would be (A181:A361). Can somebody help me figure out how to make
> this a consistant formula without having to constantly put in the next round
> of cells?
> Lori

Where do you want the formula? Which cell?

Enter this in B1
=AVERAGE(INDIRECT("$A1:A"&LOOKUP(2,1/A:A,ROW(A:A))))

This will give you the average of numbers in Col A

"tiggs96" wrote:

> We have an excel spreadsheet with constant data entered. We are trying to
> formulate a constant average of these points and having no luck. Is there a
> way to set up a formula that will consistantly average points? An example of
> what we are looking for would be averaging (A1:A180) and then the next
> average would be (A181:A361). Can somebody help me figure out how to make
> this a consistant formula without having to constantly put in the next round
> of cells?
> Lori

Suppose you use C1 to contain a number like 1, 2, 3 etc which is the
set of data you want to average (each of them covering 180 cells).
Then you could use this formula:

=AVERAGE(INDIRECT("A"&((C1-1)*180+1)&":A"&(C1*180)))

Then, with the following values of C1, your formula would evaluate as
shown:

C1 Formula range
1 A1:A180
2 A181:A360
3 A361:A540
4 A541:A720
etc

Hope this helps.

Pete

On Feb 23, 3:58*pm, tiggs96 <tigg...@discussions.microsoft.com> wrote:
> We have an excel spreadsheet with constant data entered. *We are tryingto
> formulate a constant average of these points and having no luck. *Is there a
> way to set up a formula that will consistantly average points? *An example of
> what we are looking for would be averaging (A1:A180) and then the next
> average would be (A181:A361). *Can somebody help me figure out how to make
> this a consistant formula without having to constantly put in the next round
> of cells?
> Lori

thanks for all your help that works just the way we needed it to. I really
appreciated all your help.

"Luke M" wrote:

> I'm assuming you want an average of 180 cells all the time (your second array
> of 181:361 is actually 181 cells)
>
> =AVERAGE(INDIRECT("A"&(ROW(A1)-1)*180+1&":A"&ROW(A1)*180))
>
> As you copy this down, it will average 1:180, 181:360, 361:540, etc.
> --
> Best Regards,
>
> Luke M
> *Remember to click "yes" if this post helped you!*
>
>
> "tiggs96" wrote:
>
> > We have an excel spreadsheet with constant data entered. We are trying to
> > formulate a constant average of these points and having no luck. Is there a
> > way to set up a formula that will consistantly average points? An example of
> > what we are looking for would be averaging (A1:A180) and then the next
> > average would be (A181:A361). Can somebody help me figure out how to make
> > this a consistant formula without having to constantly put in the next round
> > of cells?
> > Lori

Hi,

Try this

=AVERAGE(OFFSET(A$1,(ROW(A1)-1)*180,,180))

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"tiggs96" wrote:

> We have an excel spreadsheet with constant data entered. We are trying to
> formulate a constant average of these points and having no luck. Is there a
> way to set up a formula that will consistantly average points? An example of
> what we are looking for would be averaging (A1:A180) and then the next
> average would be (A181:A361). Can somebody help me figure out how to make
> this a consistant formula without having to constantly put in the next round
> of cells?
> Lori

Very helpful - thanks for the help resolving my formula issues.
Lori

"Pete_UK" wrote:

> Suppose you use C1 to contain a number like 1, 2, 3 etc which is the
> set of data you want to average (each of them covering 180 cells).
> Then you could use this formula:
>
> =AVERAGE(INDIRECT("A"&((C1-1)*180+1)&":A"&(C1*180)))
>
> Then, with the following values of C1, your formula would evaluate as
> shown:
>
> C1 Formula range
> 1 A1:A180
> 2 A181:A360
> 3 A361:A540
> 4 A541:A720
> etc
>
> Hope this helps.
>
> Pete
>
> On Feb 23, 3:58 pm, tiggs96 <tigg...@discussions.microsoft.com> wrote:
> > We have an excel spreadsheet with constant data entered. We are trying to
> > formulate a constant average of these points and having no luck. Is there a
> > way to set up a formula that will consistantly average points? An example of
> > what we are looking for would be averaging (A1:A180) and then the next
> > average would be (A181:A361). Can somebody help me figure out how to make
> > this a consistant formula without having to constantly put in the next round
> > of cells?
> > Lori
>
>

Glenn wrote:
> tiggs96 wrote:
>> We have an excel spreadsheet with constant data entered. We are
>> trying to formulate a constant average of these points and having no
>> luck. Is there a way to set up a formula that will consistantly
>> average points? An example of what we are looking for would be
>> averaging (A1:A180) and then the next average would be (A181:A361).
>> Can somebody help me figure out how to make this a consistant formula
>> without having to constantly put in the next round of cells?
>> Lori
>
> Assuming you meant (A181:A360) for the second average, put this in B1
> and copy down as needed:
>
> =AVERAGE(INDIRECT("A"&(ROW()-1)*180+1&":A"&(ROW()-1)*180+180))

Obviously, the second half of that could be shortened...


=AVERAGE(INDIRECT("A"&(ROW()-1)*180+1&":A"&ROW()*180))

You're welcome, Lori - thanks for feeding back.

Pete

On Feb 23, 4:28*pm, tiggs96 <tigg...@discussions.microsoft.com> wrote:
> Very helpful - thanks for the help resolving my formula issues.
> Lori
>
>