Formula: Test for overlapping number ranges

  • I searched the internet and here for the answer and found very little that was convincing. Often, an entire algorithm in VBA or other language was given.

    Here is my answer; remember, formula; not a VBA subroutine.

    For two number ranges m-n and x-y (i.e., m through n and x through y)


    Where m, n, x and y could be cell addresses or whatever gives the desired values defining the number ranges.

    Regards, Jim

  • Re: Formula: Test for overlapping number ranges

    Not sure I follow 100%, but if your going to use only formula's, I might suggest helper columns and then Use Countif against named ranges. Would have to see an example sheet of what your end goal is.

  • Re: Formula: Test for overlapping number ranges

    I posted a lot about this in the Hav-A-Chat part of this site. Here:

    Sorry for the previous ambiguity.

    Here is the final result using Excel's worksheet formulas with

    Cells A1, B1 are the first begin and end range
    Cells A2, B2 are the second begin and end range:

    True If Overlap =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.


Participate now!

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