Need help with a long complex if formula

A

ac66bronco

New Member
Joined
Dec 10, 2014
Messages
29
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")))))

QRYAD
FRCLS_SALE_DATE

<tbody>
</tbody>
PAID_TO_DATE

<tbody>
</tbody>
MTG_BAL

<tbody>
</tbody>
CORP_3RDPARTY_BAL

<tbody>
</tbody>
Result should return
Not usedNot used0.000.00
a-Claim Liquidated

<tbody>
</tbody>
Not usedNot used0.00
8,406.60

<tbody>
</tbody>
a-Claim

<tbody>
</tbody>
7/9/2014Not 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>
 
Last edited:

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Sort by date Sort by votes
Try this

=IF(Y299=0,IF(AD299<1000,"a-Claim Liquidated","a-Claim"),IF(AND(Y299<>0,Q299<>""),"b-Foreclosure Sale",IF(AND(Y299>0,Q299=""),LOOKUP(R1-$B$1,{0,90,180},{"e-Current","d-90-179 Days Delinquent","c-180+ Days Delinquent"}))))

I've changed the date comparison in 1, 2, 3 to a simple subtraction of days rather than working out months.
 
Upvote 0
This looks fairly complex. I'm not sure exactly what all of the formula is doing but, I would suggest:

Changing this: NOT(ISBLANK(Q299))

To This: Q299<>""

Also, I think you have an extra argument that is not necessary See below R2990....It looks like this is an error:

Code: 
[COLOR=#333333]=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),[/COLOR][COLOR=#ff0000][B]R299[/B][/COLOR]<date(year($b$1),month($b$1)+5,day($b$1))),"e-current",if(and(y299 style="color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">[COLOR=#ff0000][B]0[/B][/COLOR],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")))))

This formula works up to the point where you were at.... you just need to change the "INSERT NEXT IF STATEMENT HERE" to your next if condition:
Code: 
=IF(AND(Y2=0,AD2<1000),"a-Claim Liquidated",IF(AND(Y2=0,AD2>1000),"a-Claim",IF(AND(Y2<>0,Q2<>""),"b-Foreclosure Sale",IF(AND(Y2>0,Q2="",Q2="", R299>=DATE(YEAR($B$1),MONTH($B$1)+5,DAY($B$1))),"e-current",[B][COLOR=#ff0000]"INSERT NEXT IF STATEMENT HERE"[/COLOR][/B]))))


What Date Value is in cell B1????</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:
Upvote 0
Hello and thank you for the response so far that seems to be working! except for when it gets to c-180+ Days Delinquent.

I may be able to solve this with adding an if error return statamente
 
Upvote 0
This seems to work:
Code: 
=IF(Y299=0,IF(AD299>=1000,"a-Claim","a-Claim Liquidated")
,IF(Q299<>"","b-Foreclosure Sale"
,LOOKUP(DATEDIF(R299,$B$1,"M"),{0,5,8},{"e-Current","d-90-179 Days Delinquent","d-180+ Days Delinquent"})))

Does it?
 
Upvote 0
Man these are all working in some aspect!!!!

I am going to try and make this simpler by adding a column in X and copying the 11/30/2014 file date all the way down.

Thank you all I keep trying all of these maybe some combination of them all will work!!!!
 
Upvote 0
Is there a way that I can upload an excel doc? I am at work so I cant use google dox.

Ill have to remove some sensitive information but it might help you guys help me.
 
Upvote 0
Using your posted workbook, this formula seems to return correct results the most:
Code: 
B4: =IF(AA4=0,IF(AF4>=1000,"a Claim","a Claim Liquidated"),IF(R4 <> "","b Foreclosure Sale",LOOKUP(IFERROR(DATEDIF(S4,$B$2,"M"),0),{0,5,8},{"e Current","d 90-179 Days Delinquent","c 180+ Days Delinquent"})))

The few times it does not match your "known good results" are related to the way you're defining delinquency.
The DATEDIF function counts whole months. However, you may be using a different method...30-day-months? something else? Maybe different threshholds?
 
Last edited:
Upvote 0
Actually that worked brilliantly!!!!!!!!

Thank you very much Ron!

To clarify we do use the 30 day months method.
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question
Analytics Project - Sumifs
Replies
1
Views
265
bobaol
B
S
  • Question
Need Help in Dates
Replies
3
Views
58
alansidman
A
R
  • Question
Date formula Help!
2
Replies
11
Views
651
Asbestos_Jen
Asbestos_Jen
R
  • Solved
VBA Help to Automate Year
Replies
2
Views
153
RattlingCarp3048
R
john Smith72
  • Solved
Auto filter VBA
Replies
2
Views
143
john Smith72
john Smith72

Forum statistics

Threads
1,215,518
Messages
6,125,292
Members
449,218
Latest member
Excel Master

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top