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

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 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 wrote:
Very helpful - thanks for the help resolving my formula issues.
Lori