## Trying to replicate Excel Formula

Regular Contributor

## 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

2 ACCEPTED SOLUTIONS

Accepted Solutions
Master Anaplanner/Community Boss

## Re: Trying to replicate Excel Formula

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

Moderator

## Re: Trying to replicate Excel Formula

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

5 REPLIES 5
Master Anaplanner/Community Boss

## Re: Trying to replicate Excel Formula

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

Moderator

## Re: Trying to replicate Excel Formula

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

Regular Contributor

## Re: Trying to replicate Excel Formula

Hi,

Yes the pic of the module and formula are below.

Thank you

The Excel Formula

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

Master Anaplanner/Community Boss

## Re: Trying to replicate Excel Formula

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?

Regular Contributor

## Re: Trying to replicate Excel Formula

Hi,

Yes that is correct