Stuck on creating averages in a text box

[Guest]

Ok,

I have these five fileds in my detail section:
[mth1a] [mth2a] [Mth3a] [Mth4a] [Mth5a]

The fileds represent monthly utilization percentage. What I'm trying to do
is an averaging feild of to the right to average those five months as well as
an average in the group footer to average all the mth1a records in the group.

The control source for [mth1a] is as follows:
=IIf(IsNull([Mth1])=True,0,[Mth1])
The other feilds are all similar

These reason I did this was becasue I couldn't get an Avg function to work
if one of detail fields came up Null. However, I've now realized that when
it comes up zero and is included in the average, that's wrong as well. I
want a null value to be ignored in both Average calcs.

Thanks i advance.

 

[Duane Hookom]

Averaging or summing or minning or maxing across fields rather than across
records usually suggests an un-normalized table structure.

However you might try get your average with:
=(Nz(Mth1,0) +Nz(Mth2,0) +Nz(Mth3,0) +Nz(Mth4,0) +Nz(Mth5,0) ) / (5+
IsNull(Mth1) + IsNull(Mth2) + IsNull(Mth3) + IsNull(Mth4) + IsNull(Mth5) )

I didn't test this. If it works, you should be able to average by summing
the numerator (left of / ) and dividing by the sum of the denominator (right
of / ). YUK!!!

 

[Marshall Barton]

I have these five fileds in my detail section:
[mth1a] [mth2a] [Mth3a] [Mth4a] [Mth5a]

The fileds represent monthly utilization percentage. What I'm trying to do
is an averaging feild of to the right to average those five months as well as
an average in the group footer to average all the mth1a records in the group.

The control source for [mth1a] is as follows:
=IIf(IsNull([Mth1])=True,0,[Mth1])
The other feilds are all similar

These reason I did this was becasue I couldn't get an Avg function to work
if one of detail fields came up Null. However, I've now realized that when
it comes up zero and is included in the average, that's wrong as well. I
want a null value to be ignored in both Average calcs.

All of the aggregate functions (Count, Sum, Avg, etc) ignore
nulls. Since they only operate on a column in the record
source table/query, your text box expressions will not
affect their result.

With that understood, the report header/footer and/or any
group header/footer can easily calculate the average of a
record source column.

To calculate the average across the columns is messy because
you are not supposed to have related columns in a table. I
think this kind of expression might work:

=(Nz(Mth1,0) + Nz(Mth2,0) + Nz(Mth3,0) + Nz(Mth4,0) +
Nz(Mth5,0)) / (IIf(Mth1 Is Null, 0, 1) + IIf(Mth2 Is Null,
0, 1) + IIf(Mth3 Is Null, 0, 1) + IIf(Mth4 Is Null, 0, 1) +
IIf(Mth5 Is Null, 0, 1))

 

[John Spencer]

You could use a custom function in the query for the report. I wrote this
one quite a while back when I had a similar problem with data I was
importing.

Copy the code below and paste it into a vba module. Save the module with a
name other than fGetMeanAverage.

In your query, add a calculated column which should probably look like:

Field: TheAvg: fGetMeanAverage( [mth1] ,[mth2] ,[Mth3], [Mth4] ,[Mth5a])


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

 

[Guest]

Hey John,

I've been using you function here and it works great with one issue... it
doesn't seem to work in group footers. I've tried
=fGetMeanAverage([mth1],[mth2],[Mth3],[Mth4],[Mth5a])
and
=fGetMeanAverage([Avg Of Mth1],[Avg Of Mth2],[Avg Of Mth3]...) using the
named text feilds used to creat the columar averages. ANy ideas?

You could use a custom function in the query for the report. I wrote this
one quite a while back when I had a similar problem with data I was
importing.

Copy the code below and paste it into a vba module. Save the module with a
name other than fGetMeanAverage.

In your query, add a calculated column which should probably look like:

Field: TheAvg: fGetMeanAverage( [mth1] ,[mth2] ,[Mth3], [Mth4] ,[Mth5a])


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

Ok,

I have these five fileds in my detail section:
[mth1a] [mth2a] [Mth3a] [Mth4a] [Mth5a]

The fileds represent monthly utilization percentage. What I'm trying to
do
is an averaging feild of to the right to average those five months as well
as
an average in the group footer to average all the mth1a records in the
group.

The control source for [mth1a] is as follows:
=IIf(IsNull([Mth1])=True,0,[Mth1])
The other feilds are all similar

These reason I did this was becasue I couldn't get an Avg function to
work
if one of detail fields came up Null. However, I've now realized that
when
it comes up zero and is included in the average, that's wrong as well. I
want a null value to be ignored in both Average calcs.

Thanks i advance.

 

[John Spencer]

Not really and getting the average of an average doesn't make much sense to
me.

Perhaps you could use the following to get what you want.

fGetMeanAverage(Sum(Mth1), Sum(Mth2), Sum(Mth3), Sum(Mth4), Sum(Mth5))

Hey John,

I've been using you function here and it works great with one issue... it
doesn't seem to work in group footers. I've tried
=fGetMeanAverage([mth1],[mth2],[Mth3],[Mth4],[Mth5a])
and
=fGetMeanAverage([Avg Of Mth1],[Avg Of Mth2],[Avg Of Mth3]...) using the
named text feilds used to creat the columar averages. ANy ideas?

You could use a custom function in the query for the report. I wrote
this
one quite a while back when I had a similar problem with data I was
importing.

Copy the code below and paste it into a vba module. Save the module with
a
name other than fGetMeanAverage.

In your query, add a calculated column which should probably look like:

Field: TheAvg: fGetMeanAverage( [mth1] ,[mth2] ,[Mth3], [Mth4] ,[Mth5a])


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

Ok,

I have these five fileds in my detail section:
[mth1a] [mth2a] [Mth3a] [Mth4a] [Mth5a]

The fileds represent monthly utilization percentage. What I'm trying
to
do
is an averaging feild of to the right to average those five months as
well
as
an average in the group footer to average all the mth1a records in the
group.

The control source for [mth1a] is as follows:
=IIf(IsNull([Mth1])=True,0,[Mth1])
The other feilds are all similar

These reason I did this was becasue I couldn't get an Avg function to
work
if one of detail fields came up Null. However, I've now realized that
when
it comes up zero and is included in the average, that's wrong as well.
I
want a null value to be ignored in both Average calcs.

Thanks i advance.