Consistantly average 180 data cells

[tiggs96]

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]

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

 

[Luke M]

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.

 

[Sheeloo]

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

 

[Pete_UK]

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

 

[tiggs96]

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

 

[Shane Devenshire]

Hi,

Try this

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

 

[tiggs96]

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

 

[Glenn]

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

 

[Pete_UK]

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

Pete