+ Reply to Thread
Results 1 to 4 of 4

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

  1. #1
    Registered User
    Join Date
    09-10-2020
    Location
    Brussels, Belgium
    MS-Off Ver
    Microsoft Excel for Office 365 MSO
    Posts
    2

    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
    Attached Files Attached Files
    Last edited by LogisticsExpert; 09-11-2020 at 03:54 AM.

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    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....
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Registered User
    Join Date
    09-10-2020
    Location
    Brussels, Belgium
    MS-Off Ver
    Microsoft Excel for Office 365 MSO
    Posts
    2

    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. #4
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 2
    Last Post: 06-13-2020, 03:02 AM
  2. Replies: 0
    Last Post: 03-04-2016, 06:38 PM
  3. Replies: 4
    Last Post: 03-02-2016, 02:46 AM
  4. Replies: 10
    Last Post: 11-08-2014, 04:51 PM
  5. Replies: 3
    Last Post: 12-19-2013, 06:49 AM
  6. Replies: 3
    Last Post: 03-27-2012, 01:07 PM
  7. Replies: 1
    Last Post: 03-27-2006, 01:10 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1