Forum Discussion
Please HELP multiple Nested IF/OR Statements
I am trying to create a formula looking to see if a volume or a unit is hit then multiply it for that tier. I added in the OR function to my previous formula since we are now just adding in if they hit the volume or the unit but it is coming back with an error. This is the formula I have can anyone help find out why it isnt working?
=IF(Total!C3<Total!G5,0,(IF(OR(J119>=U8,J118>=V8,W8*J119,IF(OR(J119>=U7,J118>=V7,W7*J119,IF(OR(J119>=U6,J118>=V6,W6*J119,IF(OR(J119>=U5,J118>=V5,W5*J119,IF(OR(J119>=U4,J118>=V4,W4*J119,IF(OR(J119>=U3,J118>=V3,W3*J119,IF(OR(J119<U3,J118<V3,0,0))))))))))))))))
Cmyrt Honestly that is a mess. Here it is broken out somewhat and every subsequent IF statement is part of the previous OR statement which I doubt is what you wanted and in the end there is no calculations. I have a slight hunch see below:
right now:
=IF(Total!C3<Total!G5,0, (IF(OR(J119>=U8, J118>=V8, W8*J119, IF(OR(J119>=U7, J118>=V7, W7*J119, IF(OR(J119>=U6, J118>=V6, W6*J119, IF(OR(J119>=U5, J118>=V5, W5*J119, IF(OR(J119>=U4, J118>=V4, W4*J119, IF(OR(J119>=U3, J118>=V3, W3*J119, IF(OR(J119<U3,J118<V3,0,0)) ) ) ) ) ) ) ) ) ) ) ) ) ) )
try this:
=IF(Total!C3<Total!G5,0, IF(OR(J119>=U8,J118>=V8), W8*J119, IF(OR(J119>=U7,J118>=V7), W7*J119, IF(OR(J119>=U6,J118>=V6), W6*J119, IF(OR(J119>=U5,J118>=V5), W5*J119, IF(OR(J119>=U4,J118>=V4), W4*J119, IF(OR(J119>=U3,J118>=V3), W3*J119, IF(OR(J119<U3,J118<V3),0,0) ) ) ) ) ) ) )
- mtarlerSilver Contributor
Cmyrt Honestly that is a mess. Here it is broken out somewhat and every subsequent IF statement is part of the previous OR statement which I doubt is what you wanted and in the end there is no calculations. I have a slight hunch see below:
right now:
=IF(Total!C3<Total!G5,0, (IF(OR(J119>=U8, J118>=V8, W8*J119, IF(OR(J119>=U7, J118>=V7, W7*J119, IF(OR(J119>=U6, J118>=V6, W6*J119, IF(OR(J119>=U5, J118>=V5, W5*J119, IF(OR(J119>=U4, J118>=V4, W4*J119, IF(OR(J119>=U3, J118>=V3, W3*J119, IF(OR(J119<U3,J118<V3,0,0)) ) ) ) ) ) ) ) ) ) ) ) ) ) )
try this:
=IF(Total!C3<Total!G5,0, IF(OR(J119>=U8,J118>=V8), W8*J119, IF(OR(J119>=U7,J118>=V7), W7*J119, IF(OR(J119>=U6,J118>=V6), W6*J119, IF(OR(J119>=U5,J118>=V5), W5*J119, IF(OR(J119>=U4,J118>=V4), W4*J119, IF(OR(J119>=U3,J118>=V3), W3*J119, IF(OR(J119<U3,J118<V3),0,0) ) ) ) ) ) ) )
- CmyrtCopper ContributorThank you, sorry for the mess it was this worked perfectly!