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
)))))))))) )))))))))) ))))))
Improve this question
2
  • 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, 2019 at 14:29
  • 1
    chrome plugin salesforce.com enhanced formula editor is handy too
    – cropredy
    Aug 9, 2019 at 17:48

1 Answer 1

Reset to default
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
)
Improve this answer
2
  • 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, 2019 at 13:20
  • Yeah, you're right. Good point.
    – Adrian Larson ♦
    Aug 9, 2019 at 13:26

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .