By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
 434,677 Members | 1,069 Online
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,677 IT Pros & Developers. It's quick & easy.

# Average expression that can handle null values

 P: 5 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: Expand|Select|Wrap|Line Numbers 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. Expand|Select|Wrap|Line Numbers 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 Nov 11 '13 #1

#### ✓ answered by 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 :
Expand|Select|Wrap|Line Numbers
1. AvgVal: Round(Nz([Field1],0)
2.              +Nz([Field2],0)
3.              +Nz([Field3],0)
4.              +Nz([Field4],0))
5.              /
6.              (IIf([Field1] Is Null,0,1)
7.              +IIf([Field2] Is Null,0,1)
8.              +IIf([Field3] Is Null,0,1)
9.              +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.

Share this Question
6 Replies

 Expert Mod 5K+ P: 5,397 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 Nov 11 '13 #2

 Expert Mod 15k+ P: 31,488 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 : Expand|Select|Wrap|Line Numbers 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. Nov 11 '13 #3

 P: 5 NeoPa, Thanks that works perfectly! Gerry Nov 11 '13 #4

 Expert Mod 15k+ P: 31,488 Pleased to help Gerry. Also pleased that many others can find the answer to similar questions now this is here. Nov 11 '13 #5

 P: 12 Also to solve your rounding issue you can use, which rounds to two places: Expand|Select|Wrap|Line Numbers Expression Name: Round(([Value1]+[Value2]),2) Whatever you put as a value of AnyNumber is the decimal places to round to. Expand|Select|Wrap|Line Numbers Round(NumbersOrExpression,AnyNumber) Jan 7 '14 #6

 Expert Mod 15k+ P: 31,488 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. Jan 8 '14 #7

### Post your reply

Sign in to post your reply or Sign up for a free account.