Search code examples
excelexcel-2013

Set text of cell if adjacent cell is between specified values

I am working on a student assessment sheet in Excel 2013. I want set the displayed text of a cell based on the value of another cell

e.g.

If the value of cell H3 is from 0 to 5, cell I3 should display "Beginner"
If the value of cell H3 is from 6 to 10, cell I3 should display "Pre-Intermediate"
If the value of cell H3 is from 11 to 15, cell I3 should display "Intermediate"
If the value of cell H3 is from 16 to 20, cell I3 should display "Advanced"

What is the best way to accomplish this? Can anyone suggest a formula I can use in Conditional Formatting to achieve this result?

Thank you

Solution

  • In cell I3 put the formula (shortened the text for easy reading):

    Solution 1:

    =IF( H3<=5, "Beg", IF( H3<=10, "Pre", IF( H3<=15, "Int", "Adv" ) ) )

    note: negative will be "Beginner", those over 15 will be "Advanced"

    Solution 2:

    =CHOOSE( RoundUp( H3/5, 0 ) + 1, "Beg", "Beg", "Pre", "Int", "Adv"))

    note: values outside of 0-20 will be displayed as error, the first "Beg" is for the 0 value, 2nd "Beg" is for 1-5