0

Working on a IF & ELSE statement for a calculation. If the close date (year) is the current year, then I need to divide an amount by 3. If the close date (year) is next year, then I need to divide an amount by 5.

I wrote the following IF statement but its not working. Any ideas?

Error:

Error: Syntax error. Found 'ELSE'

Code:

IF
( YEAR(CloseDate) = YEAR(TODAY()),

(

IF( MONTH(CloseDate) = 1,
(Oil_Change_Amount_4__c / 3.1)
,

IF( MONTH(CloseDate) = 2,
(Oil_Change_Amount_4__c / 3.2)
,

IF( MONTH(CloseDate) = 3,
(Oil_Change_Amount_4__c / 3.3)
,

IF( MONTH(CloseDate) = 4,
(Oil_Change_Amount_4__c / 3.4)
,

IF( MONTH(CloseDate) = 5,
(Oil_Change_Amount_4__c / 3.5)
,

IF( MONTH(CloseDate) = 6,
(Oil_Change_Amount_4__c / 3.6)
,

IF( MONTH(CloseDate) = 7,
(Oil_Change_Amount_4__c / 3.7)
,

IF( MONTH(CloseDate) = 8,
(Oil_Change_Amount_4__c / 3.8)
,

IF( MONTH(CloseDate) = 9,
(Oil_Change_Amount_4__c / 3.9)
,

IF( MONTH(CloseDate) = 10,
(Oil_Change_Amount_4__c / 3.10)
,

IF( MONTH(CloseDate) = 11,
(Oil_Change_Amount_4__c / 3.11)
,

IF( MONTH(CloseDate) = 12,
(Oil_Change_Amount_4__c / 3.12)

)
,

ELSE IF
( YEAR(CloseDate) = YEAR(TODAY() + 1),

(

IF( MONTH(CloseDate) = 1,
(Oil_Change_Amount_4__c / 5.1)
,

IF( MONTH(CloseDate) = 2,
(Oil_Change_Amount_4__c / 5.2)
,

IF( MONTH(CloseDate) = 3,
(Oil_Change_Amount_4__c / 5.3)
,

IF( MONTH(CloseDate) = 4,
(Oil_Change_Amount_4__c / 5.4)
,

IF( MONTH(CloseDate) = 5,
(Oil_Change_Amount_4__c / 5.5)
,

IF( MONTH(CloseDate) = 6,
(Oil_Change_Amount_4__c / 5.6)
,

IF( MONTH(CloseDate) = 7,
(Oil_Change_Amount_4__c / 5.7)
,

IF( MONTH(CloseDate) = 8,
(Oil_Change_Amount_4__c / 5.8)
,

IF( MONTH(CloseDate) = 9,
(Oil_Change_Amount_4__c / 5.9)
,

IF( MONTH(CloseDate) = 10,
(Oil_Change_Amount_4__c / 5.10)
,

IF( MONTH(CloseDate) = 11,
(Oil_Change_Amount_4__c / 5.11)
,

IF( MONTH(CloseDate) = 12,
(Oil_Change_Amount_4__c / 5.12)

)


0))))))))))))
Improve this question
1
  • 2
    Else is not a valid formula, you need to add a better logic for your formula.
    – Raul
    Commented Nov 3, 2017 at 13:25

2 Answers 2

Reset to default
1

You could try something like this:

Oil_Change_Amount_4__c / VALUE ("5." + MONTH(CloseDate))

and replace the "5." with

CASE(YEAR(CloseDate), 
YEAR(TODAY()),"3.",
YEAR(TODAY() + 1,"5.")

So it would look like this:

Oil_Change_Amount_4__c /
VALUE( CASE(YEAR(CloseDate), 
       YEAR(TODAY()),"3.",
       YEAR(TODAY() + 1,"5.")
     + MONTH(CloseDate) )

To make everything else 0 you could surround it with a IF

IF( (YEAR(CloseDate) = YEAR(TODAY()))
 || (YEAR(CloseDate) = YEAR(TODAY())+1),

    Oil_Change_Amount_4__c /
    VALUE( CASE(YEAR(CloseDate), 
           YEAR(TODAY()), "3.",
           YEAR(TODAY()) + 1, "5.",
           "")
         + TEXT(MONTH(CloseDate)) )
    ,0
)
Improve this answer
4
  • 2
    +1 Good use of concatenation as a string and converting it to a value!
    – Raul
    Commented Nov 3, 2017 at 13:47
  • I like your logic here, but I'm getting an error (Error: Syntax error. Extra ','). Also, how can tell the difference between months? For example. What if I only want the calculation to take place if the closedate month is either Jan Feb or Mar to be populated in the field?
    – user48169
    Commented Nov 3, 2017 at 14:27
  • @HUBBY0716 If you need more conditions, just add them to the surrounding IF statement. Ya sorry I didn't check if the syntax is valid, let me doublecheck that.
    – itsmebasti
    Commented Nov 3, 2017 at 14:29
  • @HUBBY0716 had to fix a couple things, now it should work.
    – itsmebasti
    Commented Nov 3, 2017 at 14:41
0

In formulas, the IF is a function with 3 parameters, 1st is the condition, 2nd is the value when is true, and 3rd is the value when is false. Pretty much like the IF function in Excel / Sheets or many other spreadsheet programs.

If you want to individually put your values (in case they do not just go one by one, and need more customisation than just a concatenation of the month to the 3. or 5. you could use...

IF( YEAR(CloseDate) = YEAR(TODAY()),
    Oil_Change_Amount_4__c / 
    CASE(MONTH(CloseDate),
        1,3.1,
        2,3.2,
        3,3.3,
        4,3.4,
        5,3.5,
        6,3.6,
        7,3.7,
        8,3.8,
        9,3.9,
        10,3.10,
        11,3.11,
        12,3.12,
        1
    ),
    IF (YEAR(CloseDate) = YEAR(TODAY() + 1,
        Oil_Change_Amount_4__c / 
        CASE(MONTH(CloseDate),
            1,5.1,
            2,5.2,
            3,5.3,
            4,5.4,
            5,5.5,
            6,5.6,
            7,5.7,
            8,5.8,
            9,5.9,
            10,5.10,
            11,5.11,
            12,5.12,
            1
        ),
        0)
    )
)

If you want to make it shorter and the values are like you specify..., you could also do something like

IF (
    OR(
        YEAR(CloseDate) = YEAR(TODAY()), 
        YEAR(CloseDate) = YEAR(TODAY()) + 1
    ),
    Oil_Change_Amount_4__c / VALUE(IF(YEAR(CloseDate) = YEAR(TODAY()),"3.","5.") + MONTH(CloseDate))
    ,
    0
)

Hope it helps

Improve this answer

You must log in to answer this question.