Averages are defying logic

[DubboPete]

Hi all,

I have a form with three fields, and three subtotals at the bottom.
First field is FTE (a total of full-time employees), VACANT, (number
of FTE positions not filled) and PERCENTAGE (calculated from FTE vs
VACANT).

I have two entries in the table, which show up on the report, as two
line entries. The percentages calculate correctly on the line level,
but do not average out properly on the report footer, where I am
trying to get the overall percentage.

Here's the math:

FTE VACANT PERCENT
253.465 33.500 13.217%
44.472 3.526 7.929%
________________________________
297.937 37.026 12.428%

The sums work this way, but if you take the logic that 13.217% +
7.929% = 21.146, then half of that should be the average = 10.573, and
not 12.428% !

Why two different figures? Not sure if this is an Access problem or a
math problem!

Any help appreciated!

Pete

 

[Rob Parker]

Hi DubboPete,

Your expectation of how the math should work is wrong - you cannot average a
set of averages to get the overall answer (see note below), you must
calculate it from the totals. In this case it is indeed 12.428.

HTH,

Rob

Note: for the special case where each data set contains the same total
number of items, you can average the averages. In all other cases, each
data set contributes a different amount to the final result, and you need
the weighted average; the simplest way to get it is to total the data set
totals and calculate it. You can also get it by summing the weighted
averages: in your case, it is
(13.217 * (253.465/297.937)) + (7.929 * (44.472/297.937))
which also gives 12.428

 

[-james]

Hey Pete,

it's your math. you're finding the average of the

sum of Vacant
---------------- = 12.428
sum of FTE

not the average of (13.217 + 7.929) / 2 = 10.573

Don't be too hard on yourself, I've done this one too ;o)

-james

 

[DubboPete]

Hi DubboPete,

Your expectation of how the math should work is wrong - you cannot average a
set of averages to get the overall answer (see note below), you must
calculate it from the totals.  In this case it is indeed 12.428.

HTH,

Rob

Note: for the special case where each data set contains the same total
number of items, you can average the averages.  In all other cases, each
data set contributes a different amount to the final result, and you need
the weighted average; the simplest way to get it is to total the data set
totals and calculate it.  You can also get it by summing the weighted
averages: in your case, it is
(13.217 * (253.465/297.937)) + (7.929 * (44.472/297.937))
which also gives 12.428












- Show quoted text -

Hi Rob and James

Thankyou both for your answers! Never did that level of mathematics
at my school/workhouse!

Puts my mind at rest, and I now have an answer for any inquisitive
managers!

Pete