Search code examples
excelexcel-formulavlookupworksheet-functionmultiple-conditions

How to categorize/classify numbers from different ranges using Excel?

I want to categorize numbers from [0:15] into 5 classes: A, B, C, D, E, F

Rules:

0-1: Class A
2-4: Class B
5-7: Class C
8-10: Class D
11-13: Class E
14-15: Class F

Explanation: if number is in range [0:1] then it will be classified as A; if a number is in [2:4] class is B, and so on.

Sample output:

enter image description here

I know it can be done with multiple IFs, but I have trouble figuring out what a formula will look like, epsecially distinguishing different number ranges.

Solution

  • I wouldn't suggest using nested IFs - I'm generally opposed to that method on moral grounds anyway, but it's also difficult to maintain. I'd suggest using either a VLOOKUP or an INDEX(MATCH structure.

    We'll take your example of the rules and modify it slightly:

    Min |  Class
--------------
0   |  Class A
2   |  Class B
5   |  Class C
8   |  Class D
11  |  Class E
14  |  Class F

    Now, assuming your Max values are in sheet 2, column A, we'll use the following formula in the Class column of your output:

    =VLOOKUP($A2,Sheet2!$A$1:$B$7,2,TRUE)

    This has the advantage of being much easier to maintain and clearer to read.