If statement problem

[jgold20]

I need to test multiple conditions for a selected cell. The first if statement is doing the proper calculation. The next two are adding up instead of just displaying the value in the cell. Any suggestions? If statements are below:

=IF(OR(E6="A",E6="B",E6="CB",E6="K",E6="L",E6="M",E6="MD",E6="N",E6="Q",E6="R",E6="V",E6="W",E6="Y"),(I9*0.05),0) +
IF(OR(F2<299,E6="D"),"50.00",0) + IF(OR(F2>299<499,E6="D"),"100.00",0)

 

[steve the fish]

Hi. If E6 is 'D' what is supposed to happen?

 

[FryGirl]

Is it possible you meant for some of the ORs to be ANDs?

=IF(OR(E6={"A","B","CB","K","L","M","MD","N","Q","R","V","W","Y"}),(I9*0.05),IF(AND(F2<299,E6="D"),50,IF(AND(F2>299,F2<499,E6="D"),100,0)))

Maybe you can present some results you expect...

 

[jgold20]

Hi. If E6 is 'D' what is supposed to happen?

"depending on the value of f2, j6 is supposed to have 50.00 or 100.00 displayed"

 

[S_W_Langdon]

You need an OR(AND( within the last section of the formula otherwise it is seeing both OR( options and add them together

Also if Cell E6 = D it will return 150 as it is seeing both and adding them together, only one of these can have the option D within

Hope this helps

=IF(OR(E6="A",E6="B",E6="CB",E6="K",E6="L",E6="M",E6="MD",E6="N",E6="Q",E6="R",E6="V",E6="W",E6="Y"),(I9*0.05),0) +
IF(OR(F2<299,E6="D"),"50.00",0)+IF(OR(AND(F2>299,F2<499),E6="D"),"100.00",0)

p.s. just joined today so still learning the forum, so not been able to tag OP in this.

 

[jgold20]

"This was my first post & I should have explained a little better what I am try to do:

J2 = Numeric Value
E6 - E9 = Alpha value that is always unique. It is changed based upon a drop down box & Vlookup.
J6 - J9 - Numeric value

E6 - E9 - a, b, cb, k, l, m, md, n, q, r, w, y will always be multiplied by . 05
E6 - E9 - d, h, t, v, d will have a fixed numeric value based on <, =, >

I hope this is a better explanation of what I am trying to do"

 

[JonXL]

"This was my first post & I should have explained a little better what I am try to do:

J2 = Numeric Value
E6 - E9 = Alpha value that is always unique. It is changed based upon a drop down box & Vlookup.
J6 - J9 - Numeric value

E6 - E9 - a, b, cb, k, l, m, md, n, q, r, w, y will always be multiplied by . 05
E6 - E9 - d, h, t, v, d will have a fixed numeric value based on <, =, >

I hope this is a better explanation of what I am trying to do"

Does any combination of inputs return the expected results?

Which inputs are returning bad results?

 

[jgold20]

"almost there, everything is working except for - I have another criteria that I have to handle - if E6 - E9 is "WG" or "MD, I have to multiple by .03. This is showing no value -

=IF(OR(E7="A",E7="B",E7="CB",E7="K",E7="L",E7="M",E7="N",E7="Q",E7="R",E7="W",E7="Y"),(I9*0.05),0) +
IF(AND(E7="MD"),IF(AND(E7="WG"),(I9*0.03),0)) +
IF(AND(F2<401,E7="D"),50,IF(AND(F2>400,F2<501,E7="D"),100,IF(AND(F2>500,F2<1001,E7="D"),150,IF(AND(F2>1000,E7="D"),300,0))))+
IF(AND(F2<401,E7="H"),50,IF(AND(F2>400,F2<501,E7="H"),100,IF(AND(F2>500,F2<1001,E7="H"),150,IF(AND(F2>1000,E7="H"),300,0)))) +
IF(AND(F2<401,E7="V"),50,IF(AND(F2>400,F2<501,E7="V"),100,IF(AND(F2>500,F2<1001,E7="V"),150,IF(AND(F2>1000,E7="V"),300,0)))) +
IF(AND(F2<401,E7="T"),50,IF(AND(F2>400,F2<501,E7="T"),100,IF(AND(F2>500,F2<1001,E7="T"),150,IF(AND(F2>1000,E7="T"),300,0)))) +
IF(AND(F2<401,E7="X"),50,IF(AND(F2>400,F2<501,E7="X"),100,IF(AND(F2>500,F2<1001,E7="X"),150,IF(AND(F2>1000,E7="X"),300,0))))

 

[Saxus]

why not tabulate the conditions and then use function VLOOKUP?

 

[jgold20]

"Due to contraints, a vlookup will not work
Last problem -whenever the subtotal is different when I am multiplying by 0.05 ( in my first if statement),the calculation is wrong for max discount, EXCEPT WHEN MY SECOND IF STATEMENT IS CALCULATED THE MAX DISCOUNT IS CORRECT

=IF(OR(E9="A",E9="B",E9="CB",E9="K",E9="L",E9="M",E9="N",E9="Q",E9="R",E9="W",E9="Y"),(I9*0.05),0) +
IF(OR(E9="MD",E9="WG"),(I9*0.03),0) +
IF(AND(F2<401,E9="D"),50,IF(AND(F2>400,F5<501,E9="D"),100,IF(AND(F2>500,F5<1001,E9="D"),150,IF(AND(F2>1000,E9="D"),300,0))))+
IF(AND(F2<401,E9="H"),50,IF(AND(F2>400,F5<501,E9="H"),100,IF(AND(F2>500,F5<1001,E9="H"),150,IF(AND(F2>1000,E9="H"),300,0)))) +
IF(AND(F2<401,E9="V"),50,IF(AND(F2>400,F5<501,E9="V"),100,IF(AND(F2>500,F5<1001,E9="V"),150,IF(AND(F2>1000,E9="V"),300,0)))) +
IF(AND(F2<401,E9="T"),50,IF(AND(F2>400,F5<501,E9="T"),100,IF(AND(F2>500,F5<1001,E9="T"),150,IF(AND(F2>1000,E9="T"),300,0)))) +
IF(AND(F2<401,E9="X"),50,IF(AND(F2>400,F5<501,E9="X"),100,IF(AND(F2>500,F5<1001,E9="X"),150,IF(AND(F2>1000,E9="X"),300,0))))

[TABLE="width: 763"]
<colgroup><col><col><col><col span="2"><col></colgroup><tbody>[TR]
[TD]Company [/TD]
[TD]aaaaa[/TD]
[TD]bbbb[/TD]
[TD]ccccc[/TD]
[TD]Subtotal[/TD]
[TD]Max Discount[/TD]
[/TR]
[TR]
[TD]CB[/TD]
[TD] $ 1.00[/TD]
[TD]10%[/TD]
[TD] $ 10.00[/TD]
[TD] $ 560.00[/TD]
[TD] $ 28.50[/TD]
[/TR]
[TR]
[TD]L[/TD]
[TD] $ 1.00[/TD]
[TD]10%[/TD]
[TD] $ 10.00[/TD]
[TD] $ 560.00[/TD]
[TD] $ 28.50[/TD]
[/TR]
[TR]
[TD]N[/TD]
[TD] $ 1.00[/TD]
[TD]10%[/TD]
[TD] $ 10.00[/TD]
[TD] $ 560.00[/TD]
[TD] $ 28.50[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD] $ 1.00[/TD]
[TD]12%[/TD]
[TD] $ 10.00[/TD]
[TD] $ 570.00[/TD]
[TD] $ 28.50[/TD]
[/TR]
</tbody>[/TABLE]