By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,466 Members | 1,599 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,466 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
  1. ID  Field1   Field2    Field3   Field4  Average
  2. 1    19         2                  5      8.67
  3. 2               3         2        1       2 
  4. 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
  1. 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
Share on Google+
6 Replies


zmbd
Expert Mod 5K+
P: 5,286
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

NeoPa
Expert Mod 15k+
P: 31,186
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.
Nov 11 '13 #3

P: 5
NeoPa,

Thanks that works perfectly!

Gerry
Nov 11 '13 #4

NeoPa
Expert Mod 15k+
P: 31,186
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
  1. 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
  1. Round(NumbersOrExpression,AnyNumber)
Jan 7 '14 #6

NeoPa
Expert Mod 15k+
P: 31,186
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.