Newbie excel question

[avenkat]

Working on IF logic . Does if take in range like (200-500) then x
I tried writing this formula
=IF(J2=(50-200), "SMB", IF(J2=(200-500),"MID MARKET", IF(J2=(5000-10000),"SME", "Large Enterprise")))

But it did not give me the required answer which was supposed to be "SMB"
Any pointers?

 

[philwojo]

You would need to use an OR statement, try this:

[COLOR=#333333]=IF(or(J2>=50, J2<=200), "SMB", IF(or(J2>=201,J2<=500),"MID MARKET", IF(or(J2>=5000,J2<=10000),"SME", "Large Enterprise")))[/COLOR]

 

[Special-K99]

avenkat read your post again.

What happens if J2 is between 500 and 5000 ? Or is this a typo?

 

[Special-K99]

Here's another solution

=LOOKUP(J2,{0,50,200,500,5000,10000},{"Unspecified < 50","SMB","Mid Market","SME","Unspecified 500-5000","Large Enterprise"})

This works by defining groups with a minimum value in the first set of brackets {}
The equivalent description is returned from the second set of brackets {} dependent on what group the value falls in.

If J2 is 51 it is in the second group, so the 2nd description "SMB" is returned.
If J2 is 234 it is in the third group, so the 3rd description "Mid Market" is returned.

Although your grouping starts at 50, f the value is less than 50 you will get an area so an extra group with minimum value 0 had to be defined with a description of "Unspecified".
Same with the missing values from 500-5000

 

[avenkat]

Thank you for the formula. It did work for the first row , but there after it took the same row value for all the rows.
So basically it was SMB all the way throuhg. I forgot to mention above I have 4 ranges
50-200 = SMB
200- 500 =SMB2
500-1000 = MM
1000- 5000 = SME
5000- 10000= SME2
>10000 - LE
This is my data range provided as a segments and I need to classify my data into their names.

 

[Special-K99]

=LOOKUP(J2,{0,50,200,500,1000,5000,10000},{"Unspecified < 50","SMB","SMB2","MM","SME","SME2", "LE"})

 

[avenkat]

Thank you Special K99. the unspecified format didnt work as well. SO for now I went ahead and changed my range values to a single number unit like 200 - 500 became 500 and specified variables to it.

If there is another way to do this, happy to try it.

 

[Special-K99]

J2 needs to contain a number like 12, 207, 561, anything thats a number.
With a number in J2

12 returns "Unspecified < 50"
57 returns SMB
214 returns SMB2
637 returns MM
1018 returns SME
5941 returns SME2
11300 returns LE

works fine.