+ Reply to Thread
Results 1 to 12 of 12

Distributed datetime estimates based on given date ranges for chronologically sorted items

  1. #1
    Forum Contributor
    Join Date
    08-20-2011
    Location
    Oslo
    MS-Off Ver
    MS 365
    Posts
    245

    Distributed datetime estimates based on given date ranges for chronologically sorted items

    Hi,

    I have tried to get ChatGPT to solve this for me, but alas

    I have items in column A, which are sorted in what I presume to be chronological order.
    In column B, I have an estimated maximum date for when each entry was performed.
    In column C, I have an estimated minimum date for when each entry was performed.
    Together, column B and C gives a probable date range for when each entry was performed.

    I need a formula in column D, which estimates a specific datetime of entry for each item in column A.

    This formula needs to make the estimate in such a way that each estimated datetime falls within the specified date range estimation for each item,
    and in such a way that each estimate is as far away from the estimates of the other items as possible, with as many days/hours between each estimate as possible.
    In other words; maximize the entropy.

    I have attached a workbook containing example data. The colored presentation shows how I envision the distribution.

    Does anybody have a suggestion?

    Regards,
    Marbleking

  2. #2
    Forum Contributor
    Join Date
    08-20-2011
    Location
    Oslo
    MS-Off Ver
    MS 365
    Posts
    245

    Re: Distributed datetime estimates based on given date ranges for chronologically sorted i

    Hi,

    I found a solution that works when I enable iterative calculation. (Otherwise it yields circular references), please see updated workbook below.

    Does anybody have a solution for this that doesn't require iterative calculation?

    Best regards,
    Marbleking
    Attached Files Attached Files

  3. #3
    Forum Contributor
    Join Date
    08-20-2011
    Location
    Oslo
    MS-Off Ver
    MS 365
    Posts
    245

    Re: Distributed datetime estimates based on given date ranges for chronologically sorted i

    Hi,

    I am still improving the iterative solution to this.

    I have the following formula in column D:

    =IF(ROW()=2,$B$2,IF(ROW()=21,$C$21,MAX($B2,MIN((D1+D3)/2,$C2))))

    The iterative calculation makes sure the resulting date times are as evenly divided as possible, within the minimum and maximum time frames given by columns B and C.

    But for this to be complete, I also need the formula to take into consideration the times of the day.
    The chronologically sorted events can only happen between 5 PM and 10 PM on workdays and between 11 AM and 10 PM on weekends and holidays.

    How do I incorporate this into the formula, while still making sure the results come out as evenly distributed as possible?

    Or perhaps someone has a VBA for this?

    Best regards,
    Marbleking
    Attached Files Attached Files
    Last edited by Marbleking; 01-21-2023 at 02:15 PM.

  4. #4
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2016 & H&B2021
    Posts
    2,957

    Re: Distributed datetime estimates based on given date ranges for chronologically sorted i

    Without taking into account the time consideration, another possible way is to take the mid-point between:
    1) the mid-point of earliest event time from the next event time (counting from the last event)
    2) the mid-point of latest event time from the previous event time (counting from the first event)
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    08-20-2011
    Location
    Oslo
    MS-Off Ver
    MS 365
    Posts
    245

    Re: Distributed datetime estimates based on given date ranges for chronologically sorted i

    Thanks, josephteh! That's really helpful.

    I have tried your solution on larger datasets, and see that it may give the same date and time to several events. However, these are chronologically sorted events where none of the events happen at the exact same time. I see that I didn't specify that.

    How can your solution be updated to take that into consideration?

    And secondly; is it possible to take the available times of each day into consideration?

    Regards,
    Marbleking

  6. #6
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2016 & H&B2021
    Posts
    2,957

    Re: Distributed datetime estimates based on given date ranges for chronologically sorted i

    Please attach a workbook showing the larger datasets.

  7. #7
    Forum Contributor
    Join Date
    08-20-2011
    Location
    Oslo
    MS-Off Ver
    MS 365
    Posts
    245

    Re: Distributed datetime estimates based on given date ranges for chronologically sorted i

    Hi,

    Please see attached workbook with larger datasets.

    I'm beginning to think that maybe VBA is needed for this, but if you're able to solve this with formulas, that would be great.

    Regards,
    Marbleking
    Last edited by Marbleking; 01-21-2023 at 09:29 AM.

  8. #8
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2016 & H&B2021
    Posts
    2,957

    Re: Distributed datetime estimates based on given date ranges for chronologically sorted i

    Change the denominator 2 to the total number of days = current date + 1.

  9. #9
    Forum Contributor
    Join Date
    08-20-2011
    Location
    Oslo
    MS-Off Ver
    MS 365
    Posts
    245

    Re: Distributed datetime estimates based on given date ranges for chronologically sorted i

    Thanks! You're closing in on a good solution here. But still, a large fraction of the results are outside the constraints given by columns B and C (and the allowable times of day).

    Regards,
    Marbleking

  10. #10
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2016 & H&B2021
    Posts
    2,957

    Re: Distributed datetime estimates based on given date ranges for chronologically sorted i

    Hi Marbleking, I just came back from my CNY break. I have no idea how to proceed and has called for experts to help: https://www.excelforum.com/the-water...ml#post5784432.

  11. #11
    Forum Contributor
    Join Date
    08-20-2011
    Location
    Oslo
    MS-Off Ver
    MS 365
    Posts
    245

    Re: Distributed datetime estimates based on given date ranges for chronologically sorted i

    Hi Josephteh! Thanks for reverting back and happy new year!

    I have improved on the solution I started with that used the following iterative formula:

    =IF(OR(ROW()=2,ROW()=2267),C2,MAX(B2,MIN((D1+D3)/2,C2))))

    I noticed the path created by this formula looked like a piece of string that had been pulled tightly through the funnel made up of the minimum and maximum datetime limits. This created some "convergence points" along the way, where the formula result was tangential to one or both of the minimum and maximum datetime limits. The path was made up of several straight lines that were drawn between these convergence points. I read about funnel path smoothing in the following article and was thinking there might be a relation to my problem here, although I'd like to find a "smoother" path and not one that contains all these angles and straight lines:

    https://medium.com/@reza.teshnizi/th...y-41e374172d2d

    Anyway, please see the attached workbook. I have kept the values from the above formula as a starting point for further calculations to make sure the resulting datetime estimates are within the criteria for the time of day, and not only within the correct dates. It can still be improved a lot.

    Regards,
    Marbleking
    Last edited by Marbleking; 01-28-2023 at 06:59 PM.

  12. #12
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2016 & H&B2021
    Posts
    2,957

    Re: Distributed datetime estimates based on given date ranges for chronologically sorted i

    Thanks Marbleking! I believe you have nailed it! Not only the datetime estimates are within the earliest/latest estimated time, but within the time constraints as well. Well done!

+ 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. [SOLVED] Datetime text to Datetime Date
    By stribor40 in forum Excel General
    Replies: 3
    Last Post: 11-20-2021, 04:44 AM
  2. extracting page number ranges based on prefix of sorted data
    By wdjohnson in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-06-2016, 05:34 PM
  3. [SOLVED] Finding the Average of Unequally distributed sorted data
    By unitedtillidie in forum Excel General
    Replies: 19
    Last Post: 08-01-2016, 06:05 AM
  4. Find where my date is chronologically...
    By domgilberto in forum Excel General
    Replies: 18
    Last Post: 02-18-2016, 05:31 PM
  5. displaying estimates based on a table with limited data
    By Robert99 in forum Excel General
    Replies: 2
    Last Post: 02-04-2011, 11:44 PM
  6. Based on a Column datetime value auto calculate and populate a datetime range
    By rajashanmuga in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-22-2010, 04: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