SOLVED

Copper Contributor

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))))))))))))))))

2 Replies
best response confirmed by mtarler (Silver Contributor)
Solution

@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)
)
)
)
)
)
)
) ``````

1 best response

Accepted Solutions
best response confirmed by mtarler (Silver Contributor)
Solution

@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)
)
)
)
)
)
)
) ``````