Mac Forum / Applications / Excel / July 2006

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