AVERAGE

  • Thread starter Thread starter SteveL
  • Start date Start date
S

SteveL

Someone helped me with something like this a week ago and
I appreciate it but have to start over due my lack of
explaining the situation well.

I have one record with say values of 2, 5, 8, 0, & 0 in
five different fields. (Note that the last two are
zeros).

I know the average of the values (zeroes not counted) is
5.

How can I calculate that average in an additional
calculated field in a query that is already getting the
five values?

--Steve
 
Steve,

[field1]+[field2]+[field3]+[field4]+[field5]/(IIf([field1]<>0,1,0)+IIf([fiel
d2]<>0,1,0)+IIf([field3]<>0,1,0)+IIf([field4]<>0,1,0)+IIf([field5]<>0,1,0))

HTH,
Nikos
 
Hi,


A database assumes, the developers that built the database tools assume that
you will work VERTICALLY, not horizontally. Let us do it.

SELECT pk, f1 As MyField FROM tableName
UNION ALL
SELECT pk, f2 FROM tableName
UNION ALL
SELECT pk, f3 FROM tableName
UNION ALL
SELECT pk, f4 FROM tableName
UNION ALL
SELECT pk, f5 FROM tableName



save it as Qu1.


Next, with that vertical representation:

SELECT pk, AVG(myField)
FROM Qu1
WHERE myFIeld <> 0
GROUP BY pk



where I assumed that pk is the primary key of the record.


Hoping it may help,
Vanderghast, Access MVP
 
The VBA approach.

In a General Module, put:

Function NonZeroMean (varA, varB, varC, varD, varE) As Variant

' as usual, you can Dim everything more precisely, if you know what
' data types the variables will contain

Dim varSum as Variant
Dim varCt as Variant

varSum = varA + varB + varC + varD + varE

varCt = - (varA<>0) - (varB<>0) - (varC<>0) - (varD<>0) - (varE<>0)

' This is slightly tricky but quite a bit faster than a mess of If
' Statements. In a numerical context, the result of a logical test is
' either -1 (True) or 0 (False), so you are counting the arguments
' that are not equal to one

NonZeroMean = varSum / varCt

End Function

Define the calculated field in your Query grid as:

NonZeroMean (fldA, fldB, fldC, fldD, fldE)

You're done!


Someone helped me with something like this a week ago and
I appreciate it but have to start over due my lack of
explaining the situation well.

I have one record with say values of 2, 5, 8, 0, & 0 in
five different fields. (Note that the last two are
zeros).

I know the average of the values (zeroes not counted) is
5.

How can I calculate that average in an additional
calculated field in a query that is already getting the
five values?

--Steve
Click to expand...


Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher
 
In the comments towards the end of the function, "...that are not
equal to one." should read "...that are not equal to zero".

The VBA approach.

In a General Module, put:

Function NonZeroMean (varA, varB, varC, varD, varE) As Variant

' as usual, you can Dim everything more precisely, if you know what
' data types the variables will contain

Dim varSum as Variant
Dim varCt as Variant

varSum = varA + varB + varC + varD + varE

varCt = - (varA<>0) - (varB<>0) - (varC<>0) - (varD<>0) - (varE<>0)

' This is slightly tricky but quite a bit faster than a mess of If
' Statements. In a numerical context, the result of a logical test is
' either -1 (True) or 0 (False), so you are counting the arguments
' that are not equal to one

NonZeroMean = varSum / varCt

End Function

Define the calculated field in your Query grid as:

NonZeroMean (fldA, fldB, fldC, fldD, fldE)

You're done!





Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher
Click to expand...


Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher
 
Back
Top