One Cost Divided Among Multiple Date Ranges

[cardan]

I am trying to break out a cost with multiple date ranges. For example,
I have a single cost of $120 that will be broken out into multiple
phases throughout the year. I would like to break this cost out into
phases that will occur with different ranges throughout the year. To
illustrate my example, please see below:

This is my setup and manual coding area: I apologize if the alignment
is off due to uploading..


Phase 1 Phase 2 Phase 3
Amount Beg Month End Month Beg Month End Month Beg Month
End Month
$120 Jan-07 Mar-07 May-07 May-07 Aug-07
Nov-07


Since there are a total of 8 months for this cost, I need to divide the
amount by 8 and then put them into the corresponding months. The end
product is to have the costs under the appropriate month, looking
something like this:

Jan Feb Mar Apr May Jun Jul Aug Sep Oct
Nov Dec
$15 $15 $15 $15 $15 $15 $15 $15



I have the formula for calculating the total months and then it is just
a division problem for the amount per month. I am having problems
putting the ranges into one formula. Another issue is that I will end
up with around 10 phases so IF functions may not work since I can nest
only 7 "IF"'s. Is it possible to get this type of equation in one
line? The model is being built so all the $ Amount, Actual Beg-End
Months and the final $ under the month are all on the same row in the
same worksheet? Any suggestions? Thank you for your help. It is
greatly appreciated!

 

[Carim]

Hi,

If I understand correctly, you would be better off :
1. having a count formula which takes care of the exact number of
months
2. dividing your cost total by this number for each month
3. using If function only to determine if the respective month is
available or not

HTH
Cheers
Carim

 

[cardan]

Hi Carim, Thanks for responding. What you mentioned is exactly what I
am trying to do. I have already broken out the total number of months
in a seperate cell so figuring out when to allocate that cost under the
particular month is where I am stuck. However, I have around 10
"phases" so I don't think I can use the IF function. Do you think
there is a way I can use the IF function or is there a simpler formula?
Thank you again for your response.

Best regards,

Dan

 

[Carim]

Hi Dan,

Could you be a bit more precise regarding your allocation key ?
It looks as if it is only the total amount divided by a variable number
of months ...

Cheers
Carim

 

[cardan]

Hi Carim,

I am not quite sure what you mean by allocation key (somewhat new to
this). I tried to simplify what I showed to make it easier. If it is
needed, I can put the total number of months in a different cell. That
way, I only have to divide the amount and the cell with the total
months calculated. However, if there is a way to count and allocate
within the same formula, that can work as well. Essentially I am trying
to:
1. Find the total months within all phases (which can already be
calculated in another cell)
2. Divide the Amount by the the total months.
3. Allocate that cost to the months that are within the multiple date
ranges.

Does this help? Thanks again!

Cheers,

Dan

 

[Carim]

Dan,

It is a bit difficult to understand your "phase-month" relationship ...
The allocation key means how do you divide your total cost ... by
phases, by months, etc ...
If you are not reluctant to it, you can send me your spreadsheet, I
could take a look at it, and propose a solution ...
(e-mail address removed) remove nospam from email ...

Cheers
Carim