worksheet formulas
 

Hi
Can anyone help me to create a spreedsheet that will track payments and late
fees?
Here's the scenario:
A1=begining of month (8-01-05)
A2-12=clients
C=amount paid
D=date paid
I=previous balance
J=late fee (if paid after 10th of the month)
K=balance due
E=monthly payment

Now I would assume that the formulas will be several 2-part formulas
First part: I'm trying to get K(balance due) to find the result of C + J +
I(amount paid + late fee + previos balance) if D(date paid) is greater than
A1 by 10 days.
Second part: K(balance due) also need to reogonize that if none of E(monthly
payment) is paid to add E,J & I. Also J(late fee) need to recognize if
D(date paid) is after the 10th to add $25.

So here's the first scenario: once I type in the name(A2),the amount
paid(C2) and the date(D2), J2 should automatically implement a late fee and
K2 should automatically add C2, I2 & J2 because of D2.
A1=8/01/05
A2=Mr Jones
C2=$200
D2=8/11/05
E2=$400
I2=$25
J2=$25
K2=$250

Second scenario: Once I type in the name(A2), the amount paid(C2) and the
date(D2), J2 should automatically implement a late fee and K2 should
automatically add C2, E2, I2 & J2 because of C2's amout and D2.
A1=8/01/05
A2=Mr Jones
C2=$0
D2=8/11/05
E2=$400
I2=$25
J2=$25
K2=$450

I hope I haven't confused anyone it is a CHALLENGE! Pleas help me to do
this in the simpliest form possible. Thanks in advance.

Nadji,

I've attached a worksheet that does what you ask.

Be sure to use this link to get the file:
http://www.excelforum.com/attachment...achmentid=3724

Let me know what you think.


+-------------------------------------------------------------------+
|Filename: For_Nadji.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3724 |
+-------------------------------------------------------------------+

--
Excel_Geek


------------------------------------------------------------------------
Excel_Geek's Profile: http://www.excelforum.com/member.php...o&userid=26423
View this thread: http://www.excelforum.com/showthread...hreadid=398296

Thank you for the help but I need the formulas, so if someone happens to
screw it up I know how to reapply it.

"Excel_Geek" wrote:


Nadji,

I've attached a worksheet that does what you ask.

Be sure to use this link to get the file:
http://www.excelforum.com/attachment...achmentid=3724

Let me know what you think.


+-------------------------------------------------------------------+
|Filename: For_Nadji.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3724 |
+-------------------------------------------------------------------+

--
Excel_Geek


------------------------------------------------------------------------
Excel_Geek's Profile: http://www.excelforum.com/member.php...o&userid=26423
View this thread: http://www.excelforum.com/showthread...hreadid=398296

Just select any of the cells in columns J or K to see the formulas.

(For row 2)

Column J = "=IF(D2-$A$1=10,25,0)"
Column K = "=(E2-C2)+I2+J2"


--
Excel_Geek


------------------------------------------------------------------------
Excel_Geek's Profile: http://www.excelforum.com/member.php...o&userid=26423
View this thread: http://www.excelforum.com/showthread...hreadid=398296

Thank you so much, hopefully in the near future I will be able to call upon
you again.

"Excel_Geek" wrote:


Just select any of the cells in columns J or K to see the formulas.

(For row 2)

Column J = "=IF(D2-$A$1=10,25,0)"
Column K = "=(E2-C2)+I2+J2"


--
Excel_Geek


------------------------------------------------------------------------
Excel_Geek's Profile: http://www.excelforum.com/member.php...o&userid=26423
View this thread: http://www.excelforum.com/showthread...hreadid=398296