For better analysis in many surveys and questionnaires, a reverse score is used. Generally, in the reverse score, we transform 1 = 5, 2 = 4 and so on. Doing this in Excel makes it easier for analysis. In this article, we will show you how to reverse score in Excel.
Download Practice Workbook
You can download the free Excel workbook here and practice on your own.
4 Easy Ways to Reverse Score in Excel
We will illustrate four different ways to reverse score in Excel. Firstly, we will subtract the original score, then use the IF function in the second one, insert the LOOKUP function in our third approach, and finally, we will utilize the CHOOSE function to reverse the score in Excel. By using these methods, we will convert an original score into its reverse score. For example, if our original score is 1, then we will make it 5 in the reverse score. If the original score is 2, then it will become 4 as the reverse score and so on. The above-mentioned functions of Excel will help in this procedure. For our working purposes, we will use the following data set.
1. Subtracting Original Score to Reverse Score
In our first method, we will subtract the original score to make a reverse score in Excel. By subtracting the original value you will get our desired result .The detailed steps for this procedure are given below.
Step 1:
- Firstly, we will find the highest value from the original score.
- In our example, the highest value is 5.
- Then, we will add 1 with the highest value for subtraction.
- After adding, the value will be 5+1 = 6.
Step 2:
- Secondly, type the following formula in cell C5.
=6-B5
- Here, we will subtract each original score from 6 to get the reverse score.
Step 3:
- Thirdly, press Enter to see the reverse score for 1 which will be 5.
- Consequently, use AutoFill to find the reverse scores for the following original scores.
Read More: How to Create a Scoring System in Excel (With Easy Steps)
2. Using IF Function to Reverse Score in Excel
You can also find the reverse score by using the IF function in Excel. We will use the IF function to meet some certain condition. The function will show result based on the condition. If the condition is met then it will show the desired result. To learn more about the procedure, see the following steps.
Step 1:
- In the beginning, type the following formula of the IF function in cell C5.
=IF(B5=1,5,IF(B5=2,4,IF(B5=3,3,IF(B5=4,2,IF(B5,1)))))
Step 2:
- In the second step, to see the reverse score, press Enter.
- Finally, use the AutoFill feature of Excel to get the reverse scores in all the following cells.
Similar Readings
- How to Make Cricket Scorecard in Excel (With Easy Steps)
- How to Calculate T Score in Excel (4 Easy Ways)
3. Inserting LOOKUP Function to Reverse Score in Excel
We will insert the LOOKUP function as our third method. We will use the LOOKUP function to lookup a certain value in a cell. After finding the value, it will give output as per the function formula. Go through the following steps for a better understanding.
Step 1:
- First of all, choose the cell where you want to see the output.
- In our data set, it is cell C5.
- Then, write the following formula of the LOOKUP function in the cell.
=LOOKUP(B5,{1,2,3,4,5},{5,4,3,2,1})
Step 2:
- Secondly, we will get the output after writing the formula.
- To do that, hit the Enter button on the keyboard.
- Finally, to get the reverse score for the lower cells of the data set use AutoFill tool.
Read More: How to Create Scorecard in Excel (with Detailed Steps)
4. Utilizing CHOOSE Function
Lastly, to get reverse score for any original score, we will utilize the CHOOSE function of Excel. The CHOOSE function will help to show the desired result based on the cell value. The function is easier to use because it does not need any additional data table to show the result. The steps of this procedure are as follows.
Step 1:
- Firstly, in cell C5 of the data set type the following formula of the CHOOSE function.
=CHOOSE(B5,5,4,3,2,1)
Step 2:
- Secondly, to get the desired result, press Enter.
- Then, with the help of the AutoFill feature, find out the reverse scores for all the other lower cells.
Conclusion
That’s the end of this article. I hope you find this article helpful. After reading the above description, you will be able to reverse score in Excel by using any of the above-described methods. Please share any further queries or recommendations with us in the comments section below. The Exceldemy team is always concerned about your preferences.