CODEWITHSUNDEEP
excelexcel-formulavlookupworksheet-functionmultiple-conditions
Erba Aitbayev
Erba Aitbayev

Reputation: 4353

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.

Upvotes: 3

Views: 36688

Answers (3)

Slai
Slai

Reputation: 22886

=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)

Upvotes: 3

danielsto
danielsto

Reputation: 134

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‌​")

Upvotes: 1

Werrf
Werrf

Reputation: 1148

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.

Upvotes: 5

Related Questions