Averaging in a form

[Tabco]

When averaging a total in a form the final result is
always off by a few hundred. What would be the correct
formula to average a total sum?
I use =Avg([data])

 

[Jeff Boyce]

You did not describe the underlying data on which you are trying to
calculate an average. When you say "averaging a total in a form", what do
you mean? We aren't there, so we can't see the form you are referring to.

What do you mean by "average a total sum"? Averaging is usually performed
on a range of values, rather than on a sum or total.

Given the formula you provided, where does your "[data]" come from?

 

[Yanick]

As long as [data] contain all the values this is the right way to do it.
According to Access help, here is what the function does:

Avg(expr)

The expr placeholder represents a string expression identifying the field
that contains the numeric data you want to average or an expression that
performs a calculation using the data in that field. Operands in expr can
include the name of a table field, a constant, or a function (which can be
either intrinsic or user-defined but not one of the other SQL aggregate
functions).

If it still not working please post more detail.

Yanick

 

[Jeff Boyce]

The implication is that there are multiple rows of [data] to average. I
didn't have that impression from the original post...

Jeff Boyce
<Access MVP>

 

[Tabco]

The data comes from a table. The form shows a product we
produce. Each product has a budgeted units per hour which
is different for different products. If we run two or more
products on a line I want to average the budgeted units
per hour for that line. Say I run three products- The
budgeted units per hour is 13500, 11000, and 24300. This
comes from the product table. The average should be 16267.
In the report I put a text box in that says
=Avg([Budgetunitsperhr]) The value it gives me is 15575.
I can't seem to get the box to give me the correct value.
Does this help?

 

[Jeff Boyce]

I'm still somewhat confused. If your form displays a single product, and
its budgeted-units-per-hour, how does Access know which three products (your
example) to include in the calculation of average?

By the way, from a statistical standpoint, averaging "units per hour" is
likely to give you incorrect results. Consider the old high school math
problem of "average speed", given traveling at 30 miles per hour for 45
minutes, then 45 miles per hour for 2 hours, then 15 miles per hour for 10
minutes. Unless you have the same time period for each, you can't average
the miles per hour.

 

[Jeff Boyce]

Small clarification -- you CAN average any set of numbers ... whether you
get garbage or not is a different matter!