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