Completed

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

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

Skills: Data Processing, Excel, Mathematics

See more: excel if value in range then return value, if function excel, excel formula if cell contains text then return value in another cell, multiple if statements in excel, excel if between multiple ranges, if formula excel multiple conditions, excel if statement multiple conditions range, multiple if statements in excel with text, calculate differences data field, excel sheet marketing research survey data entry, updating excel vba workbooks without changing data, excel function open url, vba excel pop gui user input data, excel macro copy selected cell data cell, excel vba address calculate range, code export data sql multiple sheet excel 65000, excel function fetch data internet, excel function check double entries data, excel calculate 2d data, excel function cidr range

About the Employer:
( 0 reviews ) Brisbane, Australia

Project ID: #18321322

Awarded to:

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 More

$100 AUD in 0 days
(409 Reviews)
6.8

13 freelancers are bidding on average $195 for this job

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 More

$500 AUD in 10 days
(295 Reviews)
7.7
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 in 5 days
(87 Reviews)
6.0
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 More

$100 AUD in 0 days
(55 Reviews)
5.6
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 More

$100 AUD in 1 day
(65 Reviews)
5.3
webanis

Hi, I am interested in your offer: Excel VBA code. Let me know more details about this work. I will start working immediately :) Br;

$111 AUD in 0 days
(16 Reviews)
4.0
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 More

$200 AUD in 2 days
(7 Reviews)
4.0
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 More

$110 AUD in 1 day
(14 Reviews)
2.8
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 in 10 days
(2 Reviews)
1.9
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 in 1 day
(2 Reviews)
1.5
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 in 3 days
(0 Reviews)
0.0
ChiritaChiritas

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

$155 AUD in 1 day
(0 Reviews)
0.0
Hartono1093

I will do it the best

$333 AUD in 10 days
(0 Reviews)
0.0