Replacing value ranges with a category number

[mikenikes]

Hi!

I have a column with 3000+ rows of number data ranging from 0-100+.

In order to adjust this data so it better fits a regression analysis, I need to replace the values with one out of five category numbers.

The categories should look as follows:

value 0 = category 0
1-5 = 1
5-10 = 2
11-30 = 3
31-100 = 4
100+ = 5

I have attempted to solve this using the "Replace"-function, however it appears too limited to accommodate this need.

I am not familiar with VBA and Excel macros, however it looks like it might be the only way to solve this.

Any tips?

Regards,

Mikenikes

 

[VoG]

Try like this

=LOOKUP(A1,{1,5,11,31,100},{1,2,3,4,5})

 

[mikenikes]

This worked splendidly and saved me a lot of time! Many thanks!!!

/Mikenikes