Excel function to identify (and calculate) if a 2 value data range is between multiple 2 values ranges of values

Slutfört Publicerat 5 år sedan Betalades vid leverans
Slutfört Betalades vid leverans

I am an independent consultant in energy sector and require assistance with a small excel project.

Task description:

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

Databehandling Excel Matematik

Projekt-id: #18321322

About the project

12 offerter Distansprojekt Aktivt 5 år sedan

Tilldelades:

MimiFL

Hello, i am serious freelancer with 100% completion rate and i believe i can help you. The logic in calculating the overlapping is not that hard, i just need you to ask you few questions about how would you like to exe Mer

$100 AUD inom 0 dagar
(469 omdömen)
6.9

12 frilansare har lagt bud på i genomsnitt $202 för det här jobbet

schoudhary1553

Hello, Hope you are doing well. I can help with you in your project Excel function to identify (and calculate) if a 2 value data range is between multiple 2 values ranges of values. I can assure you the quality j Mer

$500 AUD inom 10 dagar
(320 omdömen)
7.9
asadkhan3533

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

$222 AUD inom 5 dagar
(124 omdömen)
6.4
datakillers

Hi I have good Microsoft Excel skills with years of experience as well as I have complete number of projects for Excel to manage data and sorting. You can check my profile feedback section and you will know what my cl Mer

$100 AUD inom 0 dagar
(148 omdömen)
6.4
lightingdavid

Hello How are you? I have reviewed "Excel function to identify (and calculate) if a 2 value data range is between multiple 2 values ranges of values". I have good skills for these (Data Processing, Excel, Mathematics Mer

$200 AUD inom 2 dagar
(89 omdömen)
5.8
mail2vimalarora

Hello, I am ready to discuss project with you. Being experienced in working on several excel projects related to formula/vba/macro etc. based on that I can quickly help you in developing function that you need to c Mer

$100 AUD inom 1 dag
(72 omdömen)
5.4
RavinduJayzz

Simple Excel Formula needed. Read through the description and i can develop a formula for your needs. Hope to help you out. Thanks in advance :)

$100 AUD inom 1 dag
(15 omdömen)
4.9
herguz2017

regards dear sir Can you give me more details of your project? I am an expert in excel and data analysis I can help you with your project

$200 AUD inom 10 dagar
(6 omdömen)
3.9
ReneRondon

Hello. I bid for this project because I'm a professional with a lot of experience in MS Excel and VBA programming (macros). I guarantee you an accurate job, full functionality and fast delivery, all this in exchange of Mer

$110 AUD inom 1 dag
(17 omdömen)
3.1
anilbitmca

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.

$300 AUD inom 3 dagar
(0 omdömen)
0.0
ChiritaChiritas

Progetto sara sviluppato per rispondere a tutte le necessità , mi assicuro che alla fine tutte le richieste verranno soddisfate

$155 AUD inom 1 dag
(0 omdömen)
0.0
Hartono1093

I will do it the best

$333 AUD inom 10 dagar
(0 omdömen)
0.0