Averaging fields with missing data

[mfred1]

I have a set of fields in a query for which I need to do an average for each entry. Let's say there are 5 fields. The issue is that not all 5 fields have data. So I cannot do ([Field1]+[Field2]+[Field3]+[Field4]+[Field5])/5. Any flank fields causes the average to be blank. Is there a way to create an average calculation in Access where blank fields are ignored?

[Rawb]

It sounds like your Table might not be properly normalized. If you have control over the design of your Tables, you might want to look at coming up with a more normalized structure for them.

That said...

IIf([Field1]+[Field2]+[Field3]+[Field4]+[Field5]>0,[Field1]+[Field2]+[Field3]+[Field4]+[Field5]/(IIf([Field1]>0,1,0)+IIf([Field2]>0,1,0)+IIf([Field3]>0,1,0)+IIf([Field4]>0,1,0)+IIf([Field5]>0,1,0)),0)

Assuming all of your Columns (when non-0 and non-blank) will be positive numbers, this equation will check if at least one of the Columns has a number in it.

• If not, then it just returns 0.
• If so, it will add the 5 Columns together and then divide them by the number of non-0 Columns.

If a Column can be completely blank, then you will need to wrap everything in Nz() Functions to force them to return 0 when empty.

IIf(Nz([Field1],0)+Nz([Field2],0)+Nz([Field3],0)+Nz([Field4],0)+Nz([Field5],0)>0,Nz([Field1],0)+Nz([Field2],0)+Nz([Field3],0)+Nz([Field4],0)+Nz([Field5],0)/(IIf(Nz([Field1],0)>0,1,0)+IIf(Nz([Field2],0)>0,1,0)+IIf(Nz([Field3],0)>0,1,0)+IIf(Nz([Field4],0)>0,1,0)+IIf(Nz([Field5],0)>0,1,0)),0)

[mfred1]

Thank you very much.