Aging formula

[jdmh22a]

Here is the problem: I work for a property mgmt company and our delinquent policy is stated as follow:

$150.00 balance carrying > 11 Days = Letter 1 (reminding you are late)

$150 balance carrying > 42 Days = Letter 2 (sending second notice)

$150 balance carrying > 52 Days = Letter 3 (sending to court)

Let’s say, Fees due on Jan. 1st. Jan. 10th grace period ends. Jan 11- John receive L1.
No payment received from him as of Feb. 10th. So he is 2 months behind. He gets an L2. Still no payment received from him by Feb. 21st. On Feb. 22nd he receives L3. However, if he pays on Jan. 31st, he will catch up for Jan. Then if he is late again on Feb, he will receive a L1 on Feb. 11th. Sooner the balance reaches the threshold amount; the cycle starts from the beginning.

I can run the report from software at any given date and see the delinquent folks and the total amount they owe as of that day. I can even filter by delinquent amount and run reports for folks whose balance is > 150.00. The challenge is when it comes to assign the appropriate letter. Any help would be highly appreciated. Thanks.

[oldchippy]

Hi,

There may be a more elegant way but how about this in G3 then drag down

Please Login or Register  to view this content.

[jdmh22a]

Hi:

First off, thanks a lot for the formula. It really works. Would you please explain to me in words how to read this formula. I mean I can read a simple if statement. but this is a complex one with 3 ifs and vlookup.

[oldchippy]

OK,

Starting with this part first, COUNTIF($A$3:$A$55,F3) this will produce a 0 if the Unit number is only in list 2, or 1 if the Unit number is in both lists. In other words 0=FALSE, 1=TRUE.

We can now put that into an IF(if this is TRUE, make it 2, if not make it 1) this is the formula below

=IF(COUNTIF($A$3:$A$55,F3),2,1)

So this last formula tells us whether the people in list 2 need a number 2 letter or a number 1 letter.

The next part

VLOOKUP(F3,$A$3:$C$55,3,FALSE) looks at the Unit number in the second list and if it is present in the first list will bring back the value in column C of that row where the Unit number is found, if it doesn’t then this produces #N/A error.

Now to eliminate the #N/A error we now force it to only return a value if there is one or if there isn’t then return a BLANK, to do this we put the first formula and the second formula in another IF(if this is TRUE, return a VALUE, if not make it BLANK) this is the formula below

=IF(IF(COUNTIF($A$3:$A$55,F3),2,1)=2,VLOOKUP(F3,$A$3:$C$55,3,FALSE),"")

So this last formula will only return the value from list 1 if the person is in both list 1 and 2.

The last part now looks at the values returned and if the value in list 2 is greater than the value in list 1, it needs a letter 2, if the value is less then it needs a letter 1. So again this is in another IF(if this is TRUE, make it 2, if not make it 1) this is the formula below

=IF(IF(IF(COUNTIF($A$3:$A$55,F3),2,1)=2,VLOOKUP(F3,$A$3:$C$55,3,FALSE),"")<I3,2,1)

Hope all this make sense and I haven’t confused things for you?

[jdmh22a]

Hi:

Thanks a lot for your time and explaining the formula to me. I really appreciate it. I am working on to make the formula a little more effective for my situation. Part of the problem is, this is an complex aging situation here. I will probably need some help from you. But let me do the homework first. Thanks again.

[oldchippy]

No Problem - thanks for the feedback