Average multiple column fields in access

[Guest]

I have several fields in a query that I need to average. Some of the values
are null so I can't calculate the average manually (Field1 + Field2)/2 won't
work. This is very easy to do in excel but I can't find the answer in
access. Please help.

 

[Guest]

Hi,

if fields contain null values you can set them to another default value by
using nz()

in your example it would become (nz(field1,0) + nz(field2,0))/2

- Raoul

 

[Guest]

In my case, I am calculating the market prices of a particular vegetable

Grower Description wk09 L 02/28/05 wk09 H 02/28/05 wk09 L 03/02/05
Asparagus Green $12.00 $14.00

I won't necessarily have a value for each date. In the above example, I
have an average of $13.00. If I used the formula, I would have an average of
$8.67, and that's not correct. In my database, I have six fields per week
that I need to consolidate into one average for reporting purposes. Any help
is appreciated. Thanks.

 

[Guest]

Hi,

You could do what you are trying to do by using iif() functions to determine
the number of fields greater than 0-(and thus the number that you want to
divide by), but I recommend that you look at trying to better normalize the
data for future flexibility.

Rather than having mulitple columns in your main table for prices at certain
dates, it would be much better if you created a related table with a few
fields to store the price info.

For example, you could have a table of Growers, with each one identified by
GrowerID. You could also have a table of products, with each identified by
ProductID. Then, you create a third table to store the GrowerID, the
ProductID, the date and the price.

That way, you can enter an unlimited number of dates and prices for each
grower/product combination. And, calculating things such as min price, max
price, avg price, are much easier - all you have to do is create a query that
groups by Grower and Product then add additional fields to return max price,
min price and average price. There is no need to worry about nulls, because
they wouldn't exist, and there would be no need to adjust the query as
additional dates get added (except maybe to just tweak date criteria if you
wanted to limit the stats to a date range, which would be easy to do).

Hope that helps.

-Ted Allen

 

[Guest]

I totally agree on this.

If you want to solve it for your current situation then you will have to
write a function

public function AverageAmount(arg1 as variant,arg2 as variant,arg3 as
variant,....) as double
dim iCount as integer
dim dblTotal as Double

args1=nz(arg1,0)
args2=nz(arg2,0)
args3=nz(arg3,0)

 

[John Spencer (MVP)]

You've been given good advice about normalizing the data. If for some reason
you can't then you will need a vba function to do this effectively.

You can use the VBA function below. You can call it in a query in a calculated column.

Field: AvgPrice: fGetMeanAverage(Field1, Field2, Field3, ...)

Public Function fGetMeanAverage(ParamArray Values())
'John Spencer UMBC CHPDM
'Last Update: April 5, 2000
'Calculates the arithmetic average (mean) of a group of values passed to it.
'Sample call: myAvg = fGetMeanAverage("1","TEST","2", "3",4,5,6,0) returns 3 (21/7)
'Ignores values that cannot be treated as numbers.

Dim i As Integer, intElementCount As Integer, dblSum As Double
intElementCount = 0
dblSum = 0

For i = LBound(Values) To UBound(Values)
If IsNumeric(Values(i)) Then 'Ignore Non-numeric values
dblSum = dblSum + Values(i)
intElementCount = intElementCount + 1
End If
Next i

If intElementCount > 0 Then
fGetMeanAverage = dblSum / intElementCount 'At least one number in the
group of values
Else
fGetMeanAverage = Null 'No number in the group of values
End If

End Function