3

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.

Erba Aitbayev
  • 4,167
  • 12
  • 46
  • 81
  • 1
    Use VLOOKUP. create a table with the minimums in one column and the class in another. Then use VLOOKUP with the forth criterion as true. – Scott Craner Oct 31 '16 at 19:24

3 Answers3

5

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.

Werrf
  • 1,138
  • 6
  • 14
3
=MID("AABBBCCCDDDEEEFF",A1+1,1)

or

=LOOKUP(A1,{0,2,5,8,11,14,16;"A","B","C","D","E","F",""})

or this hacky version

=CHAR((A1+1)/3+65)
Slai
  • 22,144
  • 5
  • 45
  • 53
1

It's a long IFS with several similar conditions, however here it is:

=IFS(A2<2,"A",A2<5,"B",A2<8,"C",A2<11,"D",A2<14,"E",A2<16,"F‌​")
danielsto
  • 134
  • 5
  • 17
  • 1
    You do not need the AND()s. Because of how IF resolves it needs to only be `=IF(A2<2,"A",IF(A2<5,"B",IF(A2<8,"C",IF(A2<11,"D",IF(A2<11,"E","F")))))` – Scott Craner Oct 31 '16 at 19:58
  • 1
    Also a comment on the fact that `IFS` is only available in Office 265 Excel and not earlier versions. – Scott Craner Oct 31 '16 at 20:01
  • 1
    The IFS version that is shorter without the AND(), `=IFS(A2<2,"A",A2<5,"B",A2<8,"C",A2<11,"D",A2<14,"E",A2<16,"F")` – Scott Craner Oct 31 '16 at 20:04