|
help with a formula
I have the need to create a formula that calculates the number of months
between cell B3 and C3 then divide that number by the amount in cell A1 then find the €śstart date€ť month and put the value for each month until the "end date" is reached. I hope this makes since, and I appreciate any help you can give me!! 1 A B C D E F 2 Amount start date end date Jan-07 Feb-07 Mar-07 3 $ 100,000 Feb-07 Sep-07 Thanks, Charles |
help with a formula
Will any of the date periods span into the next year?
Biff "Charles" wrote in message ... I have the need to create a formula that calculates the number of months between cell B3 and C3 then divide that number by the amount in cell A1 then find the "start date" month and put the value for each month until the "end date" is reached. I hope this makes since, and I appreciate any help you can give me!! 1 A B C D E F 2 Amount start date end date Jan-07 Feb-07 Mar-07 3 $ 100,000 Feb-07 Sep-07 Thanks, Charles |
help with a formula
Yes, it is for a 3 year forecast, theoretically there could also be some that
goes past the three years therefore that amount would not show up under any date in the forecast. "T. Valko" wrote: Will any of the date periods span into the next year? Biff "Charles" wrote in message ... I have the need to create a formula that calculates the number of months between cell B3 and C3 then divide that number by the amount in cell A1 then find the "start date" month and put the value for each month until the "end date" is reached. I hope this makes since, and I appreciate any help you can give me!! 1 A B C D E F 2 Amount start date end date Jan-07 Feb-07 Mar-07 3 $ 100,000 Feb-07 Sep-07 Thanks, Charles |
help with a formula
Ok, if I understand you that means if the end date is greater than the last
date in row 2 no forecast is calculated. It may be easier to show you how I did this in a sample file. Sample file: Charles.xls 16kb http://cjoint.com/?fxtzLpjmPc I only plotted through the end of 2007. Note how I use the full date. I entered the formula in cell D3 then copied across then down. Biff "Charles" wrote in message ... Yes, it is for a 3 year forecast, theoretically there could also be some that goes past the three years therefore that amount would not show up under any date in the forecast. "T. Valko" wrote: Will any of the date periods span into the next year? Biff "Charles" wrote in message ... I have the need to create a formula that calculates the number of months between cell B3 and C3 then divide that number by the amount in cell A1 then find the "start date" month and put the value for each month until the "end date" is reached. I hope this makes since, and I appreciate any help you can give me!! 1 A B C D E F 2 Amount start date end date Jan-07 Feb-07 Mar-07 3 $ 100,000 Feb-07 Sep-07 Thanks, Charles |
help with a formula
Wow very nice formula, not being a programmer I could have never even got
close. I do have one question, I think I miss communicated the part of if the amount goes past the forecast I still need it to put the amount for the months on the forecast and the overflow amount will not be shown. Is that an easy fix? I really appreciate your help! Charles "T. Valko" wrote: Ok, if I understand you that means if the end date is greater than the last date in row 2 no forecast is calculated. It may be easier to show you how I did this in a sample file. Sample file: Charles.xls 16kb http://cjoint.com/?fxtzLpjmPc I only plotted through the end of 2007. Note how I use the full date. I entered the formula in cell D3 then copied across then down. Biff "Charles" wrote in message ... Yes, it is for a 3 year forecast, theoretically there could also be some that goes past the three years therefore that amount would not show up under any date in the forecast. "T. Valko" wrote: Will any of the date periods span into the next year? Biff "Charles" wrote in message ... I have the need to create a formula that calculates the number of months between cell B3 and C3 then divide that number by the amount in cell A1 then find the "start date" month and put the value for each month until the "end date" is reached. I hope this makes since, and I appreciate any help you can give me!! 1 A B C D E F 2 Amount start date end date Jan-07 Feb-07 Mar-07 3 $ 100,000 Feb-07 Sep-07 Thanks, Charles |
help with a formula
Wow very nice formula, not being a programmer I could have never even got
close. I do have one question, I think I miss communicated the part of if the amount goes past the forecast I still need it to put the amount for the months on the forecast and the overflow amount will not be shown. Is that an easy fix? I really appreciate your help! Charles "T. Valko" wrote: Ok, if I understand you that means if the end date is greater than the last date in row 2 no forecast is calculated. It may be easier to show you how I did this in a sample file. Sample file: Charles.xls 16kb http://cjoint.com/?fxtzLpjmPc I only plotted through the end of 2007. Note how I use the full date. I entered the formula in cell D3 then copied across then down. Biff "Charles" wrote in message ... Yes, it is for a 3 year forecast, theoretically there could also be some that goes past the three years therefore that amount would not show up under any date in the forecast. "T. Valko" wrote: Will any of the date periods span into the next year? Biff "Charles" wrote in message ... I have the need to create a formula that calculates the number of months between cell B3 and C3 then divide that number by the amount in cell A1 then find the "start date" month and put the value for each month until the "end date" is reached. I hope this makes since, and I appreciate any help you can give me!! 1 A B C D E F 2 Amount start date end date Jan-07 Feb-07 Mar-07 3 $ 100,000 Feb-07 Sep-07 Thanks, Charles |
help with a formula
Wow very nice formula, not being a programmer I could have never even got
close. I do have one question, I think I miss communicated the part of if the amount goes past the forecast I still need it to put the amount for the months on the forecast and the overflow amount will not be shown. Is that an easy fix? I really appreciate your help! Charles "T. Valko" wrote: Ok, if I understand you that means if the end date is greater than the last date in row 2 no forecast is calculated. It may be easier to show you how I did this in a sample file. Sample file: Charles.xls 16kb http://cjoint.com/?fxtzLpjmPc I only plotted through the end of 2007. Note how I use the full date. I entered the formula in cell D3 then copied across then down. Biff "Charles" wrote in message ... Yes, it is for a 3 year forecast, theoretically there could also be some that goes past the three years therefore that amount would not show up under any date in the forecast. "T. Valko" wrote: Will any of the date periods span into the next year? Biff "Charles" wrote in message ... I have the need to create a formula that calculates the number of months between cell B3 and C3 then divide that number by the amount in cell A1 then find the "start date" month and put the value for each month until the "end date" is reached. I hope this makes since, and I appreciate any help you can give me!! 1 A B C D E F 2 Amount start date end date Jan-07 Feb-07 Mar-07 3 $ 100,000 Feb-07 Sep-07 Thanks, Charles |
help with a formula
I still need it to put the amount for the months on the
forecast and the overflow amount will not be shown. Using the sample file, does that mean on row 4 where the end date is 2/1/2008 the forecast will fill in from 7/1/2007 to 12/1/2007? If that's the case would the forecast still divide the amount by the total months from 7/12007 to 2/1/2008 or would it just divide by the number of months from 7/1/2007 to 12/1/2007? Better yet, using the sample file, tell me what the results should be on row 4. I'll be able to figure it out from there! Biff "Charles" wrote in message ... Wow very nice formula, not being a programmer I could have never even got close. I do have one question, I think I miss communicated the part of if the amount goes past the forecast I still need it to put the amount for the months on the forecast and the overflow amount will not be shown. Is that an easy fix? I really appreciate your help! Charles "T. Valko" wrote: Ok, if I understand you that means if the end date is greater than the last date in row 2 no forecast is calculated. It may be easier to show you how I did this in a sample file. Sample file: Charles.xls 16kb http://cjoint.com/?fxtzLpjmPc I only plotted through the end of 2007. Note how I use the full date. I entered the formula in cell D3 then copied across then down. Biff "Charles" wrote in message ... Yes, it is for a 3 year forecast, theoretically there could also be some that goes past the three years therefore that amount would not show up under any date in the forecast. "T. Valko" wrote: Will any of the date periods span into the next year? Biff "Charles" wrote in message ... I have the need to create a formula that calculates the number of months between cell B3 and C3 then divide that number by the amount in cell A1 then find the "start date" month and put the value for each month until the "end date" is reached. I hope this makes since, and I appreciate any help you can give me!! 1 A B C D E F 2 Amount start date end date Jan-07 Feb-07 Mar-07 3 $ 100,000 Feb-07 Sep-07 Thanks, Charles |
help with a formula
The amount for line 4 in 2007 should be 15,625. And again thanks for your
help it is really appreciated. "T. Valko" wrote: I still need it to put the amount for the months on the forecast and the overflow amount will not be shown. Using the sample file, does that mean on row 4 where the end date is 2/1/2008 the forecast will fill in from 7/1/2007 to 12/1/2007? If that's the case would the forecast still divide the amount by the total months from 7/12007 to 2/1/2008 or would it just divide by the number of months from 7/1/2007 to 12/1/2007? Better yet, using the sample file, tell me what the results should be on row 4. I'll be able to figure it out from there! Biff "Charles" wrote in message ... Wow very nice formula, not being a programmer I could have never even got close. I do have one question, I think I miss communicated the part of if the amount goes past the forecast I still need it to put the amount for the months on the forecast and the overflow amount will not be shown. Is that an easy fix? I really appreciate your help! Charles "T. Valko" wrote: Ok, if I understand you that means if the end date is greater than the last date in row 2 no forecast is calculated. It may be easier to show you how I did this in a sample file. Sample file: Charles.xls 16kb http://cjoint.com/?fxtzLpjmPc I only plotted through the end of 2007. Note how I use the full date. I entered the formula in cell D3 then copied across then down. Biff "Charles" wrote in message ... Yes, it is for a 3 year forecast, theoretically there could also be some that goes past the three years therefore that amount would not show up under any date in the forecast. "T. Valko" wrote: Will any of the date periods span into the next year? Biff "Charles" wrote in message ... I have the need to create a formula that calculates the number of months between cell B3 and C3 then divide that number by the amount in cell A1 then find the "start date" month and put the value for each month until the "end date" is reached. I hope this makes since, and I appreciate any help you can give me!! 1 A B C D E F 2 Amount start date end date Jan-07 Feb-07 Mar-07 3 $ 100,000 Feb-07 Sep-07 Thanks, Charles |
help with a formula
Ok, that actually makes it a little easier!
Enter this formula in D3: =IF(AND(D$2=$B3,D$2<=$C3),$A3/(DATEDIF($B3,$C3,"m")+1),"") Copied across then down as needed. Biff "Charles" wrote in message ... The amount for line 4 in 2007 should be 15,625. And again thanks for your help it is really appreciated. "T. Valko" wrote: I still need it to put the amount for the months on the forecast and the overflow amount will not be shown. Using the sample file, does that mean on row 4 where the end date is 2/1/2008 the forecast will fill in from 7/1/2007 to 12/1/2007? If that's the case would the forecast still divide the amount by the total months from 7/12007 to 2/1/2008 or would it just divide by the number of months from 7/1/2007 to 12/1/2007? Better yet, using the sample file, tell me what the results should be on row 4. I'll be able to figure it out from there! Biff "Charles" wrote in message ... Wow very nice formula, not being a programmer I could have never even got close. I do have one question, I think I miss communicated the part of if the amount goes past the forecast I still need it to put the amount for the months on the forecast and the overflow amount will not be shown. Is that an easy fix? I really appreciate your help! Charles "T. Valko" wrote: Ok, if I understand you that means if the end date is greater than the last date in row 2 no forecast is calculated. It may be easier to show you how I did this in a sample file. Sample file: Charles.xls 16kb http://cjoint.com/?fxtzLpjmPc I only plotted through the end of 2007. Note how I use the full date. I entered the formula in cell D3 then copied across then down. Biff "Charles" wrote in message ... Yes, it is for a 3 year forecast, theoretically there could also be some that goes past the three years therefore that amount would not show up under any date in the forecast. "T. Valko" wrote: Will any of the date periods span into the next year? Biff "Charles" wrote in message ... I have the need to create a formula that calculates the number of months between cell B3 and C3 then divide that number by the amount in cell A1 then find the "start date" month and put the value for each month until the "end date" is reached. I hope this makes since, and I appreciate any help you can give me!! 1 A B C D E F 2 Amount start date end date Jan-07 Feb-07 Mar-07 3 $ 100,000 Feb-07 Sep-07 Thanks, Charles |
help with a formula
Great formula!! Thanks for your help...
"T. Valko" wrote: Ok, that actually makes it a little easier! Enter this formula in D3: =IF(AND(D$2=$B3,D$2<=$C3),$A3/(DATEDIF($B3,$C3,"m")+1),"") Copied across then down as needed. Biff "Charles" wrote in message ... The amount for line 4 in 2007 should be 15,625. And again thanks for your help it is really appreciated. "T. Valko" wrote: I still need it to put the amount for the months on the forecast and the overflow amount will not be shown. Using the sample file, does that mean on row 4 where the end date is 2/1/2008 the forecast will fill in from 7/1/2007 to 12/1/2007? If that's the case would the forecast still divide the amount by the total months from 7/12007 to 2/1/2008 or would it just divide by the number of months from 7/1/2007 to 12/1/2007? Better yet, using the sample file, tell me what the results should be on row 4. I'll be able to figure it out from there! Biff "Charles" wrote in message ... Wow very nice formula, not being a programmer I could have never even got close. I do have one question, I think I miss communicated the part of if the amount goes past the forecast I still need it to put the amount for the months on the forecast and the overflow amount will not be shown. Is that an easy fix? I really appreciate your help! Charles "T. Valko" wrote: Ok, if I understand you that means if the end date is greater than the last date in row 2 no forecast is calculated. It may be easier to show you how I did this in a sample file. Sample file: Charles.xls 16kb http://cjoint.com/?fxtzLpjmPc I only plotted through the end of 2007. Note how I use the full date. I entered the formula in cell D3 then copied across then down. Biff "Charles" wrote in message ... Yes, it is for a 3 year forecast, theoretically there could also be some that goes past the three years therefore that amount would not show up under any date in the forecast. "T. Valko" wrote: Will any of the date periods span into the next year? Biff "Charles" wrote in message ... I have the need to create a formula that calculates the number of months between cell B3 and C3 then divide that number by the amount in cell A1 then find the "start date" month and put the value for each month until the "end date" is reached. I hope this makes since, and I appreciate any help you can give me!! 1 A B C D E F 2 Amount start date end date Jan-07 Feb-07 Mar-07 3 $ 100,000 Feb-07 Sep-07 Thanks, Charles |
help with a formula
You're welcome. Thanks for the feedback!
Biff "Charles" wrote in message ... Great formula!! Thanks for your help... "T. Valko" wrote: Ok, that actually makes it a little easier! Enter this formula in D3: =IF(AND(D$2=$B3,D$2<=$C3),$A3/(DATEDIF($B3,$C3,"m")+1),"") Copied across then down as needed. Biff "Charles" wrote in message ... The amount for line 4 in 2007 should be 15,625. And again thanks for your help it is really appreciated. "T. Valko" wrote: I still need it to put the amount for the months on the forecast and the overflow amount will not be shown. Using the sample file, does that mean on row 4 where the end date is 2/1/2008 the forecast will fill in from 7/1/2007 to 12/1/2007? If that's the case would the forecast still divide the amount by the total months from 7/12007 to 2/1/2008 or would it just divide by the number of months from 7/1/2007 to 12/1/2007? Better yet, using the sample file, tell me what the results should be on row 4. I'll be able to figure it out from there! Biff "Charles" wrote in message ... Wow very nice formula, not being a programmer I could have never even got close. I do have one question, I think I miss communicated the part of if the amount goes past the forecast I still need it to put the amount for the months on the forecast and the overflow amount will not be shown. Is that an easy fix? I really appreciate your help! Charles "T. Valko" wrote: Ok, if I understand you that means if the end date is greater than the last date in row 2 no forecast is calculated. It may be easier to show you how I did this in a sample file. Sample file: Charles.xls 16kb http://cjoint.com/?fxtzLpjmPc I only plotted through the end of 2007. Note how I use the full date. I entered the formula in cell D3 then copied across then down. Biff "Charles" wrote in message ... Yes, it is for a 3 year forecast, theoretically there could also be some that goes past the three years therefore that amount would not show up under any date in the forecast. "T. Valko" wrote: Will any of the date periods span into the next year? Biff "Charles" wrote in message ... I have the need to create a formula that calculates the number of months between cell B3 and C3 then divide that number by the amount in cell A1 then find the "start date" month and put the value for each month until the "end date" is reached. I hope this makes since, and I appreciate any help you can give me!! 1 A B C D E F 2 Amount start date end date Jan-07 Feb-07 Mar-07 3 $ 100,000 Feb-07 Sep-07 Thanks, Charles |
help with a formula
Dear T.Valko
Is there any way you can send or link me to the file you were referring to? I have the same question as Charlie, only my start and end dates span more than one calendar year. "T. Valko" wrote: Ok, if I understand you that means if the end date is greater than the last date in row 2 no forecast is calculated. It may be easier to show you how I did this in a sample file. Sample file: Charles.xls 16kb http://cjoint.com/?fxtzLpjmPc I only plotted through the end of 2007. Note how I use the full date. I entered the formula in cell D3 then copied across then down. Biff "Charles" wrote in message ... Yes, it is for a 3 year forecast, theoretically there could also be some that goes past the three years therefore that amount would not show up under any date in the forecast. "T. Valko" wrote: Will any of the date periods span into the next year? Biff "Charles" wrote in message ... I have the need to create a formula that calculates the number of months between cell B3 and C3 then divide that number by the amount in cell A1 then find the "start date" month and put the value for each month until the "end date" is reached. I hope this makes since, and I appreciate any help you can give me!! 1 A B C D E F 2 Amount start date end date Jan-07 Feb-07 Mar-07 3 $ 100,000 Feb-07 Sep-07 Thanks, Charles |
| All times are GMT +1. The time now is 12:26 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com