Managing multiple running totals

[Hercali]

Hi guys,

I'm trying to solve the following problem (also avoiding using VBA).

I'm representing adding a new resource to a project and how capacity may not be achieved with that resource for X numbers of days.

The variables are the time it takes the resource to reach maximum capacity (static for all resources) and when a new resource is acquired.

As new resources can be added at different times their rate of decay for achieving maximum potential can overlap.

I have found a workaround that almost gets me there but it doesn't quite work (adding 3 overlapping resources for example).

Can anyone lend some assistance?

Thanks!

 

[Peter Bartholomew]

Sorry but I am struggling to follow what the calculation is.
The attached is simply the same sheet refactored in an attempt to understand what is going on .

 

[Peter Bartholomew]

@Hercali

I think your calculation is a special case of a convolution (mathematical term), sometimes known as a spread in finance.

I have somewhat butchered an old workbook of mine to make it apply to your problem rather than a financial schedule of payment. The file would allow for a non-linear acquisition of capacity during training.

The objective of the original work was to produce a solution without needing a 50x10 array of helper cells. The array formula is moderately straightforward to state but it would normally fail because INDEX doesn't return an array one can iterate over; coercion is required.

Then again, you could simply spent a bit of time experimenting with the workbook and not bothering about how it works. I would probably be a bit hard-pressed to explain it after the intervening years!