About my formula for overlapping number ranges

  • This is a discussion related to my post in the Formulas forum. Here: http://www.ozgrid.com/forum/showthread.php?t=199398

    I will post a reply there with some additional info. and a link to this post.

    (Note: The following uses code tags formatting (i.e., preserve spaces).

    For a large exported data set, I needed to determine if the current row's number
    range overlapped with the next row's number range. For example, if you have some
    manufactured items with similar model and styles grouped together with different
    lot numbers, it can be of interest to know if the lot numbers overlap which would
    indicate a problem with the data.

    Originally, I just wanted a formula to use on a spreadsheet that would indicate
    if two number ranges overlapped. When I searched, all that I found was:
    * Statements similar to "Cant't do it with a boolean expression. You need need a computer program."
    * VBA routines.
    * Other prgramming language pseudo routines
    * Pseudo formulas similar to:
    - IF(A>C AND A<D Then True) ...
    - =if(a=c,true,if(a=d,true,(b=c,true,if(...

    My final answer is based on what follows hereafter.

    With two columns representing a range of numbers:
    Column A = the begin of a number range
    Column B = the end of a number range

    I used the following table/Visual of conditions defining if two ranges overlap to help figure out a
    worksheet formula.

    For two number ranges A1 thru B1 and A2 thru B2:

    Visual Overlap      Conditions for True                 How they overlap------------        ---------------------------         ---------------------------A1....B1            (B1 > A2) AND (B1 < A2)             A1-B1 overlaps A2-B2 at one end.......A2....B2.......A1....B1     (B2 > A1) AND (B2 < B1)             A1-B1 overlaps A2-B2 at other end                 Which gives a partial Boolean                  condition for True:                 ((B1 > A2) AND (B1 < A2))                 OR                 ((B2 > A1) AND (B2 < B1))                 Note that the overlap shown is visually more than exactly                  at the number range boundaries.                 It seemed a bit complicated to try using >= or =< due to                  the AND operators.                 Also, my boolean algebra has long ago become rusty.         The Excel worksheet formula: =OR(AND(B1>A2,B1<B2),AND(B2>A1,B2<B1))When the ranges overlap only at one or more boundaries or are an exact match, I used the following:Visual Overlap   Conditions for True                 How they overlap--------------   ---------------------------         ------------------------------------A1....B1         (A1 = A2) OR (B1 = B2)              Upper or lower boundaries matchA2....B2                                                    (or an exact match)A1....B1         (B1 = A2)                           One upper and lower boundary matches......A2....B2......A1....B1   (A1 = B2)                           Other upper and lower boudary matchesA2....B2                 Which gives another partial Boolean                  condition for True:                 (A1 = A2) OR (B1 = B2) OR (B1 = A2) OR (A1 = B2)                 Excel worksheet formula: =OR(A1=A2,B1=B2,B1=A2,A1=B2)Combining the two partials and using Excel's worksheet formulas with Cells A1, B1 are the first begin and end rangeCells A2, B2 are the second begin and end range:Worksheet formula       =OR(A1=A2,B1=B2,B1=A2,A1=B2,AND(B1>A2,B1<B2),AND(B2>A1,B2<B1))

    It displays True if the first range overlaps the second and False if not.
    You can use filtering, additional helper columns, etc. to further the data manipulation.

    I suppose a better, more structured database and data translation to my own database
    would be more exacting. However, I just wanted a worksheet since it would be much
    more of a project than is practical for me at this time.


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!