# Changing/Converting previously entered data that was entrered using 6 point scale to a 4 point scale

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?

-

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)

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

-