Average of Averages

[BarryC]

I have a report that has line items that I am averaging such: Spoiled %
= [Spoiled]/[Plates]*[PcsOut] where Plates and PcsOut are different
from row to row.

When I go to the summary, I have:
Sum([Spoiled])/(Sum([Plates])*Sum([PcsOut]))
This is giving me bad numbers. I understand that to just have
AVG(Spoiled%) would give wrong numbers also.

What is the correct way to figure the summary averages?

 

[Duane Hookom]

You didn't say what's wrong or provide sample records with the correct
results.
I would try:
=Sum(Spoiled)/ (Sum(Plates*PcsOut)

 

[BarryC]

Here's an example:
Row 1: Plates = 278, PcsOut = 100 Spoiled = 21680, Spoiled % = 78%

Row 2: Plates = 5387, PcsOut = 16 Spoiled = 6992, Spoiled % = 8.1%

Summary by my method yielded an average Spoiled of 4.4%
That just didn't look right.

I tried your method and it gives significantly different results.

 

[BarryC]

Since these are weighted averages, I'm not real sure what a correct
answer looks like.

 

[Duane Hookom]

If you don't know what the correct answer looks like then how do you know
any value is correct or not?

I would expect that summing Spoiled and dividing by the Sum of (plates *
PcsOut) would be correct. For the example you provided, the percent would be
25.1526%