group values based on a range using IF function

[phdman]

I have a data set made up of number values (thousands). I want to use a formula in excel to determine if each value falls in a particular range. The ranges are based on value, being: range 1: <1000, 2: 1000>=but<1500, 3: 1050>=but<3000, 4: 3000>=but<5000, 5: >=5000.

Can I use the IF function to do this in one formula?

 

[Kevin@Radstock]

Hi

You could use a IF function.
For Example:
=IF(A1<1000,"Range <1000",IF(A1<1500,"Range >=1000 & <1500",IF(A1<3000,"Range >=1500 & <3000",IF(A1<5000,"Range >3000 & <5000","Range >5000"))))

Or better still, a VLOOKUP: =VLOOKUP(A1,B1:C5,2,1)

Lookup table: Col A in a ascending order

Col B Col C
0 Range <1000
1000 Range >=1000 & <1500
1500 Range >=1500 & <3000
3000 Range >=3000 & <5000
5000 Range >5000