Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with formula
I have racked my brain to try and figure this out. I have a worksheet with
the following I am having trouble with the ROUNDDOWN part of formula in Columna J. Col E: Monthly Pmt Col G: Total to be paid back Col H: Amt Pd so far =IF(TODAY()<=R30,MIN(G30,E30*J30),MIN(G30,E30*J30) ) Col I: Balance =IF(H30="","",G30-H30) Col J: Pmts made =IF(TODAY()<$Q30,0,IF(TODAY()$R30,ROUND((($R30-$Q30)/30) +1,0),ROUNDDOWN(((TODAY()-Q30)/30),0))) Col K: Pmts remaining =IF(M30="N",ROUNDUP(G30/E30,0)-J30,"")) Col Q: Start Date Col R: End Date The formula shown shows the correct value in Column J for example 1, but not for example 2. I want Col J (pmts made) to equal 1 if TODAY() = Start Date but Col J does not equal 1 until 30 days after the start date. EXAMPLE 1: Pmt = $250 Total To Be Paid: $23,227.19 Start Date: 9/20/02 Pmts Made (as of 12/20/08) = 76 Pmts Remaining (as of 12/20/08) = 17 Example 2: This is a dummy example I am using to try and fix the problem Pmt = $500 Total to be Paid: $5,000 Start Date = today's date (whatever date that is) I want the Pmts Made (Col J) to equal 1 on the start date and to increase by one each month after that. I can't figure out why the formula works for Example 1 and not Example 2. If I fix it so example 2 works, then the PMTS MADE in Example one goes to 77, instead of staying at 76. NOTE: These are not calculation that involved knowing interest rates, but simply the TOTAL amount to be paid. Your help is GREATLY appreciated. Les |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with formula
You say our help is GREATLY appreciated, but your walk doesn't match your
talk. In the spirit of Christmas, we'll try again. As a small point, your formula in Column H, you don't need the If statement at all, because you're using the same formula for both the true and false parts. To your main question, you state your fomula won't return 1 when today equals Q30. But your formula is: (Today()-Q30)/30. Obviously, when Q30 equals today, then today-q30 will be zero. To rectify this problem, add one to the result. This will then get you to your true problem -- your formulas are assuming there are 30 days in a month, which, of course, is not the case. Between 9/20/02 and 12/20/08 there are 75 months, and there are 2283 days. When you divide 2283 by 30, you get 76, which makes you think the formula is working. It's "working" only because there are 6 years between the two dates. Every year, there are 5 more days in a year than 12 30-day months, so after 6 years, your formula will add a month. Your choices a 1. Properly calculate the number of months between two dates. 2. Use the Days360 function to calculate the number of days between two dates. Regards, Fred. "WLMPilot" wrote in message ... I have racked my brain to try and figure this out. I have a worksheet with the following I am having trouble with the ROUNDDOWN part of formula in Columna J. Col E: Monthly Pmt Col G: Total to be paid back Col H: Amt Pd so far =IF(TODAY()<=R30,MIN(G30,E30*J30),MIN(G30,E30*J30) ) Col I: Balance =IF(H30="","",G30-H30) Col J: Pmts made =IF(TODAY()<$Q30,0,IF(TODAY()$R30,ROUND((($R30-$Q30)/30) +1,0),ROUNDDOWN(((TODAY()-Q30)/30),0))) Col K: Pmts remaining =IF(M30="N",ROUNDUP(G30/E30,0)-J30,"")) Col Q: Start Date Col R: End Date The formula shown shows the correct value in Column J for example 1, but not for example 2. I want Col J (pmts made) to equal 1 if TODAY() = Start Date but Col J does not equal 1 until 30 days after the start date. EXAMPLE 1: Pmt = $250 Total To Be Paid: $23,227.19 Start Date: 9/20/02 Pmts Made (as of 12/20/08) = 76 Pmts Remaining (as of 12/20/08) = 17 Example 2: This is a dummy example I am using to try and fix the problem Pmt = $500 Total to be Paid: $5,000 Start Date = today's date (whatever date that is) I want the Pmts Made (Col J) to equal 1 on the start date and to increase by one each month after that. I can't figure out why the formula works for Example 1 and not Example 2. If I fix it so example 2 works, then the PMTS MADE in Example one goes to 77, instead of staying at 76. NOTE: These are not calculation that involved knowing interest rates, but simply the TOTAL amount to be paid. Your help is GREATLY appreciated. Les |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with formula
Hi,
The undocumented DATEDIF function has the following form: DATEDIF(StartDate,EndDate,Unit) Where Units are on the left in the table below and their results on the right. Note that the Unit must be quoted - "y" for example. y Whole years between two dates m Whole months between two dates d Whole days between two dates md Number of days between two dates ignoring months ym Number of months between two dates ignoring years yd Number of days between two dates ignoring years =DATEDIF(A1,A2,"Y") returns the number of whole years between the dates in cell A1 and A2 If this helps, please click the Yes button. Cheers, Shane Devenshire "WLMPilot" wrote in message ... I have racked my brain to try and figure this out. I have a worksheet with the following I am having trouble with the ROUNDDOWN part of formula in Columna J. Col E: Monthly Pmt Col G: Total to be paid back Col H: Amt Pd so far =IF(TODAY()<=R30,MIN(G30,E30*J30),MIN(G30,E30*J30) ) Col I: Balance =IF(H30="","",G30-H30) Col J: Pmts made =IF(TODAY()<$Q30,0,IF(TODAY()$R30,ROUND((($R30-$Q30)/30) +1,0),ROUNDDOWN(((TODAY()-Q30)/30),0))) Col K: Pmts remaining =IF(M30="N",ROUNDUP(G30/E30,0)-J30,"")) Col Q: Start Date Col R: End Date The formula shown shows the correct value in Column J for example 1, but not for example 2. I want Col J (pmts made) to equal 1 if TODAY() = Start Date but Col J does not equal 1 until 30 days after the start date. EXAMPLE 1: Pmt = $250 Total To Be Paid: $23,227.19 Start Date: 9/20/02 Pmts Made (as of 12/20/08) = 76 Pmts Remaining (as of 12/20/08) = 17 Example 2: This is a dummy example I am using to try and fix the problem Pmt = $500 Total to be Paid: $5,000 Start Date = today's date (whatever date that is) I want the Pmts Made (Col J) to equal 1 on the start date and to increase by one each month after that. I can't figure out why the formula works for Example 1 and not Example 2. If I fix it so example 2 works, then the PMTS MADE in Example one goes to 77, instead of staying at 76. NOTE: These are not calculation that involved knowing interest rates, but simply the TOTAL amount to be paid. Your help is GREATLY appreciated. Les |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with formula
Hey Fred,
Somehow I lost the thread link. I was not able to link to it via the email I get when you respond. Thanks for your help. I took your suggestions in this response and researched, which pointed me to the answer I needed. Sorry it has taken a while to respond, but I could not find the link and I happen to run across it today. Thanks again, Les "Fred Smith" wrote: You say our help is GREATLY appreciated, but your walk doesn't match your talk. In the spirit of Christmas, we'll try again. As a small point, your formula in Column H, you don't need the If statement at all, because you're using the same formula for both the true and false parts. To your main question, you state your fomula won't return 1 when today equals Q30. But your formula is: (Today()-Q30)/30. Obviously, when Q30 equals today, then today-q30 will be zero. To rectify this problem, add one to the result. This will then get you to your true problem -- your formulas are assuming there are 30 days in a month, which, of course, is not the case. Between 9/20/02 and 12/20/08 there are 75 months, and there are 2283 days. When you divide 2283 by 30, you get 76, which makes you think the formula is working. It's "working" only because there are 6 years between the two dates. Every year, there are 5 more days in a year than 12 30-day months, so after 6 years, your formula will add a month. Your choices a 1. Properly calculate the number of months between two dates. 2. Use the Days360 function to calculate the number of days between two dates. Regards, Fred. "WLMPilot" wrote in message ... I have racked my brain to try and figure this out. I have a worksheet with the following I am having trouble with the ROUNDDOWN part of formula in Columna J. Col E: Monthly Pmt Col G: Total to be paid back Col H: Amt Pd so far =IF(TODAY()<=R30,MIN(G30,E30*J30),MIN(G30,E30*J30) ) Col I: Balance =IF(H30="","",G30-H30) Col J: Pmts made =IF(TODAY()<$Q30,0,IF(TODAY()$R30,ROUND((($R30-$Q30)/30) +1,0),ROUNDDOWN(((TODAY()-Q30)/30),0))) Col K: Pmts remaining =IF(M30="N",ROUNDUP(G30/E30,0)-J30,"")) Col Q: Start Date Col R: End Date The formula shown shows the correct value in Column J for example 1, but not for example 2. I want Col J (pmts made) to equal 1 if TODAY() = Start Date but Col J does not equal 1 until 30 days after the start date. EXAMPLE 1: Pmt = $250 Total To Be Paid: $23,227.19 Start Date: 9/20/02 Pmts Made (as of 12/20/08) = 76 Pmts Remaining (as of 12/20/08) = 17 Example 2: This is a dummy example I am using to try and fix the problem Pmt = $500 Total to be Paid: $5,000 Start Date = today's date (whatever date that is) I want the Pmts Made (Col J) to equal 1 on the start date and to increase by one each month after that. I can't figure out why the formula works for Example 1 and not Example 2. If I fix it so example 2 works, then the PMTS MADE in Example one goes to 77, instead of staying at 76. NOTE: These are not calculation that involved knowing interest rates, but simply the TOTAL amount to be paid. Your help is GREATLY appreciated. Les |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|