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.

`(Rejected/Total produced)x100`

. Where`Total produce= (Rejected+Accepted)`

. ☺ – Rajesh S Sep 16 '18 at 8:02