ac66bronco
New Member
- Joined
- Dec 10, 2014
- Messages
- 32
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")))))
<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>
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 | ||||||
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> | Result should return | |||||
Not used | Not used | 0.00 | 0.00 |
<tbody> </tbody> | |||||
Not used | Not used | 0.00 |
<tbody> </tbody> |
<tbody> </tbody> | |||||
7/9/2014 | Not used |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> | |||||
Blank |
<tbody> </tbody> |
<tbody> </tbody> | Not used |
<tbody> </tbody> | |||||
Blank |
<tbody> </tbody> |
<tbody> </tbody> | Not used |
<tbody> </tbody> | |||||
Blank |
<tbody> </tbody> |
<tbody> </tbody> | Not used |
<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>
Last edited: