A quick way to place numbers into a banding group?

[TheWennerWoman]

Is there a quick way, either via Excel or via VBA? I have two columns of data, column A contains the numbers 1 - 20000 in numerical order.

What I need in column B is to place them in a band of 50.......so numbers 1 to 50 in column A will have "1 to 50" in column B, numbers 51-100 in column A will have "51 to 100" in column B.

I started off with

=IF(A2<51,"1 to 50",IF(AND(A2>50,A2<101),"51 to 100",IF(AND(A2>100,A2<151),"101 to 150",IF(AND(A2>150,A2<201),"151 to 200",IF(AND(A2>200,A2<251),"201 to 250",IF(AND(A2>250,A2<301),"251 to 300",IF(AND(A2>300,A2<351),"301 to 350",IF(AND(A2>350,A2<401),"351 to 400",IF(AND(A2>400,A2<451),"401 to 450",IF(AND(A2>450,A2<501),"451 to 500",IF(AND(A2>500,A2<551),"501 to 550")))))))))))

but that's so time consuming to write plus I believe Excel has a limit on nested IF statements.

Any help appreciated and seasons greetings to you all.

 

[Phuoc]

Try this:

=FLOOR(A1-1,50)+1&" to "&CEILING(A1,50)

 

[TheWennerWoman]

Try this:

=FLOOR(A1-1,50)+1&" to "&CEILING(A1,50)

That is ridiculously brilliant! Thank you!