Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

Please HELP multiple Nested IF/OR Statements

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)
                               ) 
                        ) 
                   ) 
             ) 
       )  
   ) 
) 
Thank you, sorry for the mess it was this worked perfectly!
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)
                               ) 
                        ) 
                   ) 
             ) 
       )  
   ) 
) 

View solution in original post