If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Calculating Averages
Good afternoon John,
It worked great!!! You're a live saver!!! Thanks for all your help and patience. Rene -- 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 |
|
Thread Tools | |
Display Modes | |
|
|