0

I wrote a formula for a field Target per Month per Opportunity owner that should populate with currency when an opportunity has a certain close date month.

IF( AND( Owner.Id = 0054J000001U0X4QAK, MONTH(CloseDate)=1) ,139.043,  
IF( AND( Owner.Id = 0054J000001U0X4QAK, MONTH(CloseDate)=2) ,141.617,  
IF( AND( Owner.Id = 0054J000001U0X4QAK, MONTH(CloseDate)=3) ,149.341,  
IF( AND( Owner.Id = 0054J000001U0X4QAK, MONTH(CloseDate)=4) ,164.791,
IF( AND( Owner.Id = 0054J000001U0X4QAK, MONTH(CloseDate)=5) ,180.240,
IF( AND( Owner.Id = 0054J000001U0X4QAK, MONTH(CloseDate)=6) ,185.390,
IF( AND( Owner.Id = 0054J000001U0X4QAK, MONTH(CloseDate)=7) ,200.839,
IF( AND( Owner.Id = 0054J000001U0X4QAK, MONTH(CloseDate)=8) ,195.690,
IF( AND( Owner.Id = 0054J000001U0X4QAK, MONTH(CloseDate)=9) ,218.862,
IF( AND( Owner.Id = 0054J000001U0X4QAK, MONTH(CloseDate)=10) ,226.588,
IF( AND( Owner.Id = 0054J000001U0X4QAK, MONTH(CloseDate)=11) ,244.611,
IF( AND( Owner.Id = 0054J000001U0X4QAK, MONTH(CloseDate)=12) ,205.987,
IF( AND( Owner.Id = 0054J000001U0QGQA0, MONTH(CloseDate)=1) ,64.896,  
IF( AND( Owner.Id = 0054J000001U0QGQA0, MONTH(CloseDate)=2) ,66.098,  
IF( AND( Owner.Id = 0054J000001U0QGQA0, MONTH(CloseDate)=3) ,69.703,  
IF( AND( Owner.Id = 0054J000001U0QGQA0, MONTH(CloseDate)=4) ,76.914,
IF( AND( Owner.Id = 0054J000001U0QGQA0, MONTH(CloseDate)=5) ,84.124,
IF( AND( Owner.Id = 0054J000001U0QGQA0, MONTH(CloseDate)=6) ,86.528,
IF( AND( Owner.Id = 0054J000001U0QGQA0, MONTH(CloseDate)=7) ,93.739,
IF( AND( Owner.Id = 0054J000001U0QGQA0, MONTH(CloseDate)=8) ,91.335,
IF( AND( Owner.Id = 0054J000001U0QGQA0, MONTH(CloseDate)=9) ,102.151,
IF( AND( Owner.Id = 0054J000001U0QGQA0, MONTH(CloseDate)=10) ,105.756,
IF( AND( Owner.Id = 0054J000001U0QGQA0, MONTH(CloseDate)=11) ,114.169,
IF( AND( Owner.Id = 0054J000001U0QGQA0, MONTH(CloseDate)=12) ,96.142,
IF( AND( Owner.Id = 0054J000001U0XOQA0, MONTH(CloseDate)=1) ,28.338, 
IF( AND( Owner.Id = 0054J000001U0XOQA0, MONTH(CloseDate)=2) ,28.914,  
IF( AND( Owner.Id = 0054J000001U0XOQA0, MONTH(CloseDate)=3) ,30.491,  
IF( AND( Owner.Id = 0054J000001U0XOQA0, MONTH(CloseDate)=4) ,33.645,
IF( AND( Owner.Id = 0054J000001U0XOQA0, MONTH(CloseDate)=5) ,36.800,
IF( AND( Owner.Id = 0054J000001U0XOQA0, MONTH(CloseDate)=6) ,37.851,
IF( AND( Owner.Id = 0054J000001U0XOQA0, MONTH(CloseDate)=7) ,41.005,
IF( AND( Owner.Id = 0054J000001U0XOQA0, MONTH(CloseDate)=8) ,39.954,
IF( AND( Owner.Id = 0054J000001U0XOQA0, MONTH(CloseDate)=9) ,44.685,
IF( AND( Owner.Id = 0054J000001U0XOQA0, MONTH(CloseDate)=10) ,46.262,
IF( AND( Owner.Id = 0054J000001U0XOQA0, MONTH(CloseDate)=11) ,49.942,
IF( AND( Owner.Id = 0054J000001U0XOQA0, MONTH(CloseDate)=12) ,42.057,
            0
)))))))))) )))))))))) )))))) 
  • You should use CASE() as Adrian suggested, but also use a text editor like the free Visual Studio Code that will do parentheses matching. – David Cheng Aug 9 '19 at 14:29
  • 1
    chrome plugin salesforce.com enhanced formula editor is handy too – cropredy Aug 9 '19 at 17:48
6

First of all, I recommend you use Owner.Name rather than Owner.Id (which you could have made just OwnerId). Never use hard coded ids. Migrating this formula between orgs would be at best an annoyance. My second recommendation is that you make use of CASE statements to dramatically simplify the syntax. My third recommendation is to use indentation to make opening and closing brackets exceedingly clear.

CASE(
    Owner.Name,
    "Person A", CASE(
        MONTH(CloseDate),
        1, 139.043,
        2, 141.617,
        ...,
        0
    ),
    "Person B", CASE(
        ...
    ),
    "Person C", CASE(
        ...
    ),
    0
)
  • 1
    Hierarchy custom setting would work based on the current user right and not on the owner of the record. But yeah that was a good idea :) – Vijay Ganji Aug 9 '19 at 13:20
  • Yeah, you're right. Good point. – Adrian Larson Aug 9 '19 at 13:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.