Problem with Avg calculation in subform

[Guest]

Hello,

I am having a problem with an average calculation on my form. I have a
continuous form with a field called "Score" and that field can either have a
numerical value in it or an "NA" in it. I have the following code in "score."

=IIf([Score]=0,"NA",[Score])

The problem is, I want to take an average of all the NUMERICAL scores in
that in subform, and not include the NAs (0s). However....I am getting an
average INCLUDING those zero amounts and I want to exclude those.

here is the code i'm currently usinig in that average score field:

=Avg(IIf([Score]<>"NA",([Score])))

However, this is not giving me the desired result -- it's including those
zeros in there.

Any ideas?

Thanks,
MN

 

[John W. Vinson]

Hello,

I am having a problem with an average calculation on my form. I have a
continuous form with a field called "Score" and that field can either have a
numerical value in it or an "NA" in it. I have the following code in "score."

=IIf([Score]=0,"NA",[Score])

The problem is, I want to take an average of all the NUMERICAL scores in
that in subform, and not include the NAs (0s). However....I am getting an
average INCLUDING those zero amounts and I want to exclude those.

here is the code i'm currently usinig in that average score field:

=Avg(IIf([Score]<>"NA",([Score])))

However, this is not giving me the desired result -- it's including those
zeros in there.

Try
=IIF([Score] = 0, Null, [Score])

Avg() will ignore the nulls; you can use a format of

"#";"-#";"0";"NA"

to display the Null values as NA.

John W. Vinson [MVP]