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.
Lets say that I have 4 part numbers.
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:
. 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.