Consistantly average 180 data cells
 

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

Consistantly average 180 data cells
 

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

Consistantly average 180 data cells
 

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

Consistantly average 180 data cells
 

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

Consistantly average 180 data cells
 

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

Consistantly average 180 data cells
 

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

Consistantly average 180 data cells
 

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

Consistantly average 180 data cells
 

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

Consistantly average 180 data cells
 

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

Consistantly average 180 data cells
 

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