I am an independent consultant in energy sector and require assistance with a small excel project.
Columns B and C provide a first set of data intervals. (a top and bottom depth interval). For example in row 14 the interval goes from 816.665 m to 816.833 m.
Columns Q and R provide a second set of data intervals (a top and bottom depth interval). For example in row 14 the interval goes from 781.18 m to 785.18 m and row 15 goes from 769.49 m to 773.49 m, etc.
The task is to develop a formula / function which will check and compare each individual data interval on columns B and C against ALL data intervals in columns Q and R AND to calculate the overlapping quantity, AND show result in the relevant row of column M.
Example 1 (No overlap), the formula in M14 would look at interval 816.665 to 816.833 m (columns B and C) and compare it to ALL intervals in columns Q and R and will find out that interval 816.665 to 816.833 m does not overlap with any of the intervals in columns Q and R and therefore the result in M14 would be 0. - -
Example 2 (Total overlap), on row 27, the formula needs to compare interval 736.908 - 737.199 with ALL intervals in columns Q and R and will find that it does overlap with interval 734.82 - 738.82 (row 18) and therefore the result in M27 will be 0.291 m.
Example 3 (Partial overlap), on row 67, the formula needs to compare interval 531.111 – 534.111 m with ALL intervals in columns Q and R and will find that it partially overlap with interval 529.17 – 533.17 m (row 36) and therefore the result in M67 will be 2.059 (533.17 minus 531.111)
Excel file is attached
13 freelancers are bidding on average $195 for this job
Please review my profile as i have relevant skills and experience required for this project. Kindly send me a message to discuss further. Thanks, Asad Khan
Hi, I am an excel expert and have 8+ years of experience. I can deliver this to you as per you expectation. Please ping me so that i can discuss with you and start working on his.