How to get numbers in a column to be classified in a range using formula

[vijitkumar]

How to get numbers in a column to be classified in a range using formula

In column "A" I have numbers.
In column "B" and "C" I have specified the desired outputs.

Please let me know how to achieve this.

I tried with IF condition using AND but it is not working and throwing error.

 

[Luke M]

Formula for col B:
=ROUNDDOWN(A2,-2)+1&" - "&ROUNDUP(A2,-2)

For col C, how do we know which range is oldest/older/old? Is it always the 501-600 group that is oldest, or is it because it's the largest in value?

 

[Somendra Misra]

Just another approach for column B. See the file. For column C I am also confused.

Regards,

 

[vijitkumar]

It is either column B or column C that I needed the output as.

IN column c, I wanted to get an output with a custom text that specify

For eg: if the number is between 501 and 600 in a cell in column A, the output should be "oldest" or for that matter any text I specify.

 

[Luke M]

In that case, I'd setup a lookup table (say in range D1:E5) with 1 column being the ranges, and the other being desired output, like:
101-200
201-300
301-400 Old
401-500 Older
501-600 Oldest

Then, formula in col C is:
=LOOKUP(B2,$D$1:$E$5)