Convert a range of numbers to a letter in Excel

问题:

I have to define a range numbers to a rating and the rating is just one letter and based on the range of numbers the next cell should show the letter.

Example:
10231 = A
8034 = B
6433 = C
4542 = D
1323 = F

• Anything over 10000 Gets converted to letter A
• anything between 8000 and 9999 converted to letter B
• anything between 6000 and 7999 converted to letter C
• anything between 4000 and 5999 converted to letter D
• anything between 0 and 3999 converted to letter F

If I enter number in A1 cell as 3214, in B1 cell I should get F Can we do this with simple formula? Or does this require VB?

回答1:

Try this formula in B1:

=VLOOKUP(A1,{0,"F";4000,"D";6000,"C";8000,"B";10000,"A"},2,TRUE)

回答2:

Played a little to see if I could shorten it using the repetitive patterns.

The VLOOKUP is easier to understand

=CHOOSE(MAX(INT(A1/2000),1),"F","D","C","B","A")