Log in

View Full Version : Several Criteria Classification on Excel


Amyunimus
Jun 23, 2006, 02:49 AM
Quick question...

I'd like to do the following:

If the value in Column A is between 1-50 or 200-250 or 400-450, or 600-650 etc etc I want it to be classified as "people" in Column B. If it does not fall in those number ranges, I want it to be classified as "monkey" in Column B.

This seems straight forward enough to me, but I can't figure out how to use the IF function (or anything else... ) to get it to work.

Any help would be greatly appreciated.

Emily

ScottGem
Jun 23, 2006, 05:59 AM
Probably best to use a VLookup. Create a table like:

1... People
51... Monkey
200... People
251... Monkey

etc.

Then use a VLookup in Column B

colbtech
Jun 23, 2006, 08:24 AM
A1 = value, doesn't matter how many digits
B1 = convert this value to text, right most 2 digits
C1 = convert B1 back to a number
D1 = Your answer

A1 = 1021
B1 = 21 =Right(A1,2)
C1 = 21 =Value(B1)
D1 = People =If(C1>50.01,"Monkey","People")

Hope this helps

Colin

ScottGem
Jun 23, 2006, 08:48 AM
I see what Colin is doing, but he's making an assumption that may not be valid. For example is the value is 160, his solution will still return Monkey. Using a lookup table allows you to fine tune the ranges.

colbtech
Jun 26, 2006, 12:36 AM
Hi Scott,

Is that not what is required?

Col

ScottGem
Jun 26, 2006, 06:09 AM
Hi Scott,

Is that not what is required?

Col

Not from my read. Look at the ranges. Its not consistently distributed.

colbtech
Jun 26, 2006, 07:26 AM
Got it now. That will teach me to read the question a little closer!

ScottGem
Jun 26, 2006, 07:56 AM
Got it now. That will teach me to read the question a little closer!

Your solution was a neat idea, if the pattern was more consistent.