Average of Averages

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

BarryC - 30 Dec 2005 20:22 GMT

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?

Reply to this Message

Duane Hookom - 30 Dec 2005 20:43 GMT

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

Signature

Duane Hookom
MS Access MVP
--

>I have a report that has line items that I am averaging such: Spoiled %
> = [Spoiled]/[Plates]*[PcsOut] where Plates and PcsOut are different
[quoted text clipped - 6 lines]
>
> What is the correct way to figure the summary averages?

Reply to this Message

BarryC - 30 Dec 2005 21:12 GMT

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.

Reply to this Message

BarryC - 30 Dec 2005 21:32 GMT

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

Reply to this Message

Duane Hookom - 30 Dec 2005 21:42 GMT

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%

Signature

Duane Hookom
MS Access MVP
--

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

Reply to this Message