1

I have a spreadsheet that I am working on that should average out some data into percentages.

I am trying to figure out what formula I should use to calculate this data.

I have two pivot tables, one that generates the quantity of defective parts, and one that generates the total quantity of parts received.

For example:

Lets say that I have 4 part numbers. p/n-1, p/n-2, p/n-3 and p/n-4.

1,2, and 3 are from the same family, while 4 is from a different family.

Column A represents the part number.

Column B represents the defective quantity.

Column D represents the part number again.

Column E represents the total quantity received.

Column G represents the family (Fam-1 = P/N 1,2,3).

Column H represents the percentage of defective parts from that family: (B1+B2+B3)/(E1+E2+E3)

.  A         B     D         E     G       H
1  p/n-1     5     p/n-1     7     Fam-1   0.28
2  p/n-2     2     p/n-2     2     Fam-2   0.25
3  p/n-3     8     p/n-3     12
4  p/n-4     1     p/n-4     4

I have 189 part numbers in total which I need to break down to their 28 respective families and calculate the defect percentage.

Currently I can do this manually by adding the quantities in each cell of the defective part number in relation to a family and dividing it by the total received, however this takes quite a bit of time.

What I would like to do is use something like this:

Lookup the specific part numbers in column A and add the quantity of each from column B.

Lookup those same numbers in column D and add the quantity of each one from column E.

Divide the quantity generated from column B into the quantity generated from column E to get the defective percentage.

As requested, here is an image of what I am trying to do.

https://i.imgur.com/x8d3Wt3.png

  • Better you share the part of sheet as Screen shot along with expected results with us, will help us to find a better formula. – Rajesh S Sep 16 '18 at 7:35
  • I think you should apply this formula to get the Rejection Rate,, (Rejected/Total produced)x100. Where Total produce= (Rejected+Accepted). ☺ – Rajesh S Sep 16 '18 at 8:02
  • I am using cell formatting which automatically appends the "x100" to give me the rejection rate that i need. – Morgan Sep 16 '18 at 16:30
  • I also Added an image of what it looks like in Excel. – Morgan Sep 16 '18 at 16:57
0

Update

I kept tinkering around and also doing some research when I found a solution that worked for me.

I can use "SUMIF" to lookup and calculate the percentages that I need.

=(SUMIF(A1:A4,"p/n-1",B1:B4)
+SUMIF(A1:A4,"p/n-2",B1:B4)
+SUMIF(A1:A4,"p/n-3",B1:B4)
/
(SUMIF(D1:D4,"p/n-1",E1:E4)
+SUMIF(D1:D4,"p/n-2",E1:E4)
+SUMIF(D1:D4,"p/n-3",E1:E4))

If anyone also knows a different way to go about this, please share as I would like to learn more about how to work efficiently in Excel.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.