Multiple IF functions in Excel

[Ben1637]

Hi Everybody:

I am trying to do a frequency distribution using the [IF , THEN] function. Maybe an example will help - I have a spreadsheet in Excel with health plans in the US and one of the columns has all the states that the health plans have it's services in. We need to put these states under particular regions, each region has a number: 1, 2 , 3...

What I am trying to do is, use the 'if' 'then' function to allot the states to those regions, instead of doing it manually which will take forever. This is what I tried doing: =(IF(Y2="MA",1))....but that just returns FALSE. Essentially, I need to get the above formula working...then I need to add additional nested IF statements to include if the cell says FL, return a 4, it it says IN, return a 5 and so one. Is this possible?

Any help you can provide would be greatly appreciated.

Many thanks, in advance!!!

 

[Zathras]

First of all, as an "Instructor" you should be aware that there is a limit of how many IF statements can be nested.

Your best bet is to create a short table of states and regions in two columns and use VLOOKUP.

 

[Ben1637]

I did in fact try that prior to posting, but I still can't get it to work. I have the table set-up like this:
CT 1
MA 1
ME 1
NH 1
RI 1
VT 1
NJ 2
NY 2
DC 3
DE 3
MD 3
PA 3
VA 3
WV 3
AL 4
AR 4
FL 4
GA 4
KY 4
MS 4
NC 4
TN 4
IL 5
IN 5
MI 5
MN 5

etc....this table lives within the same workbook, but on a different worksheet. I'm just not that familiar with VLOOKUPS so any help/guidance would be appreciated...

PS....I am primarily an HTML instructor so I am less familar with Excel....sorry

 

[sawedoff]

If you only have 3 regions, the following abbreviated formula will work. Expand the list of regions within the formula as needed. If you have more, add more or statements (up to the limit of 7 nested functions).

=IF(OR(A1="IN",A1="MI",A1="IL",A1="OH"),1,IF(OR(A1="FL",A1="GA"),2,3))

Sawedoff

 

[monagan]

Lets say you have two worksheets.
StateTable and MainSheet

MainSheet is where you're trying to put this function.

Say your state is in cell A1

=VLOOKUP(A1,StateTable!$a$1:$a$50,2,0)

This looks up the state in a1 in the State Table range a1 to a50, and returns the value in column b. (Thats what the 2 is for)

 

[Ben1637]

Thank you, Sawedoff....that is exactly what I was looking for. Unfortunately, however, there are 10 regions....

 

[sawedoff]

Then you would be beter off using the vlookup. Use =VLOOKUP(A1,Statesheet!A1:B50,2,FALSE) or whatever you name your sheet that has the states/regions. I always try to have the table sorted by the lookup value, but with the "FALSE" it should not be necessary.

Sawedoff

 

[Ben1637]

=VLOOKUP(A1,Statesheet!$A$1:$B$50)worked...woohoo!! Thank you SO MUCH for your help.