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.

Oct 16, 2022 in Others 886 views

## 1 answer to this question.

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).

• 63,700 points

## Excel formula to check date within this week and last week

Assuming the first date in A2 try this formula ...READ MORE

## Is there a maximum number of formula fields allowed in Excel (2010)

See http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP010073849.aspx for limits on specs it doesn't indicate ...READ MORE

## Multiplying cells and sum then for a range

Use SUM() as an Array formula:  =SUM(IFERROR((AB22:AB1100 = ...READ MORE

## INDEX formula in Excel, Top 10, repeats previous value

Try this formula in cell W4: =IF(V3=V4,INDEX(INDIRECT("I"&MATCH(W3,I:I,0)+1&":I26"),MATCH(V4,INDIRECT("R"&MATCH(W3,I:I,0)+1&":R26"),0)),INDEX(\$I\$2:\$I\$26,MATCH(V4,\$R\$2:\$R\$26,0))) The calculation ...READ MORE