Do I need a macro or just a function?

[Kara]

I am working with a spreadsheet that caculates tuition balances for a small
school, about 90 entries. I have it all working just fine with regular
formulas, that is all but one. I would like to see at any given time how
delinquent the families are with their monthly tuition. Every family usually
pays in a 10 month period, but most have different amounts that they pay each
month. If needed I can e-mail the spreadsheet. I believe it will need to be a
date driven formula. Right now what I do to get this amount is to take the
total tuition subtract what their monthly payments would be and come up with
the balance. Then I compare that number to the acutal amount paid in and the
different would be delinquent tuition (or overage for the year). Only problem
I don't have a clue how to do it.

 

[Bob Phillips]

It seems a formula would do it, but we need to see sample data. Post it here
in text form, and try and lay it so that it is readable.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[joeu2004]

I would like to see at any given time how delinquent the
families are with their monthly tuition. Every family usually
pays in a 10 month period, but most have different amounts
that they pay each month.

Suppose you have the following:

A1: current date: =today()
A2: starting date
A3: ending date
A4: total number of monthly payments:
=month(A3) - month(A2) + 1 + 12*(year(A3) > year(A2))
A5: number of expected monthly payments to date:
=month(A1) - month(A2) + 1 + 12*(year(A1) > year(A2))

Note: You might need to change some cell formats, especially
A1, A4 and A5.

Suppose the list of students begins in row 8. You might have
the following:

A8: student's name
B8: total tuition
C8: tuition due per month: =roundup(B8/A4, 0)
D8: tuition paid to date
E8: amount due this month: =-max(0, min(C8*A5 - D8, B8 - D8))
F8: total remaining balance to date: =B8-D8

You might want to format E8 and F8 as Number red (1234).
The min() function computes the lesser of the monthly payment
plus deliquency and the remaining balance. The max() function
returns 0 if payments are current to date.

 

[Kara]

The info below shows rows 1-5, columns A-Q. This is the best I could do to
get it here in a text format.


Family Name Tuition Amount Owed September-05
$1,200.00 $300.00
$3,250.00 $325.00
$2,225.00 $220.00
$1,200.00 $120.00


October-05 November-05 December-05 January-06
$0.00 $300.00 $0.00 $0.00
$325.00 $325.00 $325.00 $325.00
$0.00 $222.50 $440.00 $222.50
$120.00 $120.00 $120.00
$120.00

Feb-06 March-06 April-06 May-06 June-06 July-06
$600.00
$325.00 $325.00
$222.50
$120.00

August-06 Delinquent Tuition Amount PD In Amount Due
$0.00 $1,200.00 0.00
$0.00 $2,600.00 $650.00
$230.00 $1,327.50 $897.50
$0.00 $840.00 $360.00