Revision of IF formula

Jsdeloria21
Jsdeloria21
in Modeling

Hi Guys,

 

So I have an issue with my IF formula, initially I was able to get the logic of this but the client decided to add a Contract Status (Full Cancellation). After that my count seems to be out in some months. So I need to adjust the logic but for some reason I am getting the correct results in some months but after inserting the Full Cancellation logic it is not counting correctly. Sample below.

  1. Contract Status
  2. Book Date
  3. Commitment Date

Formula in Excel:

Initially this is formula

If(A=”Cancelled”,IF(B=<>”,”Cancelled”,IF(C<>””,”Back Out”,”Fall Out”)),IF(OR(A=”Cancelled with Re-Contract”,A=”Recontruct”),A))

Since adding a new status Full Cancellation my logic is off in some months.

If(A=”Cancelled” or A=”Full Cancellation”,IF(B=<>”,”Cancelled”,IF(C<>””,”Back Out”,”Fall Out”)),IF(OR(A=”Cancelled with Re-Contract”,A=”Recontruct”),A))

 

Is there a way to simplify this in my ANAPLAN formula? Below is my ANAPLAN formula (I know it is too much IF statement which is not advisable).

IF Contract Status = 'Contract Status (V)'.Cancelled OR Contract Status = 'Contract Status (V)'.Full Cancellation AND ISBLANK(Book Date) AND ISBLANK(Commitment Date) THEN 'Contract Status (V)'.Fall Out ELSE IF Contract Status = 'Contract Status (V)'.Cancelled AND ISBLANK(Book Date) AND ISNOTBLANK(Commitment Date) THEN 'Contract Status (V)'.Back Out ELSE IF Contract Status = 'Contract Status (V)'.Full Cancellation THEN 'Contract Status (V)'.Cancelled ELSE IF Contract Status = 'Contract Status (V)'.Recontract THEN BLANK ELSE IF Contract Status = 'Contract Status (V)'.Booked THEN Contract Status ELSE IF Contract Status = 'Contract Status (V)'.Fully Paid THEN Contract Status ELSE IF Contract Status = 'Contract Status (V)'.Fully Paid with OC THEN Contract Status ELSE IF Contract Status = 'Contract Status (V)'.Reserved THEN Contract Status ELSE IF Contract Status = 'Contract Status (V)'.With Downpayment THEN Contract Status ELSE Contract Status

 

Appreciate if there is a much simplified formula for this.

 

Regards,

Tagged:

Best Answer

  • prabhu
    prabhu
    Answer ✓

    Hi @Jsdeloria21 - Yes you are right, too much usage of IF Statement is not advisable.

    in addition to that, you shouldn't use hardcode reference in any formula.

     

    I tried to replicate your scenario.

     

    I don't know your complete requirement, but my approach may give you an idea.

     

    Step 1: I Created a Contract Status List.

     

    Capture0.JPG

     

     

    Step 2: I Created a module (to highlight only cancelled and Full Cancellation), which help you in your 1st and 2nd statement of IF

     

    Capture1.1.JPGCapture1.JPG

     

     

    Step 3: I Created a System Module with Contract Status as dimension.

    Purpose of this module is to handle all other if and else portion in your IF formula

     

    Capture2.1.JPGCapture2.JPGCapture3.1.JPG

     

     

    Final Formula Looks like.

     

     

    Capture3.JPG

     

     

     

    Hope this may help you.

     

    Thanks,

    Sorna Raja Prabhu

Categories