Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
% allocated over time varies
Need some help with allocating values over time based on percentages
Columns: Start | Finish | Duration (calculated) | Value | Jan08|Feb08|Mar08|Apr08|so on If the start date is 01/05/08 and finish date is 06/05/08 then the duration would be 6 (months). Then in each month column the start and finish encompass the Value would be based on a separate worksheet with percentages for each duration. So the other worksheet would look like this: 1 2 3 4 5 6 7 8 1 100% 2 35% 65% 3 20% 50% 30% 4 5 6 10% 15% 20% 20% 15% 20% So if the duration is 6 months and the value is 100, then month 1 would equal 100*.1 and month 2 100x.15 and so on Any way to use offset and indirect to automate this function? Thanks in advance |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
% allocated over time varies
All you need is VLOOKUP to get the percentage and have a formula like
IF(VLOOKUP(),VLOOKUP()*100,0) where VLOOKUP() will be in the following form; VLOOKUP(lookup_value,table_array,col_index_num,ran ge_lookup) Lookup_value - will be the duration Table_array - will be your range with percentages. Col_index_num - will be the month number Range_lookup - will be False "AK" wrote: Need some help with allocating values over time based on percentages Columns: Start | Finish | Duration (calculated) | Value | Jan08|Feb08|Mar08|Apr08|so on If the start date is 01/05/08 and finish date is 06/05/08 then the duration would be 6 (months). Then in each month column the start and finish encompass the Value would be based on a separate worksheet with percentages for each duration. So the other worksheet would look like this: 1 2 3 4 5 6 7 8 1 100% 2 35% 65% 3 20% 50% 30% 4 5 6 10% 15% 20% 20% 15% 20% So if the duration is 6 months and the value is 100, then month 1 would equal 100*.1 and month 2 100x.15 and so on Any way to use offset and indirect to automate this function? Thanks in advance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum of a range that varies | Excel Discussion (Misc queries) | |||
Font displayed varies from selection | Excel Discussion (Misc queries) | |||
Formula to see if a number is allocated | Excel Discussion (Misc queries) | |||
Weekly data allocated to months | Excel Worksheet Functions | |||
Weekly data allocated to months | Excel Worksheet Functions |