Average expression that can handle null values

[gaf44]

Hi, I am trying to create a make-table query that includes an expression which will average four fields.

My problem is that the fields contain null values for certain rows.

I want the expression to take the average of the non-null values (including zeros) within the four fields and disregard the nulls.

Here's a table describing what I want my outcome table to show:

ID  Field1   Field2    Field3   Field4  Average
1    19         2                  5      8.67
2               3         2        1       2 
3     1         1         1        0      0.75

Here is the expression I am trying to use but something is messing it up.

Average:([Field1]+[Field2]+[Field3]+[Field4])/(4-iif(isnull([Field1]),1,0)-iif(isnull([Field2]),1,0)-iif(isnull([Field3]),1,0)-iif(isnull([Field4]),1,0))

Could someone help me write the appropriate expression?

Also, I would like my expression to return values with decimals to the tenths place.

Thank you! Gerry

[zmbd]

It looks like you are ignoring the Null fields in the average calculation, correct:(19+2+5)/3 not (19+2+0+5)/4?

-

I also suggest that you avoid using "Average" as a field name:
Access 2007 reserved words and symbols
AllenBrowne- Problem names and reserved words in Access

[NeoPa]

If any of the fields in your expression hold Nulls then the dividend (The number which is divided by the divisor) becomes Null. As such, unless all fields are populated, the result will be Null.

Try instead :

AvgVal: Round(Nz([Field1],0)
             +Nz([Field2],0)
             +Nz([Field3],0)
             +Nz([Field4],0))
             /
             (IIf([Field1] Is Null,0,1)
             +IIf([Field2] Is Null,0,1)
             +IIf([Field3] Is Null,0,1)
             +IIf([Field4] Is Null,0,1),1)

Of course, this doesn't handle the "Divide by zero" error if ever all 4 fields are Null.

PS. Thanks to MorganaJ for the tip about rounding which I'd forgotten to include in the answer.

[gaf44]

NeoPa,

Thanks that works perfectly!

Gerry

[NeoPa]

Pleased to help Gerry.

Also pleased that many others can find the answer to similar questions now this is here.

[morganaj]

Also to solve your rounding issue you can use, which rounds to two places:

Expression Name: Round(([Value1]+[Value2]),2)

Whatever you put as a value of AnyNumber is the decimal places to round to.

Round(NumbersOrExpression,AnyNumber)

[NeoPa]

Indeed Morgana. I forgot to deal with that part of the question when I posted that originally. Since your post I've changed the post retrospectively to include the full answer as it should have been. Thank you for that.