Solved
How can I calculate a negative score to equal 0?
I have a test that includes a question that is a "Select all that apply" question. There are 3 correct answers and 2 incorrect answers.
Each question is worth 4 points. For the correct answers I score them with +1.33. For incorrect answers I score them with -2.
I do not want to limit the number of selections, so they are able to select all 5, and this would give them a score of -.01. I can fix that by setting one of the correct answers to +1.34. But, if they select 2 incorrect answers, only, then they will have a score of -4.
What I want to do is have all negative scores to be calculated as a 0 for that question (something like,,, if SCORE<0, then SCORE=0). Is this possible and if so how would I do it?
Maybe there is an easier way that I have not thought of, so I'm open to other suggestions as well.
Best answer by Rex
Ok, I have a work around, although it may not be perfect it will work for me for now. As I get more familiar with qualtrics I may be able to get it all completed inside of qualtrics, but I had to bring in excel to get my final calculation.
I had 5 questions on the 25 question exam that were "Select All that Apply" type questions.
So I created 5 additional Score Categories (SC1, SC2...SC5) one for each of the 5 questions. I took these 5 questions out of the standard Score (SC0) category by just not assigning a value to those 5 questions.
For each question, I would allocate the amount of points that would total the score for that question both positive and negative. Each question is worth 4 points. So if I had 4 correct answers for that question each correct answer would be +1 point (total +4). If I had 2 incorrect answers on that question each incorrect answer would be worth -2 points (total -4).
When I export the data I have 6 columns for score. SC0, is for all questions that are not "Select All that apply" types, and SC1-SC5 for each of the "Select All that apply" type questions.
In Excel, I want to add up all of these scores, but if you recall I want to have negative scores calculated to 0. So I have a master score sheet that will perform this calculation.
I copy in the exported data file into my master score spread sheet, in the "Data" worksheet. Then on my "Final Score" worksheet I have the calculation below. Column "Data!AS4" is the data from SC0, "Data!AT4" is the data from SC1...on so on.
=SUM(Data!AS4+IF(Data!AT4<0,0,Data!AT4)+IF(Data!AU4<0,0,Data!AU4)+IF(Data!AV4<0,0,Data!AV4)+IF(Data!AW4<0,0,Data!AW4)+IF(Data!AX4<0,0,Data!AX4))
I hope this makes sense and helps someone else out there who may have this problem. If you have a better work around please feel free to share.
+38I'm not sure how many questions your test is but it sounds like you could use survey flow to take care of this.
In Survey Flow
1) Set Embedded Data -> RoundedScore = use piped text to get your score for this question
2) Branch IF Embedded Data RoundedScore < 0
3) Indented under the branch Set Embedded Data -> RoundedScore = 0
The downside of this is that you would have to refer to the embedded data field instead of calling the scoring variable for any reporting you do back to the respondent. This may mean you have to set all the scoring variables as embedded data fields to get the output you want in the end.
Thank you @bstrahin . Very interesting. I have tried this on a sample set and there are some roadblocks or challenges that I have.
I suspect that each question would have to be in its own block to be able to do this? I had all the questions in 1 block to eliminate the need of the user to have to select >>NEXT after each question.
To have the final score calculated I suppose that I would have to generate a running total of RoundedScore.
I cannot just Add RoundedScore to Score, so as you mentioned I would have to calculate RoundedScore for every question.
Thank you for your help, and I think this would work fine for a one time situation. My concern is as the test changes over time, I would have to do this each time a question changes. I was hoping there might be an easier solution.
PS...the test has 25 questions.
Ok, I have a work around, although it may not be perfect it will work for me for now. As I get more familiar with qualtrics I may be able to get it all completed inside of qualtrics, but I had to bring in excel to get my final calculation.
I had 5 questions on the 25 question exam that were "Select All that Apply" type questions.
So I created 5 additional Score Categories (SC1, SC2...SC5) one for each of the 5 questions. I took these 5 questions out of the standard Score (SC0) category by just not assigning a value to those 5 questions.
For each question, I would allocate the amount of points that would total the score for that question both positive and negative. Each question is worth 4 points. So if I had 4 correct answers for that question each correct answer would be +1 point (total +4). If I had 2 incorrect answers on that question each incorrect answer would be worth -2 points (total -4).
When I export the data I have 6 columns for score. SC0, is for all questions that are not "Select All that apply" types, and SC1-SC5 for each of the "Select All that apply" type questions.
In Excel, I want to add up all of these scores, but if you recall I want to have negative scores calculated to 0. So I have a master score sheet that will perform this calculation.
I copy in the exported data file into my master score spread sheet, in the "Data" worksheet. Then on my "Final Score" worksheet I have the calculation below. Column "Data!AS4" is the data from SC0, "Data!AT4" is the data from SC1...on so on.
=SUM(Data!AS4+IF(Data!AT4<0,0,Data!AT4)+IF(Data!AU4<0,0,Data!AU4)+IF(Data!AV4<0,0,Data!AV4)+IF(Data!AW4<0,0,Data!AW4)+IF(Data!AX4<0,0,Data!AX4))
I hope this makes sense and helps someone else out there who may have this problem. If you have a better work around please feel free to share.
+38@Rex I've read through your Excel fix and there's no reason to go to the outside program.
Continue to use your scoring categories as you are SC0...SC5
Instead of using if logic in Excel you can do this in Survey flow.
1. Block - contains all 25 of your questions
2. Set Embedded Data
* Here you will create a variable for each score. You can use SC0...SC5 again
* Define these int he drop down menu by choosing "Insert Piped Text", then "Scoring", and clicking on the corresponding name, and in the last level of the menu "Score"
3. Then four sets of Branch Logic where Embedded Data SC1...SC5 Is Less than 0
4. Indent under the Branch Logic and insert Set Embedded Data, match the variable name (e.g. SC1) = 0
5. Come back out from the indents and add another Set Embedded Data field. Name the variable "Total Score" = $e{ e://Field/SC0 + e://Field/SC1 + e://Field/SC2 + e://Field/SC3 + e://Field/SC4 + e://Field/SC5 }
I've attached pictures of an example where there are four questions. 1 multiple response, three single response. You will notice in my flow I have an extra block at the end, I used this to test the scoring.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.