Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have over 1,300 pieces (2 benchmarks, 110 students, 6 categories of scoring) of individual data in excel that I would like to change as follows for the purpose of converting from a 6 point standards based report card to a 4 point.

6 NOW = A 4

5 NOW = A 4

4 NOW = A 3

3 NOW = A 2

2 NOW = A 1

1 NOW = A 1

Is there an inclusive formula that I can use that would contain all of the above conversions that I could use to change all of my data at one time?

share|improve this question

If I understand, you have options for this, the best may be to create a lookup table (I’d suggest named ‘map’) somewhere in the same book – in example is on same page in range F1:G6. Then, assuming the data to be converted is in a column starting at A1:


(FALSE is required to ensure the match is exact rather than nearest available value.)

in B1 and copy down as required. (If previous values are no longer required, Copy ColumnB Paste Special/Values over the top and Column A can then be deleted).

Alternative with formula only, just using IF, also in B1 and copied down:


(assumes values will always exist – not 0 for example. results shown in ColumnD of example)

SU538671 example

Another possibility might be to retain both scales but accept the reduced range one is indicated by colour (with a 4-icon set in Conditional Formatting):

SU538679 second example

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.