Forum Discussion

Jayqwellen's avatar
Jayqwellen
Copper Contributor
Jul 30, 2021

Reverse scoring

Hi, Is there an automatic way to reverse score? 

I know the equation but it is slow and clumsy when you have multiple columns to reverse score.

Thanks.

Excel on Mac
Formulas and Functions

3 Replies

  • Adrien1's avatar
    Adrien1
    Occasional Reader
    Nov 18, 2025

    Sorry, very late. But fear not, as I am doing an assignment where I need to reverse the scores of a variable. 

     

    Say you have a 7-point Likert scale, in a new column add the max value (7) and the min value (1), and then subtract the original value. You type "= 8 - A2" and drag this formula down.

    1 -> 7

    2 -> 6

    3 -> 5

    4 -> 4

    5 -> 3

    6 -> 2

    7 -> 1

     

    This works for smaller and bigger scales. I am pretty sure some statistical programs have a function that reverses scores, but no clue on that. With this formula you can just transform the data in excel before you plug it somewhere else to conduct tests

  • DAColledge's avatar
    DAColledge
    Copper Contributor
    Jun 25, 2022

    Hi Jayqwellen ,

     

    Sorry I'm a little late to the party but thought I'd chime in since others may encounter a similar problem.

    I managed a quick-fix reverse score on a four-point Likert scale over 10 columns of 5000+ rows by:

     

    • Highlighting all columns containing the data to be reversed
    • Hitting CTRL+H to bring up the REPLACE function
    • Replacing 1 with 10, 2 with 20, 3 with 30, 4 with 40
    • Replacing 40 with 1, 30 with 2, 20 with 3, 10 with 4

    Note I didn't just replace 1 with 4 off the bat since this would have conflated my newly-reversed 4s with my existing 4s, and the same for my newly-reversed 3s. Using 10s, 20s etc provided a quick code to sidestep this problem.

     

    Best,

     

    DC

  • mathetes's avatar
    mathetes
    Silver Contributor
    Aug 01, 2021

    Jayqwellen 

     

    I don't know if others know what you mean by "reverse score"--I googled the term in order to learn, even found a YouTube video with one solution.  https://www.youtube.com/watch?v=_oeP7gdCKLs

    That video contains a solution with nested IF statements that I found a bit clunky, frankly. So IF that's the formula you know, I have a suggestion for a better way...but why don't you help me (and others) help you by giving a more complete description here?

     

    1. Are you working with the five-point scale or something else?
    2. What's the equation/formula that you know and are rejecting because of the volume of your data?

     

    Personally, if you just want a suggestion or two, I think I would avoid the nested IFs in favor of either

    • using VLOOKUP to retrieve the "reverse score" from a table (or one of the other methods of looking up a value and retrieving some other; XLOOKUP, INDEX & MATCH)
    • using CHOOSE, unless you need to allow for 0 or N/A, in which case back to VLOOKUP

     

    In any event, I don't think there's an "automatic" wand you can wave, somehow avoiding a formula or function of some kind.

Resources