Forum Discussion

Cmyrt's avatar
Cmyrt
Copper Contributor
Sep 02, 2022

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)
                                   ) 
                            ) 
                       ) 
                 ) 
           )  
       ) 
    ) 
  • mtarler's avatar
    mtarler
    Silver 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)
                                   ) 
                            ) 
                       ) 
                 ) 
           )  
       ) 
    ) 
    • Cmyrt's avatar
      Cmyrt
      Copper Contributor
      Thank you, sorry for the mess it was this worked perfectly!

Resources