subtotaling

[jwa0]

Start with a list of 100 numbers in column A. Display the average of the first five numbers in B5, the average of the next 5 numbers in B10, etc.

John Adams

 

[daleyman]

is this a question?

if so, then a simple formula in B5 as follows...

=AVERAGE(A1:A5)

...will give you the value you want, and then you can copy and paste this cell every 5 cells. If you want a formula that you can fill down the whole list to give the relevant average every 5 cells, use the following in cell B5 and fill down from there...

=IF(ROUND((CELL("row",A5)/5),0)=(CELL("row",A5)/5),AVERAGE(A1:A5),"")

 

[Mark W.]

Or, simply enter...

=IF(MOD(ROW(),5),"",AVERAGE(OFFSET(A1,-4,,5)))

...into B1 and copy down.
This message was edited by Mark W. on 2002-04-19 13:36

 

[Yogi Anand]

On 2002-04-19 13:30, daleyman wrote:
is this a question?

if so, then a simple formula in B5 as follows...

=AVERAGE(A1:A5)

...will give you the value you want, and then you can copy and paste this cell every 5 cells. If you want a formula that you can fill down the whole list to give the relevant average every 5 cells, use the following in cell B5 and fill down from there...

=IF(ROUND((CELL("row",A5)/5),0)=(CELL("row",A5)/5),AVERAGE(A1:A5),"")

Hi daleyman:

Why not use your earlier simpler formula
=AVERAGE(A1:A5)

highlite cells A1:A5 and then drag down ... just curious about the merits of using the other long formula you suggested.

Regards!

Yogi Anand

 

[Mark W.]

Hi daleyman:

Why not use your earlier simpler formula
=AVERAGE(A1:A5)

highlite cells A1:A5 and then drag down ... just curious about the merits of using the other long formula you suggested.

Regards!

Yogi Anand

I for one prefer to have a uniform formula in a given column. That way after row insertions/deletions one can easily fill down the formula from the top row without having to worry about which rows should contain the formula. Also, it makes it easier to use the Edit | Go To... Special Column Differences command to detect inconsistencies among the formulas of a given column.

Of course, I also prefer my own formulation...

=IF(MOD(ROW(),5),"",AVERAGE(OFFSET(A1,-4,,5)))

...but no surprise there!
This message was edited by Mark W. on 2002-04-19 13:52

 

[Yogi Anand]

I for one prefer to have a uniform formula in a given column. That way after row insertions/deletions one can easily fill down the formula from the top row without having to worry about which rows should contain the formula. Also, it makes it easier to use the Edit | Go To... Special Column Differences command to detect inconsistencies among the formulas of a given column.

Good point Mark! ... makes for a more robust setup. It is a pleasure to get your insightful comments.

Regards!

Yogi Anand

 

[daleyman]

yep, i bow at your feet! Got the ball rollin' though huh?

Yogi, was just doin it logically with my existing skills, not with Marks expertise (his MOD solution is very nice).

_________________<table style="background-color:#0e54be" cellspacing="1" cellpadding="2"><td style="background-color:#ceffff;font-family:arial;color:#072c63;font-size:8pt;">   DALEY     </td></table>
This message was edited by daleyman on 2002-04-19 15:38