Average help needed

[Guest]

Hello all,

I have a unbound text box on a form and I would like to have the control
source set up to average totals from 3 fields on the form. Any of the fields
could be null. I have tried using the nz function and dividing by 3 but the
problem I am having is that it will still divide by 3 even though there is
data in 2 of the fields. Any ideas??

Thanks to all

 

[David F Cox]

What "it" ?
If "it" is the avg function that is used in queries, it is smart enough not
to count null values as well as smart enough not to sum them.
Do you mean, perhaps, that you are adding the fields and are still dividing
by 3 even if one or more is null?

divisor = 3 + isnull(field1) + isnull(field2) + isnull(field3)
if divisor = 0 then
'nothing to do
else ' calc average
.......

 

[JK]

Bruce what your are getting is correct (3+3+0)/3=2

I guess that you are trying to average the filed which are not null. That
being the case:
This should work:

= IIf(Nz([Fld1], 0) = 0 And Nz([Fld2], 0) = 0 And Nz([Fld3], 0) = 0, 0,
(Nz([Fld1], 0) + Nz([Fld2], 0) + Nz([Fld3], 0)) / (IIf(Nz([Fld1], 0) = 0,
0, 1) + IIf(Nz([Fld2], 0) = 0, 0, 1) + IIf(Nz([Fld3], 0) = 0, 0, 1)))

Hopefully I did not confuse both of us

regards/JK

 

[Guest]

Thank you for your reply. I was trying to have the text box do the averaging.
However, I decided to do the avg in a query instead.
--
Bruce DiPaola

What "it" ?
If "it" is the avg function that is used in queries, it is smart enough not
to count null values as well as smart enough not to sum them.
Do you mean, perhaps, that you are adding the fields and are still dividing
by 3 even if one or more is null?

divisor = 3 + isnull(field1) + isnull(field2) + isnull(field3)
if divisor = 0 then
'nothing to do
else ' calc average
.......