Average % calculations

[Jay]

This query is as much about mathematical method as it is Excel.

A day-to-day task is to review sales data against expected values. So,
let's say I have 2 columns i.e.

Sold Price Expected Value
5000 4500
3000 2800
4500 3850
3850 4375

With a few thousand rows.

So, my question is - what's the best way of calculating the Average % of
Expected Value realised across ALL sales? Each of the sales above
individually works out as:

5000 4500 111.11%
3000 2800 107.14%
4500 3850 116.88%
3850 4375 88.00%

So, the two methods I can see of calculating the OVERALL Average % are:

1) Average the % data above-gives Avg% of Expected Values over ALL sales

2) SUM or AVERAGE both of the original columns & calculate the % i.e.

5000 4500
3000 2800
4500 3850
3850 4375
------------
16350 15525 105.31%

So, I have 2 questions:

A) Which method gives the most accurate Result.
B) Why can the Final Avg % differ by up to 10% between both methods?

Thanks for taking the time to read this, but we just can't figure out
why the 2 methods can give such varied results. (a little variance would
be expected)

Any help greatly appreciated.

Jay

 

[Harvey Waxman]

5000 4500 111.11%
3000 2800 107.14%
4500 3850 116.88%
3850 4375 88.00%

So, the two methods I can see of calculating the OVERALL Average % are:

1) Average the % data above-gives Avg% of Expected Values over ALL sales

This is an average of averages. A 25 - 25 - 100% comparison is just as
important as 5000-5000-100% in the overall average.

2) SUM or AVERAGE both of the original columns & calculate the % i.e.

5000 4500
3000 2800
4500 3850
3850 4375

In this case the 25-25 relationship would have much less influence on the
overall average than the 5000-5000 comparison.

I guess it depends on what you are trying to measure.

But I am no mathematician so take this with a skeptical eye.

 

[Mr F]

This query is as much about mathematical method as it is Excel.

A day-to-day task is to review sales data against expected values. So,
let's say I have 2 columns i.e.

Sold Price Expected Value
5000 4500
3000 2800
4500 3850
3850 4375

With a few thousand rows.

So, my question is - what's the best way of calculating the Average %
of Expected Value realised across ALL sales? Each of the sales above
individually works out as:

5000 4500 111.11%
3000 2800 107.14%
4500 3850 116.88%
3850 4375 88.00%

So, the two methods I can see of calculating the OVERALL Average % are:

1) Average the % data above-gives Avg% of Expected Values over ALL sales

2) SUM or AVERAGE both of the original columns & calculate the % i.e.

5000 4500
3000 2800
4500 3850
3850 4375
------------
16350 15525 105.31%

So, I have 2 questions:

A) Which method gives the most accurate Result.
B) Why can the Final Avg % differ by up to 10% between both methods?

Thanks for taking the time to read this, but we just can't figure out
why the 2 methods can give such varied results. (a little variance
would be expected)

Any help greatly appreciated.

Jay

As Harvey correctly pointed out, you need to be very careful when
averaging percentages, since each 100% represents a different quantity,
so they are not actually the same value. Statistically, averaging the
percentage data gives a meaningless figure - just because Excel can
work it out doesn't mean you should believe it! I tell my pupils at
school to use their calculators to work out 3 pencils + 7kg - 4 minutes
and they all tell me the answer is 6 !!

Your best bet will be to use all the original data values in your
calculation. I think you can do something with "Dynamic Named Ranges"
which will allow your calculation to be updated irrespective of the
length of your data list, but I'm still learning about that so can't
advise.
What I can offer is just a reassurance that actually all you need to do
is to sum each column.
(Sum(Sold)-Sum(Expected))/Sum(Expected)*100 as you did above.
If the numbers go ridiculously large in the sums then you can of course
simply divide everything by (e.g.) 10000 before summing - the answer's
the same. That's why averaging the values instead of summing them gives
the same result as well.
HTH,
Mr F