How can we check whether the given time is between the working hours or not?
Is this Time between Working Hours
In our case, work starts at 9:30 AM, and ends at 5:30 PM. Our given time, which is 6:47 isn’t between the working ours. When we change it, we can see that the color changed into red and ‘No’ turned into ‘Yes’, which means that now the time is between the working ours (Fig. 1)
Fig. 1 Our given time is between the working hours
However, when we go to earlier hours we can see that the details changed (Fig. 2)
Fig. 2 Our given time isn’t between the working hours
How can we check our time? The simplest solution is using the MEDIAN function that returns the value in the middle. Since all our times are in one row, we can just select the range (Fig. 3)
=MEDIAN(B2:D2)
Fig. 3 Selecting the whole range
If our data is in separate cells, we can just add those cells one by one (Fig. 4)
=MEDIAN(B2,C2,D2)
Fig. 4 Selecting cells one by one
And just like that we receive the value in the middle. If this value is equal to the given time, it means it’s between the working hours. What’s curious about our median is the fact that it won’t cross our thresholds, i.e. our starting time and finishing time. It means that the smallest value our MEDIAN function can show is the starting hour, and the largest is the finishing hour (Fig. 5)
Fig. 5 The smallest value
Now, we have to check whether our time is between the working hours. We have to compare the function to the given time (Fig. 6)
=MEDIAN(B2,C2,D2)=B2
Fig. 6 Comparing
As we can see, our logical test returned FALSE (Fig. 7)
Fig. 7 FALSE
If the logical test returns TRUE, it means that the given time is between the working ours (Fig. 8)
Fig. 8 TRUE
The answers we have are logical. Let’s make them more human like by adding the IF function to our MEDIAN logical test and some simple text like ‘Yes’ and ‘No’ (Fig. 9)
=IF(MEDIAN(B2,C2,D2)=B2,“Yes”,“No”)
Fig. 9 Adding the IF function
We can see that we can check whether our given time is between the working ours or no (Fig. 10)
If you want to calculate a median value with a condition, you cannot use a simple MEDIAN function because it needs only numbers. We have to use another function that will reduce the number of wages we look at.
Median with condition
Typically, when we want to add a condition to another function we can use the IF function and make a logical test. In our case, the logical test will be simple. We just have to check each cell in the Department column whether it’s equal to the name of the Department we look at now. Since we are comparing a single cell to a whole range, Excel will check every single cell in a given range (Fig. 1).
Fig. 1 Comparing a single cell to a range of cells
If a cell in the range is equal to the target cell, we want the function to return proper wages, i.e. the wages from the same row. If the logical test result is FALSE, we want to have something that the MEDIAN function will ignore which, in most cases, is an empty text string. However, I want to show you that the IF function really returns something so let’s write “no” (Fig. 2).
=IF($B$2:$B$13=E2,$C$2:$C$13,“no”)
Fig. 2 IF function
We can see that Dynamic Array Excel spilled the results and we have wages only for the departments we chose. For other departments we have ‘no’. Now, we can put this function into the MEDIAN function (Fig. 3).
=MEDIAN(IF($B$2:$B$13=E2,$C$2:$C$13,“no”))
Fig. 3 MEDIAN function with IF function
Now, we’ve calculated the median value with a condition for each department. We can see that for HR it’s $4,000, for IT it’s $8,100 and for Marketing it’s $5,900. Those are the middle values for each department (Fig. 4).
Why do politicians lie to us when talking about average wages? Let’s ask Excel.
Median vs Average wages
Let’s calculate some average. The AVERAGE function is a simple function that sums up values from all cells in a range and divides it by the number of cells with values (Fig. 1).
=AVERAGE(D2:D12)
Fig. 1 AVERAGE function
In our example, the average is $2,900. It means that 8 people earn less, which gives us around 80% of all people. It’s important that one person at the bottom earns much more than the rest and affects our average more than other people (Fig. 2).
Fig. 2 Average wages
If we want to talk more precisely about the distribution of those wages, we can use the MEDIAN function which returns the value in the middle (Fig. 3).
=MEDIAN (D2:D12)
Fig. 3 MEDIAN function
Since our values are sorted, we can clearly see the middle value, which is $2,000. We can see that 50% of people earn this value or less and 50% of people earn this value or more (Fig. 4).
Fig. 4 Median value
If we have an even number of values and there are two middle values, the MEDIAN function calculates the average of those two values near the middle line (Fig. 5).
=MEDIAN(G2:G11)
Fig. 5 MEDIAN function with even number of cells
We can see that it calculated the middle value (Fig. 6).
Fig. 6 Median value
If we add one zero to cell D12 which belongs only to one person, we can see that the average hit $11,900. It means that only one person earns more that the average. On the other hand, the median value stayed the same. It’s very important to know this difference (Fig. 7).
Fig. 7 Higher average
I also want to add one column with the RAND function (Fig. 8).
=RAND()
Fig. 8 RAND function
I want to show you that we don’t have to have our data sorted to use the MEDIAN function (Fig. 9).
