Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Allocate Value Across Monthly Periods
Dear All
I would like to do the following via a formula/formulas if possible, rather than using VBA. I would like to allocate a given value across its relevant period. For example, the headings row would be: cell A1 = "value", B1 = "from date", C1 = "to date", D1 = "Oct-09", E1 = "Nov-09" all the way to P1 = "Oct-10" Then in row2, A2 = 1200, B2 = "01-Jul-09", C2 = "31-Jun-10". In cells D2 to P2, I would like to enter formula/formulas to allocate the 1200 across the period in cells B2 and C2. The value in cell D2 should return 400 as it is a catchup of 4 months (Jul-Oct). The values in cells E2 to L2 should each show a value of 100, and the values in cells M2 to P2 should be zero. Please note: 1) the "from date" and "to date" are not always 12 month intervals, they could be 1 month, 3 months, etc 2) the value to be allocated in each month should be a standard monthly amount, not an amount based on the number of days in that specific month. 3) sometimes the start period would be in the future, so the first month's allocation might be in say "Dec-09", not "Oct-09". 4) it's fine to add another workings column if that makes life easier for the final formula. For example an extra column that calculates the number of months between cells B2 and C2 If anyone can help, that would be much appreciated. Thanks, AlanR p.s. in case it's relevant, I use the Analysis Toolpak add-in. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Allocate Value Across Monthly Periods
Try this formula in D2 and copy across..../down as required
=IF(AND(TEXT($B2,"yyyymm")<=TEXT(D$1,"yyyymm"),TEX T($C2,"yyyymm")=TEXT(D$1,"yyyymm")),IF(ISNUMBER(C $1),$A2/(DATEDIF($B2,$C2,"m")+1),(DATEDIF($B2,D$1,"m")+1)* $A2/(DATEDIF($B2,$C2,"m")+1)),0) If this post helps click Yes --------------- Jacob Skaria "AlanR" wrote: Dear All I would like to do the following via a formula/formulas if possible, rather than using VBA. I would like to allocate a given value across its relevant period. For example, the headings row would be: cell A1 = "value", B1 = "from date", C1 = "to date", D1 = "Oct-09", E1 = "Nov-09" all the way to P1 = "Oct-10" Then in row2, A2 = 1200, B2 = "01-Jul-09", C2 = "31-Jun-10". In cells D2 to P2, I would like to enter formula/formulas to allocate the 1200 across the period in cells B2 and C2. The value in cell D2 should return 400 as it is a catchup of 4 months (Jul-Oct). The values in cells E2 to L2 should each show a value of 100, and the values in cells M2 to P2 should be zero. Please note: 1) the "from date" and "to date" are not always 12 month intervals, they could be 1 month, 3 months, etc 2) the value to be allocated in each month should be a standard monthly amount, not an amount based on the number of days in that specific month. 3) sometimes the start period would be in the future, so the first month's allocation might be in say "Dec-09", not "Oct-09". 4) it's fine to add another workings column if that makes life easier for the final formula. For example an extra column that calculates the number of months between cells B2 and C2 If anyone can help, that would be much appreciated. Thanks, AlanR p.s. in case it's relevant, I use the Analysis Toolpak add-in. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Allocate from the sum | Excel Worksheet Functions | |||
Comparing data from 2 monthly periods in 1 chart | Charts and Charting in Excel | |||
How to subtract/add periods in monthly base? | Excel Discussion (Misc queries) | |||
Number of semi-monthly periods between 2 dates | Excel Worksheet Functions | |||
Mortgage template comparing interest pd, monthly, bi-monthly, ext. | Excel Discussion (Misc queries) |