Weighted percentage discrepancy.xls
Hi,
I have attached a file, to show the issue.
Does the discrepancy result because of rounding figures?
If somebody can give me some insight that would be great.
Thank you,
JAJ
Weighted percentage discrepancy.xls
Hi,
I have attached a file, to show the issue.
Does the discrepancy result because of rounding figures?
If somebody can give me some insight that would be great.
Thank you,
JAJ
in the example at the bottom, there is an (almost) constant relationship between value1 and value2. value2 is 63% of value1 (with tiny differences that i assume are due to rounding). Since you are just scaling all the weights down by 62%, this results in the same weighted average.
In your case at the top, the "value2" is not always a fixed percentage of "value1", so the same thing doesn't happen.
The way you set up your spreadsheet is not ideal - in column F I would rather make a reference to the original ratio (B/A) which I did in the spreadsheet attached, and the both ratios are exactly the same.
There must have been some minor error in copying/formatting, otherwise, the two methods would have given the same result (exactly equal ratios), because Excel calculates numbers with higher accuracy than it shows in the spreadsheet - ie unless you insert 'rounding' formula (which you didn't seem to do) the calculation will be accurate to the highest order.
Copy of Weighted percentage discrepancy.xls