Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy formulas to different cells in another worksheet | Excel Discussion (Misc queries) | |||
Search/Match between 2 x separate Worksheets and populate result in third worksheet | Excel Discussion (Misc queries) | |||
Copy from worksheet to another x times | Excel Discussion (Misc queries) | |||
Dynamic Formulas with Dynamic Ranges | Excel Worksheet Functions | |||
Weekly Transaction Processing | Excel Worksheet Functions |