Need help with a long complex if formula

[ac66bronco]

Good morning. This will be my first post ever however I have used this forum many many times thanks to google search and have found this forum to simply be invaluable. I am so thank full that it exists.

I work for a bank so I use excel all day everyday.

Any way I have a very long if formula that I am having trouble with.

Here is what I need it to do.


i. If MTG_BAL = 0 and CORP_3RDPARTY_BAL < 1,000, then “a-Claim Liquidated”
ii. If MTG_BAL = 0 and CORP_3RDPARTY_BAL => 1,000, then “a-Claim”
iii. If “MTG_BAL” <> 0 and “FRCLS_SALE_DATE” <> blank, then “b-Foreclosure Sale”
iv. If “MTG_BAL” >0 and “FRCLS_SALE_DATE” = blank, then use below
1. If “PAID_TO_DATE” is less than 5 months from file date then “e-Current”
(Ex. for May 31st any loan with paid to date 2/1/2014 or after)
2. If “PAID_TO_DATE” is greater than or equal to 5 months and less than 8 months from file date then “d-90-179 Days Delinquent”
(Ex. for May 31st any loan with paid to date 1/1/2014, 12/1/2013, or 11/1/2013)
3. If “PAID_TO_DATE” is greater than or equal to 8 months from file date then “c-180+ Days Delinquent”
(Ex. for May 31st any loan with paid to date 10/1/2013 or before)


So far my formula appears to be working ok until I his iv and from there I am stuck.

my formula so far is this.

=IF(AND(Y299=0,AD299<1000),"a-Claim Liquidated",IF(AND(Y299=0,AD299>1000),"a-Claim",IF(AND(Y299<>0,NOT(ISBLANK(Q299))),"b-Foreclosure Sale",IF(AND(Y299>0,ISBLANK(Q299),R299<date(year($b$1),month($b$1)+5,day($b$1))),"e-current",if(and(y299>0,ISBLANK(Q299),R299>=DATE(YEAR($B$1),MONTH($B$1)+5,DAY($B$1)),R299<date(year($b$1),month($b$1)-8,day($b$1))),"d-90-179 days="" delinquent")))))

Q	R	Y	AD
FRCLS_SALE_DATE <tbody> </tbody>	PAID_TO_DATE <tbody> </tbody>	MTG_BAL <tbody> </tbody>	CORP_3RDPARTY_BAL <tbody> </tbody>	Result should return
Not used	Not used	0.00	0.00	a-Claim Liquidated <tbody> </tbody>
Not used	Not used	0.00	8,406.60 <tbody> </tbody>	a-Claim <tbody> </tbody>
7/9/2014	Not used	28,651.48 <tbody> </tbody>	-4,591.62 <tbody> </tbody>	b-Foreclosure Sale <tbody> </tbody>
Blank	7/1/2013 <tbody> </tbody>	18,915.75 <tbody> </tbody>	Not used	e-Current <tbody> </tbody>
Blank	6/1/2014 <tbody> </tbody>	38,284.44 <tbody> </tbody>	Not used	d-90-179 Days Delinquent <tbody> </tbody>
Blank	4/1/2014 <tbody> </tbody>	37,133.61 <tbody> </tbody>	Not used	c-180+ Days Delinquent <tbody> </tbody>

<tbody>
</tbody>

Above is some of the data that is used. Also the File dat is 11/30/2014

Any help or suggestions would be greatly appreciated!!!

Edit eww sorry for the ill table I've never done one on a forum before and it copied and pasted over bad.</date(year($b$1),month($b$1)-8,day($b$1))),"d-90-179></date(year($b$1),month($b$1)+5,day($b$1))),"e-current",if(and(y299>

 

[ac66bronco]

NVM I'm an idiot!!!!

HMMM this worked in one file and not in the other...... odd

 

[ac66bronco]

GOT IT DOWN TO TWO ERRORS!!!!

And they were the 2 that were already marked as exceptions!

I changed 5 and 8 to 4 and 7 and it was 99.99999999% accurate!

Thank you all for your help!

 

[Ron Coderre]

Glad you got something you can work with!