Trying to replicate Excel Formula

Hi,

 

I'm trying to replicate the Excel Formula in Anaplan below. I'm trying to use the AND function, so i made two Boolean Line Items and combined them to satisfy the condition. The formula worked but no numbers are showing in my line item. please advise. 

 

Excel Formula 

=IF(AND(Accounts!WP32<3000000,MIN(4000000,WP27,WP41-2000000)>0),MIN(4000000,WP27,WP41-2000000),0)

 

My Formula 

 

IF 'EFS to WFA Calc1' AND 'EFS to WFA Calc 2' THEN MIN(4000000, IF 'S01 Time Settings'.Weekdays? THEN IF WEEKDAY(START()) = 1 THEN OFFSET('Y03 Securitization (Actuals)'.'Excess Funding Account (x1005)', -3, 0) ELSE PREVIOUS('Y03 Securitization (Actuals)'.'Excess Funding Account (x1005)') ELSE 0, (IF 'S01 Time Settings'.Weekdays? THEN IF WEEKDAY(START()) = 1 THEN OFFSET('Y03 Securitization (Actuals)'.'Excess Collateral / (Deficit)', -3, 0) ELSE PREVIOUS('Y03 Securitization (Actuals)'.'Excess Collateral / (Deficit)') ELSE 0) - 2000000) ELSE 0

Best Answers

  • You have to troubleshoot using drill down function (F8) on the resultant and check if which condition makes your result to become 0

     

    Thanks

    Arun

  • Misbah
    Answer ✓

    @neg177 

     

    Could you please share the screenshot of your module. Excel formula looked simple but Anaplan it is pretty complicated

Answers

  • I'd also try breaking it down into the various elements to see where the issue is occuring particularly the offset piece.

    Am I right in thinking if you're looking at a weekend day or the Booleans aren't true then you will get zero?

  • Hi,

     

    Yes the pic of the module and formula are below. 

     

    Thank you 

     

    neg177_0-1620239967177.png

    neg177_1-1620240020949.png

     The Excel Formula 

    =IF(AND(Accounts!WU32<3000000,MIN(4000000,WU27,WU41-2000000)>0),MIN(4000000,WU27,WU41-2000000),0)

  • Hi,

     

    Yes that is correct