Categorize Data

[apricot_16]

Hello,
I have a set of data with dollar amounts that I would like to categorize based on size: <$100 is Small, $100<$200 is Medium, >$200 is Large.
Example:
If the amount is $150.25, my new category column would say, Medium.
If the amount is $10.60, it would say, Small.

What formula should I use to get these categories in place? I would also like to have a formula that can work on whole numbers, not just dollar amounts. As I have another column that has whole numbers on it.
Thank you,
Apricot

 

[Somendra Misra]

Hi Apricot,

See this file. Note since you mentioned three categories so I had hard-coded some values in the formula, but if you have large list of categories, we can make a table and give a ref. to the same.

Regards,

 

[Khalid NGO]

Hi Apricot,
Welcome to the Chandoo.org forums; and Happy New Year.

Setting up Lookup Table would be much better option; however, for alternate solution you can use:

In Column B
=IF(A2>200,"Large",IF(A2<100,"Small","Medium"))
Assumed Column A having numbers.

Regards,

 

[apricot_16]

Hi Apricot,
Welcome to the Chandoo.org forums; and Happy New Year.

Setting up Lookup Table would be much better option; however, for alternate solution you can use:

In Column B
=IF(A2>200,"Large",IF(A2<100,"Small","Medium"))
Assumed Column A having numbers.

Regards,

Happy New Year to you also! I only have five categories so for now, I think this formula and Somendra's would work. But if you have an example of a file that uses a Lookup Table--I'd like to get a hold of it, if I need it in the future.

Thank you for the help!

 

[apricot_16]

Hi Apricot,

See this file. Note since you mentioned three categories so I had hard-coded some values in the formula, but if you have large list of categories, we can make a table and give a ref. to the same.

Regards,

Thank you, Somendra! Yes, I actually only have five categories and I think this formula you gave as well as Khalid's would work. Thanks again.