Hi!
Why AVERAGE() of subtotals of a data array does not equal to AVERAGE() of
all data in the array?

Subtotal IGNORE other subtotals OR you may have text (number formated as
text) on it. otherwize you can use =SUBTOTAL(9,....)/SUBTOTAL(3,....) .


"Elena" wrote:

Hi!
Why AVERAGE() of subtotals of a data array does not equal to AVERAGE() of
all data in the array?

May be I have worded it the wrong way.
It is not function SUBTOTAL(), but just groups of data of this array.

Like this:

Orders Contracts
11 a
22 a
1 b
2 b
4 b
5 b

Average of contract a 16,5, average of b - 3.
Average of a and b - 9,75.

Average of all orders - 7,5

Why there is a difference and what does it mean?

Hello, Elena!
You wrote on Wed, 24 Jan 2007 05:37:03 -0800:

E Like this:

E Orders Contracts
E 11 a
E 22 a
E 1 b
E 2 b
E 4 b
E 5 b

E Average of contract a 16,5, average of b - 3.
E Average of a and b - 9,75.

E Average of all orders - 7,5

We had this question recently and the average of group averages
is only equal to the overall average if the groups contain the
same number of values (and don't overlap). If you write out the
actual arithmetic of your example, you will see that the average
of the a group is given twice the weight of the other when an
average of averages is calculated.

James Silverton
Potomac, Maryland

E-mail, with obvious alterations:
not.jim.silverton.at.comcast.not

Thank you James.

Now it is a little bit more clear to me.

But what is the right number in this situation?

Should I apply weights in my calculation? How does it work?

Hello, Elena!
You wrote on Wed, 24 Jan 2007 07:11:00 -0800:

E Now it is a little bit more clear to me.

E But what is the right number in this situation?

I would say that you should still calculate your group averages
if you want; they have their uses. However, calculate the
overall average by selecting *all* the data in all the groups.
AVERAGE() is very fast!

James Silverton
Potomac, Maryland

E-mail, with obvious alterations:
not.jim.silverton.at.comcast.not

Hello James!

it is not a question of velocity :)))
The situation is more complicated.
We have to calculate percentage of fullfillment of each contract which in
tuern consists of different number of deliveries which also have different
percentage of fullfillment.
It looks like that:

Deliveries Contract
36% a
24% a
50% b
109% b
88% b
22% b
15% c
60% c

Based on this data we have to calculate an average percentage of
fullfillment for each contract. 109% means we have been better than contract
terms.
It has to be a chart grouped by contracts with indication of total average
so it is not clear whether we shall use an average throughout all range of
data or an average of averages for contracts - these figures are very
different.

Elena