I have order quantities and % late for each. How do I calculate the main drivers?

[Katie620]

This may be more of a math question sorry :P

So say you have three columns (sorry for bad formatting):

Order Quantity % Late
A 500 62%
B 50 95%
C 300 25%
D 900 55%
E 150 82%


So with a sample like that, it's not easy to just say the largest order quantity or largest % late is the largest driver of total percent late % for all orders.

So is there a way to calculate something in a new column that gives a new ranking for each to order them from largest contributor to total late % to smallest?

Thanks! Let me know if that doesn't make sense!

[AliGW]

Please attach a sample workbook. In it, manually mock up a before and after showing some expected results.

[63falcondude]

If the #1 contributor is the order with the highest number (not percent) late, you can use this:

D2 (# Late) =B2*C2
Drag down column D.

E2 (Rank) =COUNTIF(D:D,">="&D2)
Drag down column E.

[63falcondude]

If you do not want to use a helper column, you can use this in D2:

=SUMPRODUCT(--(B$2:B$6*C$2:C$6>=B2*C2))