% 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

% 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