weird calculation issue

Hi,

Unfortunately I don't have MS Excel installed in my laptop so the below was created in openoffice. I wasn't sure if people who do not use openoffice would have been been able to open the file so I'm attaching a screenshot of if as it's quite simple really.

The attached screenshot illustrates a 2 year comparison for 2 products (Product 1 & Product 2), in terms of quantity, total cost and unit price (total cost / quantity). In row 5 I'm calculating the combined quantity, total cost and unit price for each each year.

Lastly, column H shows the variance in the unit price for product 1 & 2 and the combined too. It's the combined that confuses the hell out of me basically..Why would the combined unit price variance be -93 when the unit price variance for product 1 is -33 and for product 2 is +466? Shouldn't the combined unit price variance fall between these two numbers??

Any ideas??

Thanks everyone

Dimiliv

the unit prices should be between , and they are. as xlnitwit says there is no mathematical reason why it the variances should

From a mathematical point of view you're right, there isn't, the calculations are correct indeed..but I just can't get my head around how can you have one product the unit price of which was reduced by 33 pounds, and another product the unit price of which increased by 466 pounds and when you calculate the combined variance you get -93?! I would have expected this result if the second product's unit price variance had been reduced too...that (in my head) would have pushed the combined unit price variance even further under 0...in -93... does that make sense at all?

Originally Posted by xlnitwit

Not logically, no, I'm afraid, though it is a fairly common confusion when working with averages.

haha thanks..