Averaging across fields with null values

[chatfield]

I would like to display the average of 10 fields in a record on my report. How do I handle fields that contain null values? How do I ignore them and just include fields with values in them?

Thanks.

cjhatfield

 

[scriverb]

It all depends on what the Null means. Should your average include all ten fields in its calculation of the Sum. This is for you to determine. If so then your can caluclate the average with the following as the Control Source for your calculated control on the report:

=(NZ(me![Field1],0) + NZ(me![Field2],0) + . . .NZ(Field10],0))/10

If the Nulls should be left out of the calculation use the following:

=(NZ(me![Field1],0) + NZ(me![Field2],0) + . . .NZ(Field10],0))/(IIF(IsNull(me![Field1]),0,1) + IIF(IsNull(me![Field2]),0,1) + . . . IIF(IsNull(me![Field10]),0,1))

This one adds up all of the fields including the Null values which will be handled as a zero(0) value but be dividied only by the number of non Null values.

Bob Scriver