If statement problem

jgold20

Board Regular
Joined
Feb 4, 2018
Messages
135
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)
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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...
 
Upvote 0
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.
 
Upvote 0
"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"
 
Upvote 0
"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?
 
Upvote 0
"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))))
 
Upvote 0
"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))))

Company aaaaabbbbcccccSubtotalMax Discount
CB $ 1.0010% $ 10.00 $ 560.00 $ 28.50
L $ 1.0010% $ 10.00 $ 560.00 $ 28.50
N $ 1.0010% $ 10.00 $ 560.00 $ 28.50
A $ 1.0012% $ 10.00 $ 570.00 $ 28.50

<colgroup><col><col><col><col span="2"><col></colgroup><tbody>
</tbody>
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,920
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top