Help Replicating Excel Formula

neg177

Hi, 

 

Need help replicating this Excel Formula. The Anaplan formula we created is not working. 

 

Excel Formula 

=IF((WR20+WR26+WR27+WR29+WR28+WR5*0.95+WR31+WR32+WR35)-WR40>0,IF(WR29+(WR5*0.95)+WR31+WR32+WR28-WR35<0,0,WR5*0.95),0)

 

Our Formula 

IF
'S01 Time Settings'.Actual Date <= 'S02 Time Admin'.Last Actual Day
THEN
PFR Value of Receivables * 0.95
ELSE
if
(Total Eligible Principal + 'Interest Acct (x1001)' + 'Securitization Master Acct (x5000 Opening)' + 'BALANCING FIGURE FOR x5000' + PFR Value of Receivables * 0.95 + Cash Collected + '(Cash Sweep to Wells)') - 'Min. Asset Requirement'>0
then
if
(
IF

'S01 Time Settings'.Weekdays?
THEN
IF

WEEKDAY(START()) = 1
THEN

OFFSET('Securitization Master Acct (x5000 Closing)' , -3, 0)
ELSE

PREVIOUS('Securitization Master Acct (x5000 Closing)' )
ELSE

0)+(PFR Value of Receivables*0.95) +Cash Collected+'(Cash Sweep to Wells)' +'BALANCING FIGURE FOR x5000'-Excess Funding Sweep<0
then
0
else
PFR Value of Receivables*0.95
else
0

PhilippErkinger

Is the formula not valid (anaplan gives you a message) or does it provide the wrong result?

 

Additional remark: My general recommendation would be to split out the individual parts of the statement into separate line items. For example: This alone should be at least 2 statements. The first one with the calculation, the second one with the True / False check. (Total Eligible Principal + 'Interest Acct (x1001)' + 'Securitization Master Acct (x5000 Opening)' + 'BALANCING FIGURE FOR x5000' + PFR Value of Receivables * 0.95 + Cash Collected + '(Cash Sweep to Wells)') - 'Min. Asset Requirement'>0

This allows easier debugging of the individual statements.