How to Write a Case Statement in Excel

A Case Statement in Excel

In Excel, there isn't a direct equivalent to the Case statement as seen in other programming languages. However, you can achieve a similar result using the IF or CHOOSE functions, or by creating a nested IF statement.

Using Nested IF Statements (Example)

Let's consider an example where we want to categorize people based on their age:

1. Teen (13-19)
2. Adult (20-59)
3. Senior (60 and above)

Assuming the age of the person is in cell A1, you can create a nested IF statement in cell B1 as follows:

=IF(A1>=13, IF(A1<=19, "Teen", IF(A1>=20, IF(A1<=59, "Adult", IF(A1>=60, "Senior", "")))), "")

This formula checks the age value in cell A1 and assigns the appropriate category in cell B1.

Using CHOOSE Function (Example)

Another way to create a case statement in Excel is by using the CHOOSE function. Let's consider an example where we want to assign a rating based on the score:

1. Poor (1-2)
2. Average (3-4)
3. Good (5-6)
4. Excellent (7-8)
5. Outstanding (9-10)

Assuming the score is in cell A1, you can create a CHOOSE function in cell B1 as follows:

=CHOOSE(INT((A1-1)/2), "Poor", "Average", "Good", "Excellent", "Outstanding")

This formula checks the score value in cell A1 and assigns the appropriate rating in cell B1. Note that this example assumes that the score is an integer between 1 and 10.

Remember that these examples can be adapted to various scenarios by modifying the conditions, values, and output text.