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)
Sometimes, we need to make many statistical operations and use many statistical functions in Excel. How can we do it?
Named ranges and statistics
A fast and quick solution is using name ranges. For example, we have the SUM function that sums up all values from January (Fig. 1)
Fig. 1 SUM function
How can we name ranges and use them for our advantage? Let’s start with a simple example of the SUM function. For the SUM function, we have keyboard shortcut which is Alt + =. This way Excel will put the sum for January, which is a range form B2:B6 (Fig. 2)
=SUM(B2:B6)
Fig. 2 Alt + = to put the sum
In the example above we had numbers. However, let’s go to another sheet where we have no numbers. When we want to sum the numbers from the previous sheet, it’s going to be a very tedious job, as we have to move between those two sheets and write proper values. What’s more, the reference we have while using values form another sheet isn’t the easiest to understand. What we can do is naming the ranges. We just have to select a range in a column and write a proper name in the Name Box (Fig. 3).
Fig. 3 Naming the range
Now, when we are using the Alt + = shortcut under our values, Excel will automatically refer to the January range, which is B2:B6 (Fig. 4)
=SUM(January)
Fig. 4 Automatic reference
As we know, Excel remembers names. It means that we can use the name in calculation on a different sheet. When I start writing “ja” in another sheet, Excel will suggest the range name (Fig. 5). I can just accept it and sum the values from January.
Fig. 5 Excel suggested the range name
As we can see, naming ranges makes our work faster. We can also name a bigger group of columns at one time. Just select the group you want. What’s important, when you’re selecting your data, you have to select the top rows, and then the numbers beneath them. Then, go to the Formulas tab, click on the Create from Selection command and select the Top Row checkbox in the window that appeared (Fig. 6)
Fig. 6 Creating names from selection
Now, after we named our ranges, we can see that the February range is named February, the March range is named March and so on. When we move to another sheet, we can use those names (Fig. 7)
Fig. 7 Excel is suggesting the new names
However, we can go one step further using the reference to names. I have January in cell B1. But, when I refer to cell B1, Excel won’t simply convert the text from cell B1 to a range because it just text. It means that our function won’t add anything. First of all, we have to lock the rows (B$1), then add the INDIRECT function to our calculation. Now, the INDIRECT function changes the text into a reference to the proper range (Fig. 8)
=SUM(INDIRECT(B$1))
Fig. 8 INDIRECT function
Now, when I drag the formula to the right, I will have sums from each individual month (Fig. 9)
=SUM(INDIRECT(F$1))
Fig. 9 Sum from individual months
When I copy the formula one cell lower, I’m just changing SUM into AVERAGE (Fig. 11)
=AVERAGE(INDIRECT(B$1))
Fig. 1 SUM into AVERAGE
After I drag it to the right, I have proper results. I do the same with the remaining rows (Fig. 12)
=MIN(INDIRECT(B$1))
Fig. 12 Changing the remaining names
This way, we can quickly add more statistical operations for many months using proper name ranges and the INDIRECT function.
Sometimes, we need to calculate a cubic root, square root or an nth root of a number. How can we do that?
Cubic root, square root and an nth root
First of all, we can use the SQRT function to calculate a square root (Fig. 1)
=SQRT(A3)
Fig. 1 SQRT function
And here we have our results. There is also the POWER function used for calculating the square root with the use of inverse numbers. The number for a square root is 1 divided by 2 (1/2). (Fig. 2)
=POWER(A3,1/2)
Fig. 2 POWER function
As we can see the results are the same. We can even use a simple symbol of raising a number to a power. In the case of a cubic root it will be the number from cell A3 (2), then a caret (Shift + 6). Then, the reverse of a cubic, which is 1/3 written in a parenthesis (Fig. 3)
=A3^(1/3)
Fig. 3 Cubic root
And we have our cubic roots. In the case of the fourth root, we will use the same calculation, which is a caret and, this time, 1/4 (Fig. 4)
=A3^(1/4)
Fig. 4 Fourth root
And we have our fourth root. In the case of the tenth root, I can even write this number as 0.1 (Fig. 5)
=A3^0.1
Fig. 5 Tenth root
And, just like that we have our tenth root (Fig. 6)
Fig. 6 Final results
Depending on the situation, use the calculation you understand the most.
Sometimes, we need to insert a population pyramid in Excel or a chart comparing two values.
Population Pyramid Chart
One part of the chart is on the left, while the other is on the right. In order to create something like population pyramid we have to insert a proper chart. I’m using data from Poland as I’m an Excel lover from Poland. We have to have one part of negative values, which will go to the left side of the chart. If you want to do it, you have to write ‑1 in one cell, then copy it and select all values where we want to change the sign, then use the Paste Special option (or use the Alt + Ctrl + V shortcut). In the Paste Special window, select the Values radio button and the Multiply radio button in the Operation section. It means that we will be multiplying ‑1 by the selected values (Fig. 1)
Fig. 1 Paste Special window
Now, we have negatives and positives, which is the left and the right side of our chart. We can insert the chart. Let’s select one cell and go to the Insert tab and find the Clustered Bar option (Fig. 2)
Fig. 2 Clustered Bar option
The chart that appeared isn’t good enough for us. The first modification we want to implement is changing the title. Let’s write ‘Population Pyramid’ (Fig. 3)
Fig. 3 Chart Title change
I also want to modify bars. Let’s click on them, then press Ctrl +1. Format Data Series window will appear where we have to select 100% in the Series Overlap section and set the Gap Width to 10% (Fig. 4)
Fig. 4 Series Overlap and Gap Width options
Now, the bars are larger. The next thing is the Vertical Axis. Click Ctrl + 1 shortcut and go to the Labels tab. Set the Label Position to Low which, in our case, is the left side. In the Specify interval unit let’s leave 1 (Fig. 5)
Fig. 5 Label Position and Specify Interval Unit options
Now, the chart looks much better, but I don’t like the vertical lines here. Let’s click it and press the Delete key (Fig. 6)
Fig. 6 Vertical lines not needed
The next thing we want to change is the horizontal axis where we have person values. It means that we don’t need any negative values. We have to select it, press Ctrl + 1, then in the Format Axis window, we need to go to the Numbers bar. In the Format Code bar, we need to repeat the percent code for the negative values. Let’s add a semicolon, then write 0.00% without any minus sign. Then, we click on the Add button (Fig. 7)
Fig. 7 Format Code bar
Now, we have positive values on the left and on the right side of the chart. What I’m still missing is Data Labels. We have to press the plus sign and select the Data Labels checkbox (Fig. 8)
Fig. 8 Adding Data Labels
We can see that the label font is too big. Let’s make it smaller by selecting the labels and changing the font size on the ribbon (Fig. 9)
Fig. 9 Changing the font size
There is still one more thing we need to change. There are minus signs on the left side of the chart. So, let’s press the Ctrl + 1 shortcut and go to the Format Data Labels window. We have to open the Number tab and write the percentage once again. We separate the percentages with a semicolon, so that the percentage from the left side corresponds to the left part of the chart, and the right corresponds to the right part. Then, we press Add (Fig. 10)
Fig. 10 Two percentages
In the end, I want to change the position of the legend. Let’s select the legend and press Ctrl + 1. We can see many options concerning the position of the legend (Fig. 11)
Fig. 11 Legend Positions
However, I want to change the position manually. Let’s drag it to the right and change its size. We can also change the size of the bars. There are many more modifications that you can implement. My Population Pyramid is finished and looks as follows (Fig. 12)
Sometimes, we want to add a degree sign to numbers. How can we do it?
Add degree sign (°C) to numbers
Let’s say we want to show temperature in degrees Celsius or Fahrenheit. The first thing we want to do is inserting the correct sign. We can either copy it from the internet or we can choose the Symbol command from the Insert tab (Fig. 1).
Fig. 1 Adding a symbol
In the Symbol window that appears, we’re choosing the Latin‑1 Subset (1), then the Degree Sign, whose name we can see at the bottom of this window. We click the Insert and Close buttons (Fig. 2).
Fig. 2 Symbol window
We can see the Degree Sign in cell D1. After adding C, we have the full symbol. Now, we can copy the symbol in the edit mode, then select all cells with numbers to which we want to add the sign. Then, we press the Ctrl + 1 shortcut. In the Format Cells window we’re choosing the Custom Category, then we’re choosing 0 and we can paste the °C sign. In the Sample bar we can preview what our final text will look like in the end. If we want to make sure that our text will be show as a text in the end, we can write it in double quotes. In this case, it’s a bulletproof solution (Fig. 4).
Fig. 4 Format cells window
And we have our signs correctly inserted (Fig. 5).
