#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 1-4 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 1-4 the answer is reversed to 4-1. Look to the formula, it is reversing the answer by calculating cell D30 like so (5-D30). 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 1-4, 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<>"")*(5-B$30)+(C$30<>999)*(C$30<>"")*C$30+(D$30<>999)*(D$30<>"")*(5-D$30)+(E$30<>999)*(E$30<>"")*E$30+(F$30<>999)*(F$30<>"")*(5-F$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 Q50-Q56, 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 non-empty cells, then use the formula Code:
=7-COUNTIFS(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
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#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! |
#12
|
|||
|
|||
Thank you for your help
|
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Calculations | etobias | Word | 2 | 11-09-2016 10:14 AM |
Calculations | etobias | Word | 1 | 01-08-2016 01:50 PM |
Calculations | etobias | Word | 4 | 12-03-2015 01:36 PM |
Tiered Fee Calculations | lynchbro | Excel | 5 | 07-17-2014 11:01 AM |
How to do Calculations In a Text Box? | SteveF | Word | 4 | 09-27-2013 08:34 AM |