Average is showing #Error when all cells are null

[homage]

I have the following in a query

Avg: (nz([A],0)+nz(,0)+nz([C],0))/(3-IIf(IsNull([A]),1,0)-IIf(IsNull(),1,0)-IIf(IsNull([C]),1,0))
which seems to work really well except when A, B, and C are all null. I would like it to show 0.

 

[MarkK]

If they are all null, your math in the denominator results in zero, and division by zero causes an error. You need to test for this case before you do your math, and don't do the division if this will be the result.

Avg: IIF( Nz(A, 0) + Nz(B, 0) + Nz(C, 0) = 0, 0, <your expression here>)

But it also looks like a non-normalized table. Rather than fields A, B, and C, you should fields Type, and Value. Type should contain "A", "B", or "C", and Value is obvious. Then your sums, averages, and all your summaries are so, so, so much easier. Using the design I propose, your query could do . . .

AvgValue: Avg(Value)

This is a direct cost of not normalizing your data.

 

[gemma-the-husky]

Avg: (nz([A],0)+nz(,0)+nz([C],0))/(3-IIf(IsNull([A]),1,0)-IIf(IsNull(),1,0)-IIf(IsNull([C]),1,0))

if A,B, and C are all null, then your denominator evaluates as 0.

0/0 is an error.

 

[homage]

I'm not sure what you mean by not normalizing my data. Do you think I'm not trying to average values? I am trying to normalize my data. I have three sets of values, some of which have null. I am trying to get an average to hopefully get usable data for other queries. I'm getting 15687 records with about 300 showing #Error.

How do I tweak the formula to show 0?