How to set up Formula in Excel to calculate the MU%

Question

How to set up Formula in Excel to calculate the MU%

Gladys Chan 0

Hi All,

I have a list of style no. need to run the Average MU% based on their total qty and sample type per attached file.

There are 4 styles under Sample Type A & B. For duplicated 4 styles, I need to run the Average MU% in either under all styles under sample type A and sample type A for single style while sample type B for duplicated 4 styles. style should not be duplicated). I have no idea to set up the formula to run result out due to:-

Total qty is not correct and the qty duplicated for 4 styles under sample type A & B. The total qty should be 219,000 pcs but the sum qty is 231,000 pcs.
4 styles go either sample Type A or Type B.

Please help how to set the excel formula to run the average MU% out.

Thanks for all your great help in advance!!

Ashish Mathur 102.6K Reputation points Volunteer Moderator

2026-10-16T23:03:44.5866667+00:00

Hi,

Please share some data to work with and show the expected result. Share data in a format that can be pasted in an MS Excel file.

1 answer

Your answer

Ashish Mathur 102.6K Reputation points Volunteer Moderator

2026-10-16T23:03:44.5866667+00:00

Hi,

Please share some data to work with and show the expected result. Share data in a format that can be pasted in an MS Excel file.

Answer 1

Hello Gladys Chan
The total is overstated because four styles appear under both Sample Type A and B, so their quantities are counted twice.

Assuming duplicated styles should use Type B, please add two helper columns:
Selected Type:

=IF(COUNTIFS($A:$A,A2,$B:$B,"B")>0,"B","A")

Valid Qty:


=IF(AND(B2=E2,COUNTIFS($A$2:A2,A2,$B$2:B2,B2)=1),C2,0)

Then calculate the quantity-weighted Average MU%:

=IFERROR(SUMPRODUCT(F:F,D:D)/SUM(F:F),0)

Assuming that: Columns A–D contain Style, Sample Type, Qty and MU%. Columns E–F are the new Selected Type and Valid Qty columns.

The valid quantity should be 219,000 pcs, not 231,000 pcs. Assuming whether Type B should always be selected when a style appears under both A and B.
Please let me know if you have any further questions.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.