Weighted Average for Mutiple Categories

[Marcie Be]

I have used the following formula with the data below. I am seeking confirmation that my results are correct. Could someone please check and comfirm

Formula

=SUMPRODUCT(D5:D10,E5:E10,F5:F10,G5:G10,H5:H10)/SUM(H5:H10)

Results

 

[CA_Punit]

i am a bit confused with your requirment ..Please elaborate

 

[Marcie Be]

i am a bit confused with your requirment ..Please elaborate

Thank you for responding. It's hard to explain.
I have six product types Alpha to Foxtrot.
Each made up of 5 parts A to E
Each product has a take rate highest runner 36% lowest 3% all six combined add up to 100%. The take rate is the weighting for each product.
I am trying to calculate the weighted average for all six products.
I hope this makes things clearer.
Best Regards
Marcie

 

[mamady]

Hi Marcie,

indeed, SUMPRODUCT is the relevant formula for weighted average.

However, to address your question, we need to understand the relationship between the 5 parts, are they mutually exclusive or aggregated. In other words, and for example, if we take Alpha, do you want to consider the average of A, B, C and D and then te multiply it against the Take Rate weight. Or, A is part of (A+B+C+D), so in this case A/SUM(A,B,C,D) and then we multiply it against the Take Rate weight.

Regards
m. yusuf

 

[Marcie Be]

Hi Marcie,

indeed, SUMPRODUCT is the relevant formula for weighted average.

However, to address your question, we need to understand the relationship between the 5 parts, are they mutually exclusive or aggregated. In other words, and for example, if we take Alpha, do you want to consider the average of A, B, C and D and then te multiply it against the Take Rate weight. Or, A is part of (A+B+C+D), so in this case A/SUM(A,B,C,D) and then we multiply it against the Take Rate weight.

Regards
m. yusuf

Hi Yusuf the take rates aply to Apha, Beta etc
Alpha, Beta etc are each comprised of there own A+B+C+D

EXAMPLE:
Alpha has a Take Rate of 8%. Alpha = A+B+C+D
The take rates combined = %100

I want to obtain the weighted Avg for each of the styles Alpha, Beta, Charlie, Delta, Echo, Foxtrot to get an overall weighted average.
I was looking to get a one number result that is the weieghted average of the lot. Can this be done with the data that I have?

Regards

Marcie

 

[Marcie Be]

For all the styles Alpha, Beta, Charlie, Delta, Echo, Foxtrot I have claculated the weighted average for A,B,C & D.
I am now at the point where I need to sum A+B+C+D for each style to get the weighted average of all the component parts and then x by the take rate. Is this correct?

 

[CA_Punit]

I was looking to get a one number result that is the weieghted average of the lot.

This is confusing . As per my understanding You cannot get a overall weightage. Overall weightage is alway one.