Excel formula to calculate contribution

Question

To determine the top 100 Part Nos that contribute the most to the overall error, I must calculate the error contribution made by each Part No. I require a formula in Excel that I can enter into column F of the illustration below.

Sample

The image provided is to illustrate the scenario. My data set contains over 30,000 records that I need to pick the top 100 parts.

Answer 1

For several component numbers, you may obtain the same accuracy. So you might not be able to get an exact hundred. However, you may filter the top 100. use the formula below.

=RANK(accuracy,$E:$E,1)

The accuracy column is the basis for the rating above. However, if you wish to order according to the column for mistake contributions. Use the formula below in the F column and delete the abs function from the error formula.

=RANK(error,$D:$D,1)

Please note that these formulas are given in the same context that you used in your image. You need to replace the 'error' or 'accuracy' with the current row's cell detail (like 'D2' or 'E2' etc).