• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Check for overlapping of a median value in a specific range

P

pradhishnair

New Member
http://i40.tinypic.com/10fxrf6.jpg


i need to find if the values between range D2:E2 are overlapping in any of the following rages, if yes then where? (may be row number)

thanks upfront!
 
Hi ,


Your modified question is not very clear to me ; can you please clarify ?


For instance , what are the values in column D and E ? How are we to find out whether a value like 149+650 ( in cell E10 ) , overlaps with a value in column D ?


Narayan
 
Oh my bad. should have removed the custom formatting before posting.. the img is reposted please check..

http://i41.tinypic.com/zsn2o7.jpg
 
Pradhishnair


Firstly, Welcome to the Chandoo.org forums


Try this one:

=IF(SUMPRODUCT(($D$3:$D$5<=E2)*($E$3:$E$5>=D2))>0,"Overlap row = "&TEXT(SUMPRODUCT(($D$3:$D$5<=E2)*($E$3:$E$5>=D2)*ROW(D3:D5))-2,"#"),"No Overlap")


Adjust ranges to the length of your data


ps: In future please post sample files not JPG's, it makes it so much easier for us to assist you.

Please refer to this post: http://chandoo.org/forums/topic/posting-a-sample-workbook
 
Dear Hui,

thanks for your attempt. but the formula aint doing any good for me. a sample work book with the formula you suggested have been attached herewith. i require to find if whether the values between "chainage From" and "chainage To" is overlapping in any of the subsequent data. Also if it is then location of the overlap. Hope i have made myself clear. waiting eagerly to hear from you. thanks upfront.


https://docs.google.com/viewer?a=v&pid=explorer&chrome=true&srcid=0BwDJ-TLtQDayMjVmMmUwMjgtMGZiYS00ZDNiLTg2NTctYzcyMmNkNTEyYTVi&hl=en_US


https://skydrive.live.com/redir.aspx?cid=c5de4d6874a65d50&resid=C5DE4D6874A65D50!111&parid=root
 
a Simple
Code: 
=SUMPRODUCT((D3:$D$41<=E2)*(E3:$E$41>=D2)) will tell you if it overlaps


or


="Overlap Row S.no.: "&MIN(IF((D3:$D$41<=E2)*(E3:$E$41>=D2)*(A3:$A$41)>0,(D3:$D$41<=E2)*(E3:$E$41>=D2)*(A3:$A$41)))

Will tell you the S.no. of the first overlapping row
 
Not helping me out sir,

could you please help me out with the working of this formula.

also i need to locate the location where the overlapping is occurring. hope to hear from you soon.
 
="Overlap Row S.no.: "&MIN(IF((D3:$D$41<=E2)*(E3:$E$41>=D2)*(A3:$A$41)>0,(D3:$D$41<=E2)*(E3:$E$41>=D2)*(A3:$A$41)))

Will tell you the S.no. of the first overlapping row
 
If i wish to exclude the upper and lower values of a ranges is it okay if i use only "<" & ">" instead of "=<" and "=>" operators..
 
the formula is doing just awesome.. i really would like to know the working of this formula. if anytime u can spare sometime for this. i would really thankful.
 
hi again hui i will be troubling you a little more.. is it possible to print the "Overlap Row S.no.: "more than 0 only"

ofcourse there are lengthy ways but i wish to know if a particular result be omitted in ur formula, for eg print all values except for Overlap Row S.no.:0


please reply..
 
Code: 
="Overlap Row S.No. = "&MIN(IF((D3:$D$41<=E2)*(E3:$E$41>=D2)*(A3:$A$41)>0,(D3:$D$41<=E2)*(E3:$E$41>=D2)*(A3:$A$41)))

Then press Ctrl Shift Enter, Not Enter


This can be simplified to:

="Overlap Row S.No. = "&MIN(IF((D3:$D$41<=E2)*(E3:$E$41>=D2)>0,(A3:$A$41)))
 
You must log in or register to reply here.
Back
Top