# Need help formatting and writing this formula.  Hi,

The Excel Version of the Formula I'm trying to write is =MIN(ROUND(ABS(((UI20+UJ9)-UJ40)/Collection_Funding!UI3),2),0.98)

The Formula I'm trying to use is MIN(ROUND(ABS(((previouse(total eligible principle)+paydown)-Min Assesset Requirment)/Previouse(Collection Funding!RTR)), 2), 0.98), which I do not believe is 100% correct.

Functions I'm using (MIN, ROUND, ABS, PREVIOUS)

Line Items (UI20=total eligible principle, UJ9=paydown, UJ40=Min Assesset Requirment, UI3=RTR) All my Line Items are Number Formatted.

Thank you

Tagged:

•  Thank you so much. The below are the formulas I ended up using. Once I get in Data I will see if they work.

Thank you

ROUND(ABS(((IF 'S01 Time Settings'.Weekdays? THEN IF WEEKDAY(START()) = 1 THEN OFFSET(Total Eligible Principal, -3, 0) ELSE PREVIOUS(Total Eligible Principal) ELSE 0 + '(Paydown)') - 'Min. Asset Requirement') / IF 'S01 Time Settings'.Weekdays? THEN IF WEEKDAY(START()) = 1 THEN OFFSET('Y01 Collection_Funding'.RTR, -3, 0) ELSE PREVIOUS('Y01 Collection_Funding'.RTR) ELSE 0), 2)

IF '+New Receivables Pledged (% to Securitization) Cal Value' < 0.98 THEN '+New Receivables Pledged (% to Securitization) Cal Value' ELSE 0.98

•  I highly recommend you breaking that up for performance reasons as well as being able to debug it.  Also, I would suggest you placing the literal (0.98) in a SYS Global module so if that ever changes, you change it one spot instead of having to hunt around for all line items that are hardcoded.

•  Hi,

How could I break the Formula up to be more readable. Also the Formula was accepted but it is not pulling in any numbers.

ROUND(ABS(((IF 'S01 Time Settings'.Weekdays? THEN IF WEEKDAY(START()) = 1 THEN OFFSET(Total Eligible Principal, -3, 0) ELSE PREVIOUS(Total Eligible Principal) ELSE 0 + '(Paydown)') - 'Min. Asset Requirement') / IF 'S01 Time Settings'.Weekdays? THEN IF WEEKDAY(START()) = 1 THEN OFFSET('Y01 Collection_Funding'.RTR, -3, 0) ELSE PREVIOUS('Y01 Collection_Funding'.RTR) ELSE 0), 2)

Thank you

•  It's okay to add more line items. Just don't forget to turn off the summary so you conserve memory.

By breaking up your formula you can also debug it easier since you can see all the components.

Here's an approach you might consider - I use LI for Line Item but  you should use descriptive names:

We'll keep 'S01 Time Settings'.Weekdays? as it is since it's referencing a system module. Our goal is to only calculate things once and to not use nested IF statements.

• LI1 = WEEKDAY(START())
• LI7 = IF LI1 = 1 THEN OFFSET(Total Eligible Principal, -3, 0) ELSE PREVIOUS(Total Eligible Principal)
• LI8 = IF 'S01 Time Settings'.Weekdays? THEN LI7 ELSE 0 + '(Paydown)') - 'Min. Asset Requirement'
• LI9 = IF LI1 = 1 THEN OFFSET('Y01 Collection_Funding'.RTR, -3, 0) ELSE PREVIOUS('Y01 Collection_Funding'.RTR)
• LI10 = IF 'S01 Time Settings'.Weekdays? THEN LI9 ELSE 0
• LI11 = ROUND(ABS(((LI8 / LI10), 2)

A lot easier to read and now is much more easily tested for issues.

Always go for simple if you can!

Hehe, that was fun. Another, Another!