Use MEDIAN function to calculate overlapping ranges
I found an old post that I think is interesting to write about today. Think of two overlapping ranges, it may be dates, times or whatever, the formula demonstrated here works with everything.
This picture shows two time ranges, 06:00-13:00 (yellow) and 11:00-18:00 (green).
It is obvious that there are two overlapping hours but how do we calculate it in excel?
The MEDIAN function comes to rescue, but first let me explain the function. It returns a value that separates the higher half of a data set from the lower half. Example, MEDIAN(1,2,3) returns 2. 1 is the lower half and 3 is the higher half.
MEDIAN(1,2,3,4,5,6) returns 3.5 because there are two values (3, 4) separating the higher half (5,6) from the lower half (1,2). The average of these two values is 3.5.
Calculate overlapping hours
We have 4 times here to remember, the start and end of time range 1 and 2.
Let see what happens if we use the MEDIAN function with the start and end value of time range 1 and only the start value of time range 2.
MEDIAN("06:00", "13:00", "11:00") returns 11:00.
And then the end of time range 2.
MEDIAN("06:00", "13:00", "18:00") returns 13:00
13:00 - 11:00 is 02:00. Two hours are overlapping.
The formula becomes
and returns 02:00.
Lets do something more complicated
Between 00:00 and 08:00 the rate is 8, 08:00-18:00 the rate is 5 and 18:00 to 24:00 the rate is 10.
How do we calculate total cost if the time range is 06:00-20:00?
Count overlapping hours for the first range 00:00-08:00 and multiply with rate 8.
returns 16.
Count overlapping hours for the second range 08:00-18:00 and multiply with rate 5.
returns 50
Count overlapping hours for the second range 18:00-24:00 and multiply with rate 10.
returns 20
Combining all formulas gives
returns 86.
Interested in learning more about excel, join my Advanced excel course.
Download *.xlsx file
Median - overlapping ranges.xlsx
I have a question for you
It would be great to build an array formula to shrink the formula above, like this one:
But it won´t work, you can´t use the MEDIAN function to do that. Do you know a workaround?
Related articles
Days contained in a range that overlap another range
Rene asks: Hi Oscar, I need a fomula that gives me the number of days contained in a range that […]
Column C contains both date and time, to calculate the average of only time we need to extract the hours, minutes […]
The DATEDIF function in cell E3 allows you to calculate days between two dates.
Question: I am trying to create an excel spreadsheet that has a date range. Example: Cell A1 1/4/2009-1/10/2009 Cell B1 […]
Find latest date based on a condition
Table of contents Lookup a value and find max date How to enter an array formula Explaining array formula Download […]
Formula for matching a date within a date range
Table of contents Match a date when a date range is entered in a single cell Match a date when […]
Question: I am trying to create an excel spreadsheet that has a date range. Example: Cell A1 1/4/2009-1/10/2009 Cell B1 […]
Find latest date based on a condition
Table of contents Lookup a value and find max date How to enter an array formula Explaining array formula Download […]
Formula for matching a date within a date range
Table of contents Match a date when a date range is entered in a single cell Match a date when […]
Column C contains both date and time, to calculate the average of only time we need to extract the hours, minutes […]
Returns an integer representing the hour of an Excel time value. The returning number is ranging from 0 (12:00 A.M.) […]
How to use the MEDIAN function
The MEDIAN function calculates the median based on a group of numbers. The median is the middle number of a […]
Days contained in a range that overlap another range
Rene asks: Hi Oscar, I need a fomula that gives me the number of days contained in a range that […]
Highlight records based on overlapping date ranges and a condition
adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]
Identify overlapping date ranges
The formula in cell F6 returns TRUE if the date range on the same row overlaps another date range in […]
Column C contains both date and time, to calculate the average of only time we need to extract the hours, minutes […]
Count complete hours between two times
The formula in cell D5 calculates the number of complete hours between the time entries in cell B5 and C5. […]
8 Responses to “Use MEDIAN function to calculate overlapping ranges”
Leave a Reply to Henry Dishington
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
English
Spanish
Great post! Here's a solution, which uses a couple of (changeable) assumptions:
=SUMPRODUCT(FREQUENCY(ROW(INDIRECT("A"&HOUR(A7)+ 1&":A"&HOUR(B7))), HOUR(C2:C3)), D2:D4)
By quantizing the time (into hours in this case), and then counting the quantity in each rate bucket, we can then do a SUMPRODUCT against the rates. Remember that the FREQUENCY function returns one more item than the number of buckets passed to it, so I left out the final bucket time to compensate.
-Alex
Alex,
fantastic, why didn´t I think of that.
Thank you for your valuable comment!
I'm willing to take my medicine if there's something simple in here that I should have seen, but here goes...
How do I find overlapping ranges (using the MEDIAN function) for three to six time ranges? Or do I have to figure each range separately, then combine it into one IF statement using AND statements?
Forgot to add something to my previous post:
There are a variable number of time range(s) within each day - anywhere from 0 to 4 time ranges - and the number of ranges may change from day to day. Any formula must be able to handle this.
Henry Dishington,
Do you want to find overlapping ranges? Read this:
https://www.get-digital-help.com/2013/11/26/identify-overlapping-records/
Or do you want to count overlapping days?
Another solution at your quest, with CSE formula:B2:B4,$A$7,B2:B4)>0,IF($B$7B2:B4,$A$7,B2:B4),0)*D2:D4*24)
=SUM(IF(IF($B$7
for some unknown reason, previous paste was an error.
Correct formula is:
{=SUM(IF(IF($B$7A2:A4,$A$7,A2:A4)>0,IF($B$7A2:A4,$A$7,A2:A4),0)*C2:C4*24)}
O, no, not again!
I try to upload a file.