Calculating an Average in a series of differant fields

[RobinL]

I have a form that has about 9 fields that have a series
of values. Now not all of the fields will have a value so
if 4 of the 9 field have a value and I need to calculate
the average value of the 4 field , can sum one help me
with the necessary formula. If there are 7 values out of
9 the same formula needs to work. Just as if there is 9
out of 9.

Thank you

Robin

 

[John Vinson]

I have a form that has about 9 fields that have a series
of values. Now not all of the fields will have a value so
if 4 of the 9 field have a value and I need to calculate
the average value of the 4 field , can sum one help me
with the necessary formula. If there are 7 values out of
9 the same formula needs to work. Just as if there is 9
out of 9.

This calculation works much better if you normalize the data so that
you're averaging across records instead of across fields...

You can use the fact that IsNull returns -1 (True) if the field is
NULL, 0 (False) if not:

AvgOfNine: ([Field1] + [Field2] + [Field3] + [Field4] + [Field5] +
[Field6] + [Field7] + [Field8] + [Field9])/(IsNull([Field1]) +
IsNull([Field1]) + IsNull([Field1]) + IsNull([Field1]) +
IsNull([Field1]) + IsNull([Field1]) + IsNull([Field1]) +
IsNull([Field1]) + IsNull([Field1]) + 9)

Each NULL will subtract one from the denominator.