Help! IF functions

[Anna57]

Hello,

I have a list of ages (all to 2 decimal places) and i am trying to use an if
function to classify them into groups. Next to the list of ages i would like
another column indicating which group the individual falls in. Group
membership should follow the rules below:

age < 8 = group 1
age 9-11 = group 2
age 12-14 = group3
age 15+ = group 4

The formula i have put in is pasted below but it doesn't work, does anyone
know what might be wrong with it? I have used decimal numbers (e.g.11.99) for
the end of ranges as otherwise some ages will fall into more than one group:

=IF(A1=<8, "1", IF(A1=9:11.99, "2", IF(A1=12:14.99, "3", IF(A1=>15, "4",
"0"))))

Any ideas? I have been tring for ages to see what's wrong but can't come up
with anything!

 

[John C]

=LOOKUP(A1,{0,"GROUP 1";9,"GROUP 2";12,"GROUP 3";15,"GROUP 4"})

 

[Mike H]

Hi,

Try this

=IF(A1<=8,1,IF(A1<=11,2,IF(A1<=14,3,4)))

Mike.

 

[Gord Dibben]

You have left out some ranges.

What about 8 to 8.99?

Try this lookup formula. Adjust to suit.

=LOOKUP(B1,{0,8,12,15,15.01},{1,2,3,4,4})


Gord Dibben MS Excel MVP