Ranking Excel Values?

[bangers88]

Hi, I have a data set with a column (a) of numbers from 1-100, in another column(b) i would like to give each value a 1-5 rank.

So all numbers in (a) have a rank in (b) using the following order:

0-10 = 1
10-30 = 2
30-60 = 3
60-80 = 4
80-100=5

If anyone knows how this can be done it would be greatly appreciated, as the data set is too large to just type in each cell.

Thanks

[Trebor76]

Hi bangers88,

Welcome to the forum.

This can be done via LOOKUP formula, but your title breaks Rule 1 of the forum rules (see here for more information). Once you re-word your title I can provide a suggested solution (note though that the suggested bands overlap).

Kind regards,

Robert

[bangers88]

Thanks Trebor that would be great, just changed the title then and read the rules.

Sorry with the groupings i meant:

>0 - <10
10 - <30
30 - <60
60 - <80
80 - 100

[Trebor76]

Hi bangers88,

Thanks for that.

Assuming the data starts at A2 (change as required) enter the following formula in the first destination cell and copy down as required:

=LOOKUP(A2,{0,10,30,60,80,101},{1,2,3,4,5})

HTH

Robert

[bangers88]

Thanks trebor76, that has worked and saved me alot of time.

Thanks again.