Calculating Averages

[Guest]

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.

 

[John Spencer]

Try the following.

Avg(IIF([Score A] = "NA",Null, Val([Score A])))

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

How would i include [Score B], [Score C], etc.

Thanks.
--
Rene Lazaro

Try the following.

Avg(IIF([Score A] = "NA",Null, Val([Score A])))

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

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.

 

[John Spencer]

Avg(IIF([Score A] = "NA",Null, Val([Score A]))) +
Avg(IIF([Score B] = "NA",Null, Val([Score B]))) +
Avg(IIF([Score C] = "NA",Null, Val([Score C]))) + ...

I am not sure why you are adding the averages but if that is what you
want to do then this will work.

If you want the average score for A and the average score for B then you
need to do the calculation in separate fields.n



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Guest]

Good morning John,

I tried that but it gave me #error. I thought i might've been doing
something wrong.

Other suggestions.

I appreciate your help. Await your response.


--
Rene Lazaro

Avg(IIF([Score A] = "NA",Null, Val([Score A]))) +
Avg(IIF([Score B] = "NA",Null, Val([Score B]))) +
Avg(IIF([Score C] = "NA",Null, Val([Score C]))) + ...

I am not sure why you are adding the averages but if that is what you
want to do then this will work.

If you want the average score for A and the average score for B then you
need to do the calculation in separate fields.n



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

How would i include [Score B], [Score C], etc.

Thanks.

 

[Guest]

Good afternoon John,

Please help.

Thank you.

 

[John Spencer]

Sorry, but I can't see why you are getting error returned. You might try

Avg(IIF(IsNumeric([Score A],Val[Score A],Null))

Does that work to return a value? If so, then try adding the other parts
one at a time and see if they work.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Good morning John,

I tried that but it gave me #error. I thought i might've been doing
something wrong.

Other suggestions.

I appreciate your help. Await your response.


--
Rene Lazaro

Avg(IIF([Score A] = "NA",Null, Val([Score A]))) +
Avg(IIF([Score B] = "NA",Null, Val([Score B]))) +
Avg(IIF([Score C] = "NA",Null, Val([Score C]))) + ...

I am not sure why you are adding the averages but if that is what you
want to do then this will work.

If you want the average score for A and the average score for B then you
need to do the calculation in separate fields.n



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

How would i include [Score B], [Score C], etc.

Thanks.

 

[Guest]

Good afternoon John,

I tried the other formula once again and it returned a value; it added
instead of averaging. Also, when i enter NA, it does not return a value at
all, it simply goes blank.

I apologize for the inconvinience and do appreicate your help with this.
What can i do to get it to Average instead of adding and to factor in the NA
in order to set it to Null.
--
Rene Lazaro

Sorry, but I can't see why you are getting error returned. You might try

Avg(IIF(IsNumeric([Score A],Val[Score A],Null))

Does that work to return a value? If so, then try adding the other parts
one at a time and see if they work.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Good morning John,

I tried that but it gave me #error. I thought i might've been doing
something wrong.

Other suggestions.

I appreciate your help. Await your response.


--
Rene Lazaro

Avg(IIF([Score A] = "NA",Null, Val([Score A]))) +
Avg(IIF([Score B] = "NA",Null, Val([Score B]))) +
Avg(IIF([Score C] = "NA",Null, Val([Score C]))) + ...

I am not sure why you are adding the averages but if that is what you
want to do then this will work.

If you want the average score for A and the average score for B then you
need to do the calculation in separate fields.n



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Rene Hernandez wrote:
How would i include [Score B], [Score C], etc.

Thanks.

 

[John Spencer]

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

Good afternoon John,

Please help.

Thank you.
--
Rene Lazaro

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.

 

[Guest]

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

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

Good afternoon John,

Please help.

Thank you.
--
Rene Lazaro

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.

 

[Guest]

Good afternoon John,

It worked great!!! You're a live saver!!!

Thanks for all your help and patience.

Rene
--
Rene Lazaro

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

Good afternoon John,

Please help.

Thank you.
--
Rene Lazaro

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.