Avg expression in a query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Radhika - 19 May 2008 16:53 GMT

I am trying to build an expression in a query that calculates the average of
three fields. The three fields are called 'OPSE 10 ml Avg', 'OPSE pudding
Avg' and 'OPSE cracker Avg'. I want to caculate the average of all three
feilds. However, if one of the fileds is missing, I want the caculation to be
the average of all the other values that are not null instead of counting the
missing field as '0'.
The following is the experssion I have:
OPSE Alll Avg: (0+Nz([OPSE 10 ml Avg],[OPSE Pudding Avg],[OPSE cracker
Avg)+Nz([OPSE cracker Avg],[OPSE Pudding Avg],[OPSE 10 ml Avg]))/3

This gives me an error msg saying that the wrong number of arguements have
been used. What can I be doing wrong?

Thankyou,
Radhika

Reply to this Message

Jerry Whittle - 19 May 2008 17:42 GMT

OPSE Alll Avg:
(Nz([NUM_NULLS],0)+Nz([NUM_BUCKETS],0)+Nz([SAMPLE_SIZE],0))/((IsNull([NUM_NULLS])+IsNull([NUM_BUCKETS])+IsNull([SAMPLE_SIZE]))+3)

This works because IsNull returns a True or -1 for Nulls and a 0 for Falses.

Problems:

If all three fields are null, you can't divide a Null. You probably need an
Or statement to trap those.

If all three fields add up to 0 in the divisor, you'll get a divide by 0
error. That can be any combination of Nulls and actual 0 values.

Signature

Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

> I am trying to build an expression in a query that calculates the average of
> three fields. The three fields are called 'OPSE 10 ml Avg', 'OPSE pudding
[quoted text clipped - 11 lines]
> Thankyou,
> Radhika

Reply to this Message

Guillermo_Lopez - 19 May 2008 17:46 GMT

On May 19, 11:53 am, Radhika <Radh...@discussions.microsoft.com>
wrote:

Because Nz only supports 2 arguments.

Avg and Count only use non-null values.

Try it like this:

(Nz([OPSE cracker Avg],0)+Nz([OPSE Pudding Avg],0)+Nz([OPSE 10 ml Avg],
0))/3

If any of those are null, it will set it to zero.

- GL

Reply to this Message

Radhika - 19 May 2008 19:55 GMT

Is there any way in which the expression can be modified so that it omits a
null value from the avg calculation instead of calculating it as 0. For
example,
OPSE 10 ml Avg = Null
OPSE pudding Avg = 2
OPSE cracker Avg = 3

Avg All = 2.5, instead of 1.66.

How can I do this?

Thankyou!

> On May 19, 11:53 am, Radhika <Radh...@discussions.microsoft.com>
> wrote:
[quoted text clipped - 26 lines]
>
> - GL

Reply to this Message

Guillermo_Lopez - 22 May 2008 15:14 GMT

> Is there any way in which the expression can be modified so that it omits a
> null value from the avg calculation instead of calculating it as 0. For
[quoted text clipped - 41 lines]
>
> - Show quoted text -

Use Jerry's formula:

"OPSE Alll Avg:
(Nz([NUM_NULLS],0)+Nz([NUM_BUCKETS],0)+Nz([SAMPLE_SIZE],0))/
((IsNull([NUM_NULLS])+IsNull([NUM_BUCKETS])+IsNull([SAMPLE_SIZE]))+3)"

Reply to this Message