 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Reports / Printing / July 2007Calculating 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.  SignatureRene Lazaro Try the following. Avg(IIF([Score A] = "NA",Null, Val([Score A]))) SignatureJohn 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 > [quoted text clipped - 15 lines] > > Please help. How would i include [Score B], [Score C], etc. Thanks. SignatureRene Lazaro > Try the following. > [quoted text clipped - 19 lines] > > > > Please help. 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. 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. SignatureRene Lazaro > Avg(IIF([Score A] = "NA",Null, Val([Score A]))) + > Avg(IIF([Score B] = "NA",Null, Val([Score B]))) + [quoted text clipped - 16 lines] > > > > Thanks. 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. SignatureJohn Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County . > Good morning John, > [quoted text clipped - 25 lines] >> > >> > Thanks. 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. SignatureRene Lazaro > Sorry, but I can't see why you are getting error returned. You might try > [quoted text clipped - 32 lines] > >> > > >> > Thanks. Good afternoon John, Please help. Thank you. SignatureRene Lazaro > I am trying to calculate an average on the following > [quoted text clipped - 14 lines] > > Please help. 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)) SignatureJohn Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County . > Good afternoon John, > [quoted text clipped - 21 lines] >> >> Please help. 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! SignatureRene Lazaro > Try > [quoted text clipped - 34 lines] > >> > >> Please help. Good afternoon John, It worked great!!! You're a live saver!!! Thanks for all your help and patience. Rene SignatureRene Lazaro > Try > [quoted text clipped - 34 lines] > >> > >> Please help. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.