Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have to separate my data in category, say for example:

Category    Minimum    Maximum
   A         -100        -20
   B         -20          0
   C           0         +20
   D         +20         +100

So if one data point is -50, it belongs to Category A.

One solution is to use nested IFs:

=IF(A1<-20,"A",IF(A1<0,"B",IF(A1<20,"C","D")))

But when the number of categories increases it becomes quite messy. Is there a better way to achieve the same?

share|improve this question
up vote 1 down vote accepted

Assuming you have the table with categories in Y2:Y10 and the minimums in Z2:Z10 (with 20 not +20 and similar for all positive numbers) then you can use a LOOKUP formula

=LOOKUP(A1,Y$2:Y$10,Z$2:Z$10)

Assumes there are no gaps in the ranges, i.e. the maximum for each category is effectively assumed to be immediately beow the next minimum

share|improve this answer
    
You have inverted the 2 ranges in your formula, it should be =LOOKUP(A1,Z2:Z10,Y2,Y10) - otherwise it works fine, thanks. – assylias Oct 19 '12 at 9:56
    
Yes you are correct, apologies. – barry houdini Oct 19 '12 at 10:37

Another solution could be using named ranges.

I would modify your example to this:

Category    Minimum    Maximum
Cat_A         -100        -20
Cat_B         -20          0
Cat_C           0         +20
Cat_D         +20         +100

Then mark the data from Cat_A to +100, and use Make from selection (On Formula Tab, Section Defined Names) - use make from left column.

The you will get 4 named ranges Cat_A, Cat_B, Cat_C and Cat_D, which you can use in your workbook for i.e. conditional formatting, unsing Min(Cat_A) and Max(Cat_A) as limits.

However, this solution is more an extention to the one of barry houdini.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .