Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Do I need a macro or just a function?
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Do I need a macro or just a function?
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) "Kara" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Do I need a macro or just a function?
"Kara" wrote:
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Do I need a macro or just a function?
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 "Kara" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Custom functions calculating time arguments Help Desperate | Excel Worksheet Functions | |||
If than copy function / Macro | Excel Worksheet Functions | |||
Using "mailto" function in a macro | Excel Discussion (Misc queries) | |||
Excel option to store trendline's coefficients in cells for use | Charts and Charting in Excel | |||
Function Macro for Nested IF | Excel Worksheet Functions |