Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
i just want to allocate $ by month, for many projects
I want to allocate $1000 by month based on the available days in each month
depending on when a project starts based until that the project is complete. If i need more days beyond the first month, i want excel to move on to the next month and so on and use whatever available days are necessary and calculate the $ allocation of the project. my sheet looks like this: First 5 columns of the spreadsheet: Project Project Start Amount Start Finish QTR Workdays $/Day $1,000 1/10/08 4/10/08 QTR 2 66 $15 Note: I wrote a formula to calculate the start qtr based on my fiscal calendar. Also, the workdays are auto calculated using "Networkdays" based on project start and project finish. Also, the $/Day is a simple automatic division the the Amount and the Workdays. I want to allocate the $15 a day x the number of days available to work in the month and place it in the monthly cell. So, i have to allocate 66 days of work at $15 per day. My fiscal months vary and do not track to Gregorian calendar months. The next 5 columns of the spreadsheet: Month Jan Feb Mar Apr Start 1/7/08 2/4/08 3/3/08 4/7/08 Stop 2/3/08 3/2/08 4/6/08 5/4/08 Networkdays 20 20 25 20 Note: The network days are automatically calculated using Networkdays formula. Because of my fiscal month situation, January in this case has only 17 work days available to me because of when this specific project starts. So, i want to multiply the $19 x 17 days and automatically put that $ amount in Jan. In Feb I have 20 days available and need all of them so i want to multiply $19 x 20. Now i have used 37 days so i need all of Marchs' 25 days and want $15 x 25 in the mar cell. Now I only need 4 days to use up all the 66 days of the project so i want to put $15 x 4 in Apr. Now i have automatically allocate the entire project. I the idea is to allocate all the $ using the available days in the month by using the total project days available to me. I want all the cells for all the months to be the same so i can copy them down the spreadsheet and use the formula for multiple projects. In this example may-dec should say $0. If my project should start in may then jan-apr should return 0$. Can you help me? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to allocate based on seniority | Excel Discussion (Misc queries) | |||
allocate different colors to each number | Excel Worksheet Functions | |||
How to allocate the XLODBC.XLA software? | Excel Discussion (Misc queries) | |||
Allocate a value to a cell from an if statement | Excel Worksheet Functions | |||
Allocate workload evenly to different staff | Excel Worksheet Functions |