Excel formula to calculate contribution

0 votes

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 by Kithuzzz
• 38,010 points
886 views

1 answer to this question.

0 votes

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

answered Oct 17, 2022 by narikkadan
• 63,700 points

Related Questions In Others

0 votes
1 answer

Excel formula to calculate MIN in table filtered

Try this: =SUBTOTAL(105;B2:B7) READ MORE

answered Jan 30, 2023 in Others by narikkadan
• 63,700 points
363 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
849 views
0 votes
1 answer

Excel formula to check date within this week and last week

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

answered Sep 29, 2022 in Others by narikkadan
• 63,700 points
1,546 views
0 votes
1 answer

How Can I Round Prices to the nearest 0.95 with an Excel Formula?

Try this: =IF(OR(A3-FLOOR(A3,1)>0.95,A3=CEILING(A3,1)),CEILING ...READ MORE

answered Oct 9, 2022 in Others by narikkadan
• 63,700 points
533 views
0 votes
1 answer

Excel COUNTIF formula

Please see MS Excel: COUNTIF Function (WS) You should ...READ MORE

answered Sep 29, 2022 in Others by narikkadan
• 63,700 points
638 views
0 votes
1 answer

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

answered Sep 30, 2022 in Others by narikkadan
• 63,700 points
511 views
0 votes
1 answer

Multiplying cells and sum then for a range

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

answered Oct 2, 2022 in Others by narikkadan
• 63,700 points
730 views
0 votes
1 answer

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

answered Oct 8, 2022 in Others by narikkadan
• 63,700 points
710 views
0 votes
1 answer
0 votes
1 answer

How to convert an excel formula to SQL to calculate daily compound interest

Here is a solution that is effective. ...READ MORE

answered Nov 26, 2022 in Others by narikkadan
• 63,700 points
666 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP