Avg expression in a query

[Radhika]

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

 

[Jerry Whittle]

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.

 

[Guillermo_Lopez]

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 tobe
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

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

 

[Radhika]

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!

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

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

 

[Guillermo_Lopez]

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!

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

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- Hide quoted text -

- Show quoted text -

Use Jerry's formula:

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