Ignoring Unanswered Questions

[TSCworkhelp]

Hi all. I need some help with the attached file, hopefully it’s a simple answer.

I work in an office where patients need to fill out some questionnaires – one of them is tedious to score by hand, so I created this Excel file to do it quicker. The form is 60 questions long, and scores are determined by entering their answers into the “Full Form” tab: Strongly Agree (SA) = 1, Agree (A) = 2, Disagree (D) = 3, and Disagree (SD) = 4; however, some of these scores are subtracted from 5 while others aren’t. For example:

• Patient answers “Strongly Agree” for Question 6, which calculates raw score only
***SA = 1; the score is 1
• Patient answers “Strongly Agree” for Question 7, which calculates the raw score subtracted from 5
***SA = 1; 5-1=4; the score is 4

Final scores are transcribed into their particular categories (seen in the “Scoring” tab). Those categories are then totaled and divided by the number of questions that were answered. For example, the scores for Category A are 3 + 2 + 4 + 3 + 3 + 4 = 19… 19/6 = final score 3.17

The spreadsheet works well, but there is a problem when patients skip questions. A skipped question shows up as a number (either 0 or 5) and is calculated as an answered question, which is then used to divide for the final score. How can I have it adjust to ignore unanswered questions?

I really hope I explained it correctly. If not, hopefully the spreadsheet sample I’m providing will make more sense.

Thank you!

[6StringJazzer]

First I'll give an answer that requires the minimal change to your file. If the normalized score on Scoring!J:P is either 0 or 5, it means the question was not answered. So in your formulas on row 64 of that sheet, use this formula in J64 and copy to the right:

Please Login or Register  to view this content.

PS Why do you require a lowercase "x"? An uppercase "X" would also work with your formulas.

[6StringJazzer]

The design of your data requires a lot of manual work if you want to change any detail about your questionnaire.

Instead of using the location of the formulas on Scoring to identify which category the question is in, I would identify the category right next to the question. I have done this on the Guide sheet. (Note that on the Guide sheet you have the counts for each category but they are just typed-in values. I changed them to formulas.)

Now the formulas on Scoring can all the be same, in every cell. They automatically determine which category the question is in, and whether the score needs to be subtracted from 5.

This is much more flexible because if you change a question to make it a different category, or change whether the score must be subtracted from 5, you just make the appropriate notation on the Guide sheet and you don't have to touch any formulas.

[TSCworkhelp]

I added your COUNTIFS formula with >0 and <5 and it now functions exactly as I hoped it would! Thank you so much!

I'm trying to implement the other changes you suggested, simply to make the spreadsheet easier to use, but I became stumped at this part in the scoring tab: =IF("Category "&Guide!$H3=Scoring!P$2,IF(Guide!$G3="",Scoring!$H3,5-Scoring!$H3),"")

I had to change all of the wording in my sample file for fear of accidentally violating copyrights with identifiable info ("Category A" is not really such a simple name, the "Guide" tab refers to the document name, etc.), so now I'm trying to take what you did and apply it to my actual spreadsheet, but I think that's where I'm tripping myself up. I'm not sure if I don't understand the formula or if I'm just making an error in trying to transfer the information to my actual data – for example, is “A” referring to the Category or column, etc.

I’m uploading the same sample spreadsheet but renamed the categories to be easier to distinguish from one another and from columns/rows. Can you please use those formulas again, but with the new category names so that I can try to understand it better?

Thanks again for all your help, and also for mentioning there's no difference between entering a lower or uppercase x for it to work.. whoops!

[6StringJazzer]

Here is a revision of what I did with your new category names. It simplifies some of the formulas.

[TSCworkhelp]

Thank you for your help and for such a fast reply! I am only at this work site once per week, so I won't get a chance to really look at it again (and try to make the changes myself) until next Friday, but it already looks a bit easier for me to understand. Thanks again!!

[TSCworkhelp]

All done! Your edits were perfect and I was able to understand them much better with the most recent file. The scoring sheet now works exactly as I hoped it would.

Thanks for your help!!