# Request formula to spread a value over time according to a start time

1. ## Request formula to spread a value over time according to a start time

Hi!

I'm looking for a formula in excel that helps me spreading a value over time based on a processing time and according to a specific start time.
E.g. A truck (cell A4) with 84 (cell B4) items on board arrives. The first item is being offloaded at 04h02 (cell C4) at a speed of 20 items per minute (cell D1). This means that 20 items are offloaded at 04h02, another 20 at 04h03, another 20 at 04h04, another 20 at 04h05 and the remaining 4 items are offloaded at 04h06.
Another example: a van with 19 items on board arrives for which the first item is offloaded at 04h06. This means that at 04h06 19 items are offloaded. At 04h07 no items are offloaded anymore as the van is already empty by that time.
Based on a offload starting time, the number of items on board and the processing time (= number of items to be offloaded per minute) I'm looking for a formula where excel can do this for me. I've attached an example how it should look like...but the formula is missing of course ;-)
Many thanks for your support on this!

Example.JPG

2. ## Re: Request formula to spread a value over time according to a start time

In E4, enter

=IF(ROUND(E\$3,6)>=ROUND(\$C4,6),IF(SUM(\$D4:D4)<\$B4,MIN(\$D\$1,\$B4-SUM(\$D4:D4)),""),"")

And then copy down and across to fill out your table.

Note that I had to use ROUND because how the times are created often changes the underlying value just slightly....

3. ## Re: Request formula to spread a value over time according to a start time

Hi Bernie!

Super!
This is the formula I was looking for.
I just had to replace all , with a ; and then it just worked fine.
Thanks a million. Much appreciated!

4. ## Re: Request formula to spread a value over time according to a start time

Great - I usually miss the location - but I always figure that most folk know their own

There are currently 1 users browsing this thread. (0 members and 1 guests)