Custom calculation

[Guest]

How do I create a query or provide the result on a form that calculates the
average of a number of fields in the same record ignoring null values.

 

[Brendan Reynolds]

SELECT (NZ([TestNum1],0)+NZ([TestNum2],0)+NZ([TestNum3],0))/3 AS Average
FROM tblTest;

 

[John Spencer]

Brendan's solution is good if you have a specific number of fields and you
want to treat the null values as zero. Otherwise, you need to do something
a bit more complex to get the correct divisor

Field: MyAverage: (NZ([TestNum1],0)+NZ([TestNum2],0)+NZ([TestNum3],0))/ (
IIF(TestNum1 is Null,0,1) + IIF(TestNum2 is Null,0,1) + IIF(TestNum3 is
Null, 0,1))

I would use the custom function below saved in a module and called in the
query. Max of 39 fields allowed in a query.

Field: MyAverage: fGetMeanAverage(TestNum1, TestNum2, TestNum3)

'==== Code Follows =====
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 = GetMeanAverage("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

SELECT (NZ([TestNum1],0)+NZ([TestNum2],0)+NZ([TestNum3],0))/3 AS Average
FROM tblTest;

--
Brendan Reynolds
Access MVP

How do I create a query or provide the result on a form that calculates
the
average of a number of fields in the same record ignoring null values.

 

[Guest]

Thanks Brendan I know about the NZ function and it works.

Pagan

SELECT (NZ([TestNum1],0)+NZ([TestNum2],0)+NZ([TestNum3],0))/3 AS Average
FROM tblTest;

--
Brendan Reynolds
Access MVP

How do I create a query or provide the result on a form that calculates
the
average of a number of fields in the same record ignoring null values.

 

[Guest]

I meant to write I NOW know about the NZ function, thanks to you.

Thanks Brendan I know about the NZ function and it works.

Pagan

SELECT (NZ([TestNum1],0)+NZ([TestNum2],0)+NZ([TestNum3],0))/3 AS Average
FROM tblTest;

--
Brendan Reynolds
Access MVP

How do I create a query or provide the result on a form that calculates
the
average of a number of fields in the same record ignoring null values.

 

[Guest]

Hi John I entered "JohnsAverage:fgetMeanAverage([Race Time 1],[Race Time
2],[Race Time 3])" without the quotes in the Field area of a select quety. I
got the message: "undefined function fGetMeanAverage" Was this an option you
sugested is did I misinterpret you reply.
Pagen

Brendan's solution is good if you have a specific number of fields and you
want to treat the null values as zero. Otherwise, you need to do something
a bit more complex to get the correct divisor

Field: MyAverage: (NZ([TestNum1],0)+NZ([TestNum2],0)+NZ([TestNum3],0))/ (
IIF(TestNum1 is Null,0,1) + IIF(TestNum2 is Null,0,1) + IIF(TestNum3 is
Null, 0,1))

I would use the custom function below saved in a module and called in the
query. Max of 39 fields allowed in a query.

Field: MyAverage: fGetMeanAverage(TestNum1, TestNum2, TestNum3)

'==== Code Follows =====
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 = GetMeanAverage("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

SELECT (NZ([TestNum1],0)+NZ([TestNum2],0)+NZ([TestNum3],0))/3 AS Average
FROM tblTest;

--
Brendan Reynolds
Access MVP

How do I create a query or provide the result on a form that calculates
the
average of a number of fields in the same record ignoring null values.

 

[John Spencer]

Did you copy the function fGetMeanAverage to a vba module?

Did you save the module with a name OTHER than fGetMeanAverage? A module and a
routine (function or sub) cannot have the same name.

Also, check the code and make sure that the comment does not wrap to a second line.

If you see Red lines in the code module, a comment has probably wrapped onto a
second line.


For instance, each of the following comments should be on one line

'At least one number in the group of values

'Sample call: myAvg = GetMeanAverage("1","TEST","2", "3",4,5,6,0) returns 3 (21/7)

Hi John I entered "JohnsAverage:fgetMeanAverage([Race Time 1],[Race Time
2],[Race Time 3])" without the quotes in the Field area of a select quety. I
got the message: "undefined function fGetMeanAverage" Was this an option you
sugested is did I misinterpret you reply.
Pagen

Brendan's solution is good if you have a specific number of fields and you
want to treat the null values as zero. Otherwise, you need to do something
a bit more complex to get the correct divisor

Field: MyAverage: (NZ([TestNum1],0)+NZ([TestNum2],0)+NZ([TestNum3],0))/ (
IIF(TestNum1 is Null,0,1) + IIF(TestNum2 is Null,0,1) + IIF(TestNum3 is
Null, 0,1))

I would use the custom function below saved in a module and called in the
query. Max of 39 fields allowed in a query.

Field: MyAverage: fGetMeanAverage(TestNum1, TestNum2, TestNum3)

'==== Code Follows =====
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 = GetMeanAverage("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

SELECT (NZ([TestNum1],0)+NZ([TestNum2],0)+NZ([TestNum3],0))/3 AS Average
FROM tblTest;

--
Brendan Reynolds
Access MVP

How do I create a query or provide the result on a form that calculates
the
average of a number of fields in the same record ignoring null values.