Calculating Averages
Try
Avg(IIF(IsNumeric([ScoreA]),Val([ScoreA]),0) + IIF(IsNumeric([Score
b]),Val([Score B]),0) + IIF(IsNumeric([Score C]),Val([Score C]),0))
That will average in a 0 if all the scores are in the row are NA.
If that is not acceptable then you will have to write a more complex
expression that might look something like
Avg(IIF (IsNumber(A) + IsNumeric(B) + IsNumeric(C) = 0,
Null,
IIF(IsNumeric([ScoreA]),Val([ScoreA]),0) + IIF(IsNumeric([Score
b]),Val([Score B]),0) + IIF(IsNumeric([Score C]),Val([Score C]),0))
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
"Rene Hernandez" wrote in message
...
Good afternoon John,
Please help.
Thank you.
--
Rene Lazaro
"Rene Hernandez" wrote:
I am trying to calculate an average on the following
[score A]+[score B]+[score C]+[score D]
These are text fields with validation rule
="NA" or Between 1 and 5
I know that the Avg and Sum functions omit Null values but how can i get
it
to omit "NA" and what is the function for calculating the average.
I try to calculate the average using
=Avg([Score A])+Avg([Score B]), etc.
but it does not work.
Please help.
--
Rene Lazaro
|