Calculating Averages
Good afternoon John,
The first expression you provided worked. That's great!
It sums up the numbers and omits any fields that have NA. I set the same
expression to count in another textbox which returns back the count of fields
with a numeric figure. I divided one by the other and obtained my average.
Thanks a million!!! You're a life saver!
--
Rene Lazaro
"John Spencer" wrote:
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
|