Average function - mathematical exercise

Hi All,

I have a mathematical challenge which I hope you smart people can explain to me.

In attached excel file I have products A-D with sales quantity, revenue and the average revenue per sale. I have actuals and targets thereby I can calculate an index.

My challenge is the yellow cell. My average index is less than index 102% in all 4 product categories, but my total is index 104%. How come this can be higher?
As 72% of my quantity is in product A then I whould expect my overall average to be close to this level.

And is it my formulas that are incorrect?

My head is spinning here. I hope some can help.

thanks
Alexander

that function will do the. Trick. Thanks!

But mathematically I still don't understand it. I take a total average on actual and target and divide one with the other. The averages are a product of revenue divided by quantity. Isn't this already kinda a weight average of the sub-product mix? Shouldn't this work?

It's been years since i had my math classes

yes and know. Your initial formula gave me a total average per sale index vs. target across all product groups - weighted.

However, I have now come across a scenario B. Here my product D have zero in sales and with your formula the average revenue per sale, overall is 74%. As D is minor then it should have very little impact on my overall performance.

I think the solutions I have received will do. Thanks a lot!

However, mathematically I still don't understand why it doesn't work.