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 |
#1
|
|||
|
|||
Calculating Averages
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 |
#2
|
|||
|
|||
Calculating Averages
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 .. "Rene Hernandez" wrote in message ... 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 |
#3
|
|||
|
|||
Calculating Averages
How would i include [Score B], [Score C], etc.
Thanks. -- Rene Lazaro "John Spencer" wrote: 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 .. "Rene Hernandez" wrote in message ... 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 |
#4
|
|||
|
|||
Calculating Averages
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. |
#5
|
|||
|
|||
Calculating Averages
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 "John Spencer" wrote: 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. |
#6
|
|||
|
|||
Calculating Averages
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 |
#7
|
|||
|
|||
Calculating Averages
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 .. "Rene Hernandez" wrote in message ... 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 "John Spencer" wrote: 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. |
#8
|
|||
|
|||
Calculating Averages
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 "John Spencer" wrote: 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 .. "Rene Hernandez" wrote in message ... 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 "John Spencer" wrote: 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. |
#9
|
|||
|
|||
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 |
#10
|
|||
|
|||
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 |
|
Thread Tools | |
Display Modes | |
|
|