Excel 97 if statements

[cgilmore]

In column A I have entered all 52 states with one state in each cell such as ID, WA, MT, LA, OR, CA, etc.
I need a formula which I will enter in the B column which states if the cell to my left is one of 8 states put a 1 if the cell to my left is one of 2 states put a 1.3, if it is "CO" put a 1.08, and if it is any other state put a 1.25. In column B I entered a formula which states
if (a1="ID", 1, 1.25). What I would like this formula to state is if (a1="ID" or "WA" or "MT" or "LA" or "OR" or "CA" or "WY" or "NM", 1, or if a1= "KS" or "MO, 1.3, or if a1="CO", 1.08, 1.25)

 

[euskadi]

Rather than doing ifs, you can do simple switches that use 1.25 as the base and determine if the state qualifies as a state that should deviate from the norm:

=((A1="ID")+(A1="WA")+(A1="MT")+(A1="LA")+(A1="OR")+(A1="CA")+(A1="WY")+(A1="NM"))*(-0.25) + ((A1="KS")+(A1="MO"))*(0.05) + (A1="CO")*(-0.17) + 1.25

Or, you could do a lookup table (as I'd bet someone else is suggesting right now). But I thought I'd offer this as an alternative to IF(IF(IF( which can get crazy after a while.

 

[SkipVought]

Hi,

Here's what I did to simplify the formula...

1. Make a list of the 8 and name the list State1

2. Make a list of the 2 and name it State2

3. Name the list of states, State

4. Here's the formula...

=IF(ISERROR(MATCH(State,State1,0)),IF(ISERROR(MATCH(State,State2,0)),IF(State="CO",1.08,1.25),1.3),1)

VOLA Skip,
metzgsk@voughtaircraft.com

 

[SkipVought]

I made it even more general, readable and easy to change the assigned values...

1. In addition to the above, name CO as State3

2. Add the following Named Ranges for the threshhold values...

S1Val	1
S2Val	1.3
S3Val	1.03
OtherVal	1.25

these values can then be modifyed and the results reflected in the return values immediately.

3. Modify the formula to read...

=IF(ISERROR(MATCH(State,State1,0)),IF(ISERROR(MATCH(State,State2,0)),IF(ISERROR(MATCH(State,State3,0)),OtherVal,S3Val),S2Val),S1Val)

VOLA

Is the apple polished enough yet??? Skip,
metzgsk@voughtaircraft.com