Is there a shorter way of writing this IF statement?
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::
Comments

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.

That's a good idea  will definitely try that!

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#comment151026
Basically take all of the variables possible and show what happens when each criteria is met.

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.
Help Article Resources
Categories
Check out the Formula Handbook template!