calculating an average with one or more values being NULL

[Guest]

I have a situation where I'm trying to design a query that will calculate the
ratio of one field to the average of 4 other fields for a given table. The
problem is that in some records one or more of the values that are in the
average calculation might be NULL. When this is the case, I'd like to modify
the calculation to the ratio of one field to the average of the remaining
fields that are not NULL. Is there a way to do this in SQL, or do I need to
use VBA?

 

[Andreas]

Can be done in the query.
1. 4 calculated columns: IsNull(Fieldx)
2. 1 calculated column: CalculatedColumn1 + ... + CalculatedColumn4
3. 1 calculated column: Nz(Field1) + ... + Nz(Field4)
4. 1 calculated column: divide result from 3. by result of ABS(2.)

Regards,
Andreas

 

[Andreas]

Just realized a mistake in step 4.
You need to divide by 4 + result from step 2.

Or, to put it all into a single column:
Create a calculated column as follows:
(Nz(Field1,0) + Nz(Field2,0) + Nz(Field3,0) + Nz(Field4,0)) / (4 +
IsNull(Field1) + IsNull(Field2) + IsNull(Field3) + IsNull(Field4))

This will work as long as not all 4 fields are Null.
If this is a possibility, you may get an error.
In this case use an IIF function around the last part of the above.

Regards,
Andreas

 

[Guest]

That worked great! One last question, occasionally, I will have all 4 divisor
fields be NULL, so I included the IIf functions as follows:

(Field1/((Nz(Field2)+Nz(Field3)+Nz(Field4)+Nz(Field5))/(IIf(4+IsNUll(Field2)+IsNull(Field3)+IsNull(Field4)+IsNull(Field5)),(4+IsNUll(Field2)+IsNull(Field3)+IsNull(Field4)+IsNull(Field5)),NULL)))

When I try to save the query, I get an error: Syntax error (comma) in query
expression '<the above expression>'

I looked up the IIf function and it includes the commas. Is it the NULL
value in the falsepart? If so, how do I assign a NULL value as an expression
result?

Thanks,

Jeff

 

[Andreas]

Try:
Field1/
(IIF((Nz(Field2)+Nz(Field3)+Nz(Field4)+Nz(Field5))<>0,Nz(Field2)+Nz(Field3)+Nz(Field4)+Nz(Field5),1))/
(IIf((4+IsNull(Field2)+IsNull(Field3)+IsNull(Field4)+IsNull(Field5))<>0,4+IsNull(Field2)+IsNull(Field3)+IsNull(Field4)+IsNull(Field5),1))

If you want a blank result when all 4 are Null, then change the 1 at the
end of both IIF functions to a Null.

I would also use seperate calculated columns and refer to the
intermediate results.
That way Access won't have to compute the answer just to compute it
again. Depending on the amount of data, this could be a lot faster.
Also, it keeps each part simpler and makes it easier to debug.

Regards,
Andreas