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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63K Get Help
 380 Global Discussions
 212 Industry Talk
 442 Announcements
 4.6K Ideas & Feature Requests
 140 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 305 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!