How to split a list of numbers into 0-10, 11-20, 21-30 etc.

[thisisbaris]

Hi folks,
　
I have a long list (14,000 rows) of numbers (positive and negative) that I am trying to categorise into absolute value categories of 0 to 10, 11 to 20, 21 to 30 and so on ... So for example, 13 would return "11 to 20" and -4 would return "0 to 10".
　
These are my formula:
=IF(OR(AND(D3>-1,D3<11),(AND(D3<0,D3>-11))),"0 to 10","")
=IF(OR(AND(D3>10,D3<21),(AND(D3<-10,D3>-21))),"11 to 20","")
=IF(OR(AND(D3>20,D3<31),(AND(D3<-20,D3>-31))),"21 to 30","")
　
I have 10 columns of formula like this from "0 to 10" to "91-100". Only one of these 10 columns will be correct, so I have a =CONCATENATE column as the 11th column that returns the one true category.
　
It seems to be working perfectly for the majority of items. However, for those values that are between 30 and 31 such as -30.07 and -30.85, they are being categorised as both "21 to 30" and "31 to 40" - which I don't understand.
　
Are my formulas wrong? What is a better way of doing this?
　
Many thanks in advance!

 

[Joe4]

Try this instead:

=IF(AND(ABS(D3)>=0,ABS(D3)<=10),"0 to 10","")

etc.

 

[Fluff]

How about
=LOOKUP(ABS(D3),{0,11,21,31,41},{"0 to 10","11 to 20","21 to 30","31 to 40","41 to 50"})