Why is the average in my report footer giving the wrong calculation?

[Nancy via AccessMonster.com]

I have a report that totals up the daily efficiency for each employee. Then,
in the report footer I made a text box that will average those daily
efficiencies. However, when I manually type these numbers into a calculator I
do not get the right average. For example on employee has the following daily
efficiencies: 62.23, 131.37, 129.29, 126.53, 126.53, and 141.42. When I
manually average those I get 119.56. However, my report average shows 128.55.
I figured out that I have to include the sum inside the formula in my text
box, but when I do that I keep getting error messages. This is the formula
that I am using:

=Avg(((([Sum Qty]*[Rate])*(0.125))/[Sum Hrs])/[Employee Data_Rate])

When I run the report I get a box that tells me to enter the parameter value
for Sum Qty and Sum Hrs. These two are calculated text boxes that gets the
sum of the Qty and the Hrs. I realize that you can't use these with the Avg
function but I don't know any other way to do it. I tried putting the Sum
function inside the above formula but got an error message as well. What
would be the best way to to get my report footer to show the proper average
of these numbers?

 

[KARL DEWEY]

When I run the report I get a box that tells me to enter the parameter
value for Sum Qty and Sum Hrs.
You have to use the orignal calculation and not the control name in the
report.
Something like this if QTY and Hrs are the field names from the query or SQL
source.
=Avg((((Sum(Qty)*[Rate])*(0.125))/Sum(Hrs))/[Employee Data_Rate])

 

[Nancy via AccessMonster.com]

Karl,

Thank you for your help. I tried the formula that you gave me but I received
the following message: Cannot have aggregate function in expression. Is there
another way to create this formula?

value for Sum Qty and Sum Hrs.
You have to use the orignal calculation and not the control name in the
report.
Something like this if QTY and Hrs are the field names from the query or SQL
source.
=Avg((((Sum(Qty)*[Rate])*(0.125))/Sum(Hrs))/[Employee Data_Rate])

I have a report that totals up the daily efficiency for each employee. Then,
in the report footer I made a text box that will average those daily

[quoted text clipped - 15 lines]

would be the best way to to get my report footer to show the proper average
of these numbers?

 

[Duane Hookom]

Do you understand the difference between averaging percents versus a weighted
average? For instance dividing the 2nd column below by the first derives a
percent in the 3rd column (3/12=0.25)

12 3 0.25
8 4 0.5
10 2 0.2
16 2 0.125

Averaging the 3rd column results in 0.26875 while dividing the sum of the
2nd column by the sum of the 1st column (weighted aversage) results in
0.239130435.

If the first column is always the same number, the two averages will be
equal. In my statistical calculations, the weighted averages are always what
my customers need. They should rarely if ever use an average of a percent.