help with a formula

[Charles]

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

 

[T. Valko]

Will any of the date periods span into the next year?

Biff

 

[Charles]

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]

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]

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

 

[Charles]

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

 

[Charles]

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]

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]

The amount for line 4 in 2007 should be 15,625. And again thanks for your
help it is really appreciated.

 

[T. Valko]

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]

Great formula!! Thanks for your help...

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

 

[T. Valko]

You're welcome. Thanks for the feedback!

Biff

Great formula!! Thanks for your help...

 

[gromit]

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.