Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
One Cost Divided Among Multiple Date Ranges
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
One Cost Divided Among Multiple Date Ranges
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
One Cost Divided Among Multiple Date Ranges
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 wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
One Cost Divided Among Multiple Date Ranges
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
One Cost Divided Among Multiple Date Ranges
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 wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
One Cost Divided Among Multiple Date Ranges
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 ... remove nospam from email ... Cheers Carim |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Line Chart with multiple series that have different date ranges | Charts and Charting in Excel | |||
Creating a total cost chart where a component cost is a step cost | Charts and Charting in Excel | |||
Displaying date ranges for multiple years | Charts and Charting in Excel | |||
Summary of Multiple Date Ranges | Excel Programming | |||
Sum cost column based on date column and vlookup cost | Excel Programming |