How should I deal with zero values in a list of values to be summed/averaged?

Question

I work in a warehouse where we take components and put them together to make a finished good product.

We have these values for each component:

1. Quantity Required

2. Quantity Used

3. Variance (Quantity Used- Quantity Required)

4. Overage ((Variance/Quantity Required)) * 100%

So basically these values tell me how much we needed, how much we used, and how much we wasted.

At the bottom of the report that shows all these values, I take the sum of Quantity Required, Quantity Used, and Variance. I also take the average of Quantity Required and Quantity Used and subtract these averages to find the average Variance.

The problem: Sometimes the Quantity Required value for a component is zero. How should I adjust my summations/averages accordingly?

What I've tried: I've simply made the summations/averages ignore the rows where the Quantity Required is zero, but I think this is wrong. If my boss asks me "What is the average overage for Item X for this year?" my method will ignore the rows with zeros in the Quantity Required column, thus ignoring an amount in Quantity Used that is technically still an amount to consider.

Sample Data of 1 item being used across multiple jobs:

                   Quantity Required    Quantity Used           Variance       Overage

Job A              1000                 1100                    100            10%
Job B              900                  800                    -100           -11.1%
Job C              0                    300                     300            (N/A)

    Summations:    1900                 1900                    0              0%
    Averages:      950                  950                     0              0%

Answer 1

You should definitely not ignore the zero rows.

The simplest thing to do would be just to sum the Quantity Required and the Quantity Used , then average by dividing by the number of rows in the report.

You can sum the Variance the same way. Summing the Overage makes no sense - mark it N/A.

You should not average the Variance and the Overage by using the row values Compute those directly from the Summations of the two columns.

Answer 2

It's a good rule of thumb to never add quantities that have different units. When you add, for example, the entries in the first column, the first entry has units "number of component A", whereas the second entry has units "number of component B". So what are the units of the sum?

A good way to aggregate this data is to compute the total cost of the goods used and the total cost of goods used under normal usage (i.e. if the quantities used in the "quantity required" column were used). Then you overage cost is

(cost of goods used) - (cost of goods that should have been used),

and your overage percentage is

((cost of goods used) - (cost of goods that should have been used)) / (cost of goods that should have been used).

You can do this analysis item-by-item as well.

Answer 3

As mentioned in other answers to this question, it depends on what the 0 value represents.

If a sample is missing, then there is no way to impute its values, so the zero sample can be excluded from mean and average calculations.

However, if you have a situation where you started with 10 items and used 8 of them, then a remainder of 0 indicates an error of some sort. In such a case, the mean and average is meaningless. You first need to account for the reason(s) for the discrepancy. Then you will likely have a different data set on which to compute the mean and average.