Is there a shorter way of writing this IF statement?

Annie Dunn

Here's the working statement:

=IF(AND([Account Owner]191 = "JK", [SALES REP]191 = "JK", OR([BILLED BY]191 = "Merit Medical", [BILLED BY]191 = "Don Joy"), STATUS191 = "Paid"), CHARGES191 * 0.14, IF(AND([Account Owner]191 = "JK", [SALES REP]191 = "JK", OR([BILLED BY]191 = "Cerapedics", [BILLED BY]191 = "Surgentec", [BILLED BY]191 = "InfinityDMEDewanjee", [BILLED BY]191 = "InfinityDME"), STATUS191 = "Paid"), CHARGES191 * 0.2))

 

Right now I'm repeating this part: IF(AND([Account Owner]191 = "JK", [SALES REP]191 = "JK", STATUS191 = "Paid") every time. (This part of the statement always need to be true.) 

The pieces of the equation that change are the [BILLED BY] field and corresponding value.

So, assuming all of the above statements are true, I need the following calculations to occur:

[BILLED BY] = "Don Joy", [BILLED BY] = "Merit Medical" then [CHARGES] * 14.

[BILLED BY] = "Cerapedics", [BILLED BY] = "InfiniteyDMEDewanjee", [BILLED BY] = "Surgentec" then [CHARGES] * .2

[BILLED BY] = "NuTech", [BILLED BY] = "Instrumentation" then [CHARGES] * .15

[BILLED BY] = "Elliquence" then [CHARGES] * .125

 

Please let me know if there's a more concise way to write this this statement, because I also need to repeat this statement for 5 different Sales Reps + Account owner combos with varying value calculations. ::Sigh::

Dan Seidler

You may want to create a helper column or two, to break up the formula.  Then just reference that column to shorten the main formula.

Annie Dunn

That's a good idea - will definitely try that!

L_123

You could also color pallet it. Here is an example I just helped someone with, sometimes the solution isn't really the obvious one.

 

https://community.smartsheet.com/comment/151026#comment-151026

 

Basically take all of the variables possible and show what happens when each criteria is met.

Paul Newcome

You can use that as the first part of an IF, and then in the second part of THAT use your nested IF statement for the changing variables.

 

=IF(AND([Account Owner]191 = "JK", [SALES REP]191 = "JK", STATUS191 = "Paid"), IF(OR(...............), Then this, IF(OR(.................), then this...............

 

Basically you are saying IF(AND(all of these things are true), run this nested IF.