+ Reply to Thread
Results 1 to 11 of 11

Roundup to Next Event from a Last Known Event and a Current Block

  1. #1
    Forum Contributor
    Join Date
    07-22-2013
    Location
    london
    MS-Off Ver
    Excel 2013
    Posts
    133

    Roundup to Next Event from a Last Known Event and a Current Block

    First of all, here's the sample sheet:
    https://filetransfer.io/data-package/YvwqMbpW#link
    (I cannot upload it here. ExcelForum, please fix the attach button as it doesn't work. Tried with Opera and Chrome)


    So we have a set of numbered blocks like these:

    22284676
    22296196
    22307716
    22319236
    22330756
    22342276

    Think of them like epochs, representing specific times at which the "Reset Event" take place.
    As you can see, they come with the same consecutive "Reset Lenght" increments => 11520.
    As an example, the first "Last Known Reset" is 22284676 + 11520 = 22296196 (which is the next reset) and so on.

    Now here's the problem:
    We want to know the next reset number/block based on a Known Reset PLUS a current arbitrary starting block.

    How do we do it with a formula?



    Note: I asked a similar question using time on the link below. Maybe the answer could help solve this one too.
    https://www.excelforum.com/excel-for...ultiplier.html

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

    Re: Roundup to Next Event from a Last Known Event and a Current Block

    In B5, copied down:

    =A5+$B$2

    Not sure exactly what you are looking for....
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Forum Contributor
    Join Date
    07-22-2013
    Location
    london
    MS-Off Ver
    Excel 2013
    Posts
    133

    Re: Roundup to Next Event from a Last Known Event and a Current Block

    Let me see if I can make it clearer.

    The resets are fixed on specific numbers blocks (see column D).

    And apart of that we have new arbitrary starting points and want to know the next resets from there.
    If you just add the "Reset Length" to the arbitrary starting points you won't get the actual reset numbers/blocks.
    (we need to round up to the next actual reset numbers/blocks).

    Is that clear enough?
    (sorry for my english)
    Last edited by nicoan; 12-08-2021 at 03:45 PM.

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

    Re: Roundup to Next Event from a Last Known Event and a Current Block

    There were values in A that were greater than any in D - so I guess you need time travel

    Anyway, in C5, array-enter (enter using Ctrl-Shift-Enter) this formula (change the 7s to reflect the actual last row of your values in D)

    =INDEX($D$2:$D$7,MATCH(MIN(ABS(A5+$B$2-$D$2:$D$7)),ABS(A5+$B$2-$D$2:$D$7),FALSE))

    Then copy down - that will choose the closest value from the list in D.

  5. #5
    Forum Contributor
    Join Date
    07-22-2013
    Location
    london
    MS-Off Ver
    Excel 2013
    Posts
    133

    Re: Roundup to Next Event from a Last Known Event and a Current Block

    My bad. The column D was to explain that from a known first reset/number we could get ALL future AND past resets, just by using the "Reset Length" (back and forth) on any of them

    Sorry, I now see that it wasn't clear enough.

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

    Re: Roundup to Next Event from a Last Known Event and a Current Block

    Why don't you post another version that shows what you have, what you want - and color code (with matching colors) values from D that you want to appear in B .

  7. #7
    Forum Contributor
    Join Date
    07-22-2013
    Location
    london
    MS-Off Ver
    Excel 2013
    Posts
    133

    Re: Roundup to Next Event from a Last Known Event and a Current Block

    Ok, here's v2:
    https://filetransfer.io/data-package/5ETyVtbf#link

    Hopefully it's clearer, with a few examples:

    Attachment 758680

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

    Re: Roundup to Next Event from a Last Known Event and a Current Block

    Try this in B5: Column D must be sorted ascending.

    =INDEX($D:$D,MATCH(A5,$D:$D,TRUE)+1)

  9. #9
    Forum Contributor
    Join Date
    07-22-2013
    Location
    london
    MS-Off Ver
    Excel 2013
    Posts
    133

    Re: Roundup to Next Event from a Last Known Event and a Current Block

    Bernie, the column D is an illustrative example.
    The formula I'm looking for doesn't use the column D at all.

    It just uses the values in $B$1 and $B$2 to get ALL (past and future) possible Resets.

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

    Re: Roundup to Next Event from a Last Known Event and a Current Block

    OH!

    In B5, use the formula

    =$B$1+ROUNDUP((A5-$B$1)/$B$2,0)*$B$2

    and copy down.

  11. #11
    Forum Contributor
    Join Date
    07-22-2013
    Location
    london
    MS-Off Ver
    Excel 2013
    Posts
    133

    Re: Roundup to Next Event from a Last Known Event and a Current Block

    Perfect..

    Thanks a million!!

+ 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] Formula to return the longest stretch of days from one event to the next event
    By jolthof in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-02-2021, 10:01 PM
  2. Replies: 2
    Last Post: 11-11-2017, 04:49 AM
  3. Replies: 4
    Last Post: 07-18-2015, 04:28 AM
  4. [SOLVED] Userform multipage control - exit event not firing or event order
    By jane serky in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-14-2013, 10:23 AM
  5. Replies: 4
    Last Post: 11-07-2012, 04:02 PM
  6. [SOLVED] Mental Block! - Event Change Conditional Formatting
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-24-2005, 09:06 AM
  7. block event
    By Roman Töngi in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-11-2005, 08:06 AM

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