Need If-Then statement formula using multiple variables.

I have the following range of information available:

0% through 10% = 0.0% standard rate
11% through 14% = 2.0% standard rate
15% through 18% = 2.5% standard rate
19%+ = 3.0% standard rate

If an acocunt is designated as type "N", then the rate applied is whatever the standard rate is plus 1%.

If an account is designated as type "D1", then the rate applied is whatever the standard rate is multiplied by .75

If an account is designated as type "D2", then the rate applied is whatever the standard rate is multiplied by .5

-------------------------------------------------------------------------
I have set up sheet1 as follows:

Column A, Row 1 = 0 (beginning of range)
Column B, Row 1 = .1 (end of range)
Column C, Row 1 = 0 (standard rate applied)
Column A, Row 2 = .11 (begin of range)
Column B, Row 2 = .14 (end of range)
Column C, Row 2 = .02 (standard rate)
Column A, Row 3 = .15 (begin of range)
Column B, Row 3 = .18 (end of range)
Column C, Row 3 = .025 (standard rate)
Column A, Row 4 = .19 (begin of range)
Column B, Row 4 = 1.00 (end of range)
Column C, Row 4 = .03 (standard rate)

Column E, Row 1 = D2
Column E, Row 2 = .18225 (an example of a value within a range)
Column E, Row 3 = (If, then statement)

My hope is that when the if-then statement is implemented, the result using the example given would be .0125 (.025 standard rate multiplied by .5). If I change Column E, Row 1 to 'N', then I would expect the result to be .035 (.025 standard rate plus .01). If I then change Column E, Row 2 to .21285, I would expect the result to be .04 (.03 standard rate plus .01).

Can anyone help me solve this?

Thanks,

MidCon

Oops. It looks I posted in the wrong forum. I'll try what you advised. Thanks.

Originally Posted by NBVC

Try

=Lookup(E2,{0,.11,.15,.19},{0,.02,.025,.03})*Lookup(E1,{"D1","D2","N"},{.01,.75,.5})

That doesn't quite work. My boss would like to utilize something that he can easily change the variables. The lookup you gave accomplishes that with the "D1" "D2" and "N". However it does not accomplish that with the percentages of 0, .02, .025, & .03 because they are buried within the lookup statement and not visible on the spreadsheet itself.

Thanks!