How to validate the overlap zone?

  • Thread starter Thread starter Eric
  • Start date Start date
E

Eric

There is a pair of numbers
(x,y) x is always larger than y
I would like to determine two pair of numbers in order to find out whether
they are matched or not, the matched condition is shown below

The spacing level is 8 in cell F1
Example 1
Location: 29 in cell A1, 25 in cell B1, 27 in cell C1, 23 in cell D1
(29,25) and (27,23) : they are overlap each other between 25-27, then
return yes in cell E1

(28,26) and (18,14) : they are not overlap each other, so I look for the
next spacing level (18+8,14+8) = (26,22), so mathcing (28,26) and (26,22) we
find out that they are overlap each other in 26, then return yes in cell E1

(28,26) and (22,17) : they are not overlap each other, so I look for the
next spacing level (22+8,17+8) = (30,25), so mathcing (28,26) and (30,25)
they are overlap each other between 26-28, then return yes in cell E1

Does anyone have any suggestions on how to do it in excel?
Thank everyone very much for any suggestions
Eric
 
Try this...

=IF(OR(MAX(0,MIN(A1,C1)-MAX(B1,D1)+1),MAX(0,MIN(A1,C1+F1)-MAX(B1,D1+F1)+1)),"Yes","No")
 
Back
Top