On Monday June 12 2006 05:30 pm, Keith Bainbridge wrote:
> G'day folks
>
> I am trying to automate a process where if a debt moves from 30 days to
> 60 days overdue (and then from 60 - 90; and from 90 - 120) then calc
> will return an indication of the letter needed, or today's date. If they
> have debts only at 60 days they get the soft letter; at 90 days the
> middle letter; and etc....
>
> The columns haven't lined up well, but here is an example. I am dealing
> with about 350 debts (from almost 6,000 Clients).
> 60days 90 days 120 days 150 days 180days
> A $4,875.63 $4,832.52 $4,789.78 $4,748.36 $4,043.39
> B $4,439.60 $4,400.00 $4,400.00 $0.00 $0.00
> C $7,986.38 $4,015.00 $1,265.00 $0.00 $0.00
> E $3,877.50 $3,877.50 $277.50 $277.50 $277.50
> F $5,167.25 $3,517.25 $209.35 $202.50 $92.50
> M $3,010.15 $0.00 $0.00 $0.00 $0.00
> N $2,276.50 $2,276.50 $2,276.50 $2,276.50 $2,276.50
> O $2,265.00 $2,265.00 $2,265.00 $2,265.00 $2,265.00
>
> The small increments are interest which has to be disregarded in the
> calculations. And I am to disregard debts below $300 in this process.
>
> To clarify, the first 3 debtors would get the tough letter. (C has had
> some debt for over 120 days so I treat the lot as 120 days.) Debtors E
> & F need the middle letter. And M needs the softer letter. N & O get
> tough again.
>
> I figured that I should be able to use a few =if functions but I can't
> figure how to nest them.
>
> I can see 2 ways of doing it:
> Ideally, one set of =ifs will look at the 60 90 and 120 day figures and
> return soft, middle or tough. Or
>
> 3 separate =ifs to give 'today' in the column that says soft, middle or
> tough
>
>
> Any suggestions will be appreciated.
As a starter, you may need one more column: date of the earliest debt
for each client. Then which letter could be determined by subtracting the
current date from this earliest debt date. Then testing would begin at
more than 120 days, then more than 90 days, then more than 60 days and
then more then 30 days. I am not personally sure that you can nest these
if statements, but you might.
Dan
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
