+ Reply to Thread
Results 1 to 7 of 7

How to calculate the percentage of overlap of one interval range onto another?

  1. #1
    Registered User
    Join Date
    12-07-2017
    Location
    Maryland, USA
    MS-Off Ver
    2012
    Posts
    3

    Smile How to calculate the percentage of overlap of one interval range onto another?

    I would like to calculate what percent of one range falls onto another range. I have a high value and a low value for each range.

    For example, if Bobby's interval is 5-10, and Katie's interval is 4-8, then Bobby's overlaps onto Katie's 75% (5-8 in the range of 4-8, or 3/4 numbers are the same, 75%). I can do this by hand, but I have thousands of these to do. Anyone know how to do this in Excel using conditional formatting or the like? Thank you!

    I have scoured the internet and asked professors, and no one has either had something that will work to calculate percentage specifically, or do not know how to do it not by hand.

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,894

    Re: How to calculate the percentage of overlap of one interval range onto another?

    Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

    1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired solution is also shown (mock up the results manually).

    3. Make sure that all confidential information is removed first!!

    4. Try to avoid using merged cells. They cause lots of problems!

    Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: How to calculate the percentage of overlap of one interval range onto another?

    I agree that a sample file would help us help you. Is your data entered as that kind of text string ("4-8")? Assuming that is the case, here's how I see this being done:

    1) Separate the low and high for each person =VALUE(LEFT("4-8"),1) would extract the 4. Similar (using LEFT(), RIGHT(), MID(), and other text manipulation functions to extract the lows and highs for each person. This step can be avoided if you enter your lows and highs into separate cells at data entry. Details here will depend greatly on knowing the entire range of possible entries to account for all possible scenarios.
    2) I don't readily see a neat way to identify percent overlap, so I might just calculate the several combinations and see what comes out. Percent overlap appears to be either (person_A_high-person_B_low)/(person_B_high-person_B_low) or (person_B_high-person_A_low)/(person_B_high-person_B_low). I would probably calculate both percentages, and determine the final result from there:
    2a) One result between 0 and 1 and the other result less than 0 or greater than 1 is the overlap. (A COUNTIFS() function should be able to determine how many there are).
    2b) Both results less than 0 or greater than 1 suggest no overlap.
    2c) Both results between 0 and 1 suggest that A's range is entirely within B's range, suggesting that the result might be (person_A_high-person_A_low)/(person_B_high-person_B_low).

    It's not fully developed, but that is a quick overview of how I might approach this. Note that, as one who strongly prefers to use several cells with simple formulas (lots of helper cells, in other words), I would spread that out over several cells.

    Sometimes it seems that a lot of the struggle with these kinds of problems is not how to solve the problem, but, because we despise helper cells, how to combine everything into a single cell. Is that the case here?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  4. #4
    Registered User
    Join Date
    12-07-2017
    Location
    Maryland, USA
    MS-Off Ver
    2012
    Posts
    3

    Re: How to calculate the percentage of overlap of one interval range onto another?

    Hello,

    Thank you for your feedback. I have made a sample excel data file with 10 rows of data (attached). As you will see, the low and high are already separated. I would like to see how much the Team's range (columns C & D) overlap onto the Individual's range (columns A & B). I appreciate any other help you can offer.
    Attached Files Attached Files

  5. #5
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: How to calculate the percentage of overlap of one interval range onto another?

    Your sample file did not include any of the calculations I suggested. Do they look right?

    I put my step 2 calculations into columns F and G, then my COUNTIFS() (step 2a) function in column H. It was fairly easy at this point to see that rows 6, 9, and 10 have not overlap, and row 3 is completely overlapping. I could easily read off the values between 0 and 1 from the other rows to identify the percent overlap (most were either 0% or 100%. Only row 11 had a 94% overlapping value). In order to extract the percent from columns F and G, I used an AVERAGEIFS() function (very similar to the COUNTIFS() function) in column I to get the per cent overlap for each row.

    column F: =(D2-A2)/(B2-A2)
    column G: =(B2-C2)/(B2-A2)
    column H: =COUNTIFS(G2:H2,">=0",G2:H2,"<=1")
    column I: =AVERAGEIFS(G2:H2,">=0",G2:H2,"<=1")

    Is that what you are looking to do?

  6. #6
    Registered User
    Join Date
    12-07-2017
    Location
    Maryland, USA
    MS-Off Ver
    2012
    Posts
    3

    Re: How to calculate the percentage of overlap of one interval range onto another?

    Yes! Thank you so much MrShorty.

  7. #7
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: How to calculate the percentage of overlap of one interval range onto another?

    Another thought.

    Try array entering this in E2 and filling down. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.
    Formula: copy to clipboard
    Please Login or Register  to view this content.



    A
    B
    C
    D
    E
    F
    1
    Individual Low
    Individual High
    Team Low
    Team High
    2
    1200
    1500
    1500
    1800
    0.33%
    In E2: {=SUM(--(ROW(INDIRECT(A2&":"&B2))=TRANSPOSE(ROW(INDIRECT(C2&":"&D2)))))/(D2-C2+1)}
    3
    1600
    1800
    1600
    1800
    100.00%
    4
    1800
    1900
    1400
    1800
    0.25%
    5
    1670
    1830
    1650
    1830
    88.95%
    6
    1850
    1920
    1750
    1950
    35.32%
    7
    1300
    1500
    1200
    1300
    0.99%
    8
    1600
    1700
    1600
    1900
    33.55%
    9
    1920
    1950
    1550
    1600
    0.00%
    10
    1850
    1897
    1300
    1450
    0.00%
    11
    1832
    1936
    1820
    1930
    89.19%
    Dave

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. how do i calculate the overlap of a data range using excel functions.
    By Lynn Akinyi in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-27-2017, 09:20 AM
  2. Replies: 2
    Last Post: 11-04-2016, 04:56 AM
  3. Replies: 3
    Last Post: 01-12-2015, 05:18 AM
  4. Calculate percentage of specific date range
    By grahto in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-25-2013, 06:39 AM
  5. Calculate highest percentage of 2 pairs between a range
    By sans in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-24-2012, 12:03 PM
  6. Macro to calculate range by a percentage
    By judasdac in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-26-2009, 06:11 PM
  7. Replies: 9
    Last Post: 11-24-2006, 06:03 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1