Replace individual numbers with a number range value

[AKM1]

I have a column of individual numbers ranging between 1 and 250.

I need to conform these numbers to specific values. So for example, I need to replace any number between 1 and 10 with the value "1-10", any number between 11 and 20 with "11-20", etc.

Is there any easy way to do this?

[Ace_XL]

In a spearte column use

=IF(MOD(A1,10)=0,A1-9,FLOOR(A1,10)+1)&"-"&CEILING(A1,10)

Copy down.

Copy this column and paste special values onto original data

[Pepe Le Mokko]

Perhaps

Please Login or Register  to view this content.

and pull down as needed

[benishiryo]

@ Pepe:
very nice. but i think the 10th values are all wrong. maybe:
=INT((A1-1)/10)*10+1&"-"&(INT((A1-1)/10)+1)*10

an alternative, AKM1:
=FLOOR(A1-1,10)+1&"-"&CEILING(A1,10)

[AKM1]

Thanks guys. I should have clarified that not all the ranges are increments of 10. Is there a way to specify the ranges?

[AKM1]

Is this possible?

[Pepe Le Mokko]

Could you please specify which these ranges are?

[AKM1]

Here are the ranges:

2-9
10-20
21-50
51-100
101-500
501+

[Ace_XL]

=lookup(a1,{2,10,21,51,101,501},{"2-9","10-20","21-50","51-100","101-500","500+"})

[AKM1]

Thank you, kind sir!