Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
add comment

1 Answer 1

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:

=VLOOKUP(A1,map,2,FALSE)

(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:

=IF(OR(A1=6,A1=5),4,IF(A1=4,3,IF(A1=3,2,1)))

(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
add comment

Your Answer

 
discard

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.