#1




Problem> do not include 999 in calculations
Good evening,
I must start by stating I'm not even a novice when it comes to working formulas in excel. I could really use some help, please. I have attached a file that I am trying to get the MEAN/AVERAGE for the values in the rows. It is part of a questionnaire I use. Some of the questions have been reversed to maintain the interity of the survey. I have added value for a question that is outside the 14 response, 999. when I am calculation the averages I do not want to include the 999. I also have some blank cells which need to remain blank, indicating the question was not answered. another thing I was hoping to see if possible to add to COUNT part at end of formula is that the average only is counted if the number of questions answered, or the number of 999 is greater than 50%. I would really appreciate any direction and input you may have. Thank you 
#2




Use this formula to exclude 999 from the calculation
=AVERAGEIF(B2:J2,"<>999") and copy down. 
#3




Hi Alansidman,
Thank you for the response. The formula would work if it was a simple calculation. I have attached another file, have a look at row 30, if cell d30 is changed to 0 it will give the true mean for the questionnaire. The reason is some of the questions are scored in reverse order. Likert scale is 14 the answer is reversed to 41. Look to the formula, it is reversing the answer by calculating cell D30 like so (5D30). If I remove the 999 and replace it with a blank I get an error. I cannot change the 999 to a blank because some answers have been left blank. If I replace the 999 with a zero I get the true average for the row. I'd like to stick with the 999 because they represent an answer outside the Likert 14, which I added. Thank you for your help, 
#4




I don't have a response for you. Hopefully, someone with a better understanding of your scoring methodology will be able to help. Good Luck.

#5




Unless I misunderstood you completely, something like this will do
Code:
=((A$30<>999)*(A$30<>"")*A$30+(B$30<>999)*(B$30<>"")*(5B$30)+(C$30<>999)*(C$30<>"")*C$30+(D$30<>999)*(D$30<>"")*(5D$30)+(E$30<>999)*(E$30<>"")*E$30+(F$30<>999)*(F$30<>"")*(5F$30)+(G$30<>999)*(G$30<>"")*G$30)/(COUNTIFS(A$30:G$30,"<>999")COUNTIFS(A$30:G$30,"")) 
#6




Quote:
Thank you very much for all the help. 
#7




Quote:
Can I complicate things somewhat further? In the range Q50Q56, the denominator is 7, if one was blank i.e. not answered the denominator is now six if two were blank the denominator would be 5. Is there a way to write this into the formula? Or could the 999 be changed to a blank for the calculation, by that the 999 equal a blank cell and reduces the denominator? Thank you for the help 
#8




You got me fully confused! Are you writing about same formula, or another one? And what is denominator?
I'm quessing now. When you want in range Q50:C56 to get the number of nonempty cells, then use the formula Code:
=7COUNTIFS(Q50:C56,"") 
#9




Quote:
Am I making any sense? 
#10




@will soar
please do not quote entire posts unnecessarily. They clutter the thread and make it hard to read. Use "quick reply" to avoid quotes. Thanks
__________________
Problem solved ? Let others know by clicking " Thread Tools" then " Mark thread as solved".( This can be undone if need be) Want to thank for the help received ? Click the scales symbol in the upper right corner of a post from the person you want to thank. 
#11




Maybe you load an example workbook up here, with comments about where you want the formula, what the formula must to do, and from where data are read.
Currently you have writing about 2 different ranges (Q50:Q56 and A30:G30), and there is no hint how they are related, or are they related at all. In initial workbook you uploaded the second range is blank! 
Thread Tools  
Display Modes  

Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
Calculations  etobias  Word  2  11092016 10:14 AM 
Calculations  etobias  Word  1  01082016 01:50 PM 
Calculations  etobias  Word  4  12032015 01:36 PM 
Tiered Fee Calculations  lynchbro  Excel  5  07172014 11:01 AM 
How to do Calculations In a Text Box?  SteveF  Word  4  09272013 08:34 AM 