Need help formatting and writing this formula.

neg177

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. 

Please advice on how I should create this formula. 

 

Thank you 

JaredDolich

@neg177 

Maybe something like this:

Make a separate line item for this:

Calc Value = ROUND(ABS(((UI20+UJ9)-UJ40)/Collection_Funding!UI3),2)

Then another line item to do your conditional

If Calc value < 0.98 then calc value else 0.98

The MIN function is used on line items as an alternative to SUM.

JaredDolich

@neg177 

Looks great. To optimize that first equation, you might try breaking it up so its more readable. This is part of the PLANS methodology if your inclined.

neg177

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

rob_marshall

@neg177 

 

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.

neg177

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 

JaredDolich

@neg177 

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!