Average function

[nicktred]

HI

Im tryign to get the average of five fields, on a report. However the fields are not allways in use so i need the average to adjust to any empty fields. How can this be done

eg

f1 0.1
f2
f3 0.35
f4 0.65
f5 -0.15

or could be

f1 0.1
f2 0.5
f3 -0.26
f4 0.123
f5 0.36

Any idea

Cheers

Nick

 

[glalsop]

Is the problem that you are getting a blank result each time one of the fields is null?

If so, passing each field to the Nz() function prior to calculating it:

=( Nz(f1) + Nz(f2) + Nz(f3) + Nz(f4) + Nz(f5) ) / 5

This will convert any null values to zero.

If you want to entirely exclude null's form the calculation, and not convert them to 0, perhaps you could write a function (not tested):

Private Function AvgExcludeNulls() As Double

Dim denom As Integer
denom = 0

If Not IsNull(Me.f1) Then denom = denom + 1
If Not IsNull(Me.f2) Then denom = denom + 1
If Not IsNull(Me.f3) Then denom = denom + 1
If Not IsNull(Me.f4) Then denom = denom + 1
If Not IsNull(Me.f5) Then denom = denom + 1

AvgExcludeNulls = ( Nz(f1) + Nz(f2) + Nz(f3) + Nz(f4) + Nz(f5) ) / denom

End Function

Good luck

-Gary

 

[DrSimon]

Before this can be answered, you need to define 'empty field'. If you want to calculate an average you are probably using numeric fields. If this is the case then an empty field will be Zero. How would distinguish between Zero as a valid value and one that indicates 'empty'? Answer: You can't!

The way round this would be to declare f1 to f5 as text fields and use a function which loops though the fields and calculates N and Sum according to whether they were empty or not. Then it would calculate the average as Sum/N
Simon Rouse

 

[nicktred]

the field is empty as to say there is no value there on some occasions but on others there can be

Ill give the ideas a try thanks for your help will let u know how i get on

cheers

nick

 

[nicktred]

have been looking at the function posted would i put it under the report or as a seperate module? Have tried as both but put out an error as the value

nick

 

[glalsop]

You should be able to put it behind the report (but not in any event).

Then in a text box control on the report you can call it:

=AvgExcludeNulls()

What errors are you getting? Is it possible for ALL 5 fields to be empty?

-Gary

 

[MichaelRed]

search for basAvg (using advanced search) in these fora. The issue has been raised and 'answered' previously. -as have MANY topics. More often than not, a toughtful search will provide a number of threads which address the subject of interest. Of course, it DOES require some dilligence as the specific keyword(s) may be elusive, but continued use often illuminates the process.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[nicktred]

Thanks for all you replies got it working using the example code supplied the error was stupid sorted it out had put one of the field names in incorrectly.

Thanks Again

Nick