Help Replicating Excel Formula

neg177
Regular Contributor

Help Replicating Excel Formula

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

2 ACCEPTED SOLUTIONS

Accepted Solutions
PhilippErkinger
Certified Master Anaplanner

Re: Help Replicating Excel Formula

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.

Planning Rockstar at Bluesprint

View solution in original post

ChrisHeathcote
Community Boss

Re: Help Replicating Excel Formula

@neg177 

Key to building out nested IF THEN ELSE formulas is to break them out as far as possible.

Use booleans to inform each query and never hard code a variable. If you can do this then you can be assured that you and another person inheriting the model will fully understand the logic backing up the outcome. 

Also, structure the nested functions so that it resolves with the most probable outcome first and least probable last. 

Not only with this improve the efficiency of the formula it will also allow you to be crystal clear in the logic.  

Finally, aim to understand the logic behind what you are trying to replicate. It is not always preferable to just copy what is done in the excel model. 

Following these principles you will be able to construct all manor of complex statements. 

Chris Heathcote
Bedford Consulting

Gold Partner and Regional Partner of the Year 2021, EMEA

View solution in original post

2 REPLIES 2
PhilippErkinger
Certified Master Anaplanner

Re: Help Replicating Excel Formula

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.

Planning Rockstar at Bluesprint

View solution in original post

ChrisHeathcote
Community Boss

Re: Help Replicating Excel Formula

@neg177 

Key to building out nested IF THEN ELSE formulas is to break them out as far as possible.

Use booleans to inform each query and never hard code a variable. If you can do this then you can be assured that you and another person inheriting the model will fully understand the logic backing up the outcome. 

Also, structure the nested functions so that it resolves with the most probable outcome first and least probable last. 

Not only with this improve the efficiency of the formula it will also allow you to be crystal clear in the logic.  

Finally, aim to understand the logic behind what you are trying to replicate. It is not always preferable to just copy what is done in the excel model. 

Following these principles you will be able to construct all manor of complex statements. 

Chris Heathcote
Bedford Consulting

Gold Partner and Regional Partner of the Year 2021, EMEA

View solution in original post