Nested "IF" statement
 

Is there a way to combine these into one IF statement? I need to reference
the state and the date to determine the percentage to apply to the
calculation. For example, if the state is GA and the date is between
20030501 and 20040430 apply 4%, if the state is AL and the date is between
20050501 and 20060430, apply 3.5% etc.

These are the formulas and states (I6 is the date being referenced)

AL =IF(I6=20070501,0.006,IF(I6=20060501<=20070430,0 .005,IF(I6=20040501<=20060430,0.007,IF(I6=200205 01<=20040430,0.009,0))))
DC =IF(I6=20070501,0.111,0)
GA =IF(I6=20060501<=20070430,0.088,
IF(I6=20040501<20060430,0.116,IF(I6=20020501<=20 040430,0.081,0)))
ID =IF(I6=20040501<=20060430,0.033,IF(I6=20060501,0 .031,0))
KS =IF(I6=20070501,0.034,IF(I6=20060501<=20070430,0 .035,IF(I6=20040501<=20060430,0.032,IF(I6=200205 01<=20040430,0.04,0.094))))
MN =IF(I6=20010501<=20020430,0.162,IF(I6=20020501<= 20030430,0.107,0))
SC =IF(I6=20070501,0.245,IF(I6=20060501<=20070430,0 .194,IF(I6=20040501<=20060430,0.23,IF(I6=2002050 1<=20040430,0.158,0.145))))
WI =IF(I6=20070501,0.018,0)

Nested "IF" statement
 

Excel has a limit of 7 nested IF statements. I would suggest using a
custom formula like this:

Public Function CalcTax(st As String, amt As Long) As Double
Application.Volatile
Select Case st
Case "AL"
tax = 0.035
Case "GA"
tax = 0.04
End Select
CalcTax = Round(amt * tax, 2)
End Function

--
Dan


On Apr 14, 11:18*am, Cindy - Atlanta,GA <Cindy -
wrote:
Is there a way to combine these into one IF statement? *I need to reference
the state and the date to determine the percentage to apply to the
calculation. *For example, if the state is GA and the date is between
20030501 and 20040430 apply 4%, if the state is AL and the date is between
20050501 and 20060430, apply 3.5% etc.

These are the formulas and states (I6 is the date being referenced)

AL * * *=IF(I6=20070501,0.006,IF(I6=20060501<=20070430, 0.005,IF(I6=20040501<=200*60430,0.007,IF(I6=2002 0501<=20040430,0.009,0))))
DC * * *=IF(I6=20070501,0.111,0)
GA * * *=IF(I6=20060501<=20070430,0.088,
IF(I6=20040501<20060430,0.116,IF(I6=20020501<=20 040430,0.081,0)))
ID * * *=IF(I6=20040501<=20060430,0.033,IF(I6=20060501, 0.031,0))
KS * * *=IF(I6=20070501,0.034,IF(I6=20060501<=20070430, 0.035,IF(I6=20040501<=200*60430,0.032,IF(I6=2002 0501<=20040430,0.04,0.094))))
MN * * *=IF(I6=20010501<=20020430,0.162,IF(I6=20020501< =20030430,0.107,0))
SC * * *=IF(I6=20070501,0.245,IF(I6=20060501<=20070430, 0.194,IF(I6=20040501<=200*60430,0.23,IF(I6=20020 501<=20040430,0.158,0.145))))
WI * * *=IF(I6=20070501,0.018,0)

Nested "IF" statement
 

Why don't you try using a Pivot Table. I've used Pivot Tables many times in
the past to overcome the 7-limit (limitation) of if functions.
http://peltiertech.com/Excel/Pivots/pivottables.htm
http://www.contextures.com/xlPivot02.html

Regards,
Ryan--



--
RyGuy


"Dan R." wrote:

Excel has a limit of 7 nested IF statements. I would suggest using a
custom formula like this:

Public Function CalcTax(st As String, amt As Long) As Double
Application.Volatile
Select Case st
Case "AL"
tax = 0.035
Case "GA"
tax = 0.04
End Select
CalcTax = Round(amt * tax, 2)
End Function

--
Dan


On Apr 14, 11:18 am, Cindy - Atlanta,GA <Cindy -
wrote:
Is there a way to combine these into one IF statement? I need to reference
the state and the date to determine the percentage to apply to the
calculation. For example, if the state is GA and the date is between
20030501 and 20040430 apply 4%, if the state is AL and the date is between
20050501 and 20060430, apply 3.5% etc.

These are the formulas and states (I6 is the date being referenced)

AL =IF(I6=20070501,0.006,IF(I6=20060501<=20070430,0 .005,IF(I6=20040501<=200Â*60430,0.007,IF(I6=2002 0501<=20040430,0.009,0))))
DC =IF(I6=20070501,0.111,0)
GA =IF(I6=20060501<=20070430,0.088,
IF(I6=20040501<20060430,0.116,IF(I6=20020501<=20 040430,0.081,0)))
ID =IF(I6=20040501<=20060430,0.033,IF(I6=20060501,0 .031,0))
KS =IF(I6=20070501,0.034,IF(I6=20060501<=20070430,0 .035,IF(I6=20040501<=200Â*60430,0.032,IF(I6=2002 0501<=20040430,0.04,0.094))))
MN =IF(I6=20010501<=20020430,0.162,IF(I6=20020501<= 20030430,0.107,0))
SC =IF(I6=20070501,0.245,IF(I6=20060501<=20070430,0 .194,IF(I6=20040501<=200Â*60430,0.23,IF(I6=20020 501<=20040430,0.158,0.145))))
WI =IF(I6=20070501,0.018,0)