Today, we want to learn how to create a chart on which we have unique targets for each month, group, shop, etc.
Target Chart with Unique targets
The most important task for us is to create proper data. When we do it in the right way, the rest will be much simpler. Let’s start with creating a Base series, which will be the foundation of our chart. We have to check if the income is larger than the target. It means that we have to make a logical test with the IF function. If it’s correct, we want the smaller value, which is the target value. In the other case, we want the income (Fig. 1)
=IF(B2>C2,C2,B2)
Fig. 1 IF function for the Base series
And just like that, we have our foundation. Now, we can move on to the next step. If the income is larger than the target, we want to know how much larger it is. We will create it in the Upper column. We start with the IF function again, and we are checking if the income is larger than the target. If so, we want to deduct the target from the income. In the opposite case, we want nothing, i.e. we want our chart to show nothing. It means that we cannot write just an empty text string in the formula in two double quotes. We have to return an error. The simplest way I know is using the NA function (Fig. 2)
Fig. 2 IF function for the Upper series
And just like that, we have our Upper series. The last thing to do is the Lower series. It is very similar to the previous formula, All we need to change is the ‘larger than’ sign into ‘smaller than’ sign, and deduct the income from the target (Fig. 3)
Fig. 3 IF function for the Lower series
Just like that, we have the second series.
Now, when we have a value in one of the Upper cells, we will have an error in the same row in the Lower cell, and vice versa. When our data looks OK, we can move on to inserting a chart. We have to select the Months column, as well and the Base, Upper and Lower series. Then, we go to the Insert tab (1), then to the bar chart, where we select the Stacked Column option (2) (Fig. 4)
Fig. 4 Creating a stacked column
Now, we have all we need. We have our Base. When our income is larger than the target, we have a blue part, and when we didn’t reach the target, we have green. Let’s change the colors, so that the differences are more visible. I also don’t like the horizontal lines, so let’s delete them by selecting them and clicking the Delete key. I will also widen the columns by selecting them and pressing Ctrl + 1. In the Format Data Series window on the right we can change the Gap Width to 70% (Fig. 5)
Fig. 5 Gap Width option
Now, I want to add our target. Since we have only column charts here, we can copy our target column, select the chart and press Ctrl + V. Now, we have our target, however, we don’t want to have another column. We can change it into a line chart with marks. We can do it by right-clicking on the target series in the chart. In the pop-up menu, we have a Change Series Chart Type option (Fig. 6)
Fig. 6 Change Series Chart Type option
In the Change Chart Type window, we can change our target by clicking on proper options (Fig. 7)
Fig. 7 Line with Markers option
However, there is one more option. We can go to the Insert tab, and when the series is selected, we choose a proper chart. The result is the same (Fig. 8)
Fig. 8 Line with Markers option
Now, we have to select our line with markers, then press Ctrl + 1 to open the Format Data Series window. When our line chart is selected, we have to go to the bucket part, and select the No line option in the Line part (Fig. 9)
Fig. 9 No line option
In the Marker part, we can use some built-in markers, like a line. However, when I’m using the line, it’s too thick for me, especially in a bigger size (Fig. 10)
Fig. 10 Bigger size
In such a case, we can do another trick. Let’s go to the Insert tab (1), then the Shape part (2), and select a simple Line (3) (Fig. 11)
Fig. 11 Line option
Now, while holding the Shift key, we can draw a line with our mouse (1), and modify it (2) (Fig. 12)
Fig. 12 Line modification
Now, we can copy the line, then select markers and paste it. I can see that my line isn’t long enough, so i’ll just make it longer, then paste it once again (Fig. 13)
Fig. 13 Longer line
Now, let’s insert data labels. In the Chart Elements window we have to select the Data Labels option. Excel,then will give me data labels for each series (Fig. 14)
Fig. 14 Chart elements window
However, I don’t want data labels for the target series, so I have to select it and just delete it. Now, It’s important that we have data labels for our upper part only when there is an upper part. In places with errors, there won’t be data labels. The same is with lower parts. Errors won’t show anything on the chart. Let’s change lower data label color by going to the Home tab and selecting white (Fig. 15)
Fig. 15 Color change
Let’s add the chart title. Let it be the contend of cell B1. We write = in the formula bar, then click the cell and press Enter (Fig. 16)
=‘E061’!$B$1
Fig. 16 Chart title
There is one more thing. I sometimes don’t want to show the target series in the legend. To remove it, we have to click on the legend, then once more on the target series in the legend, then press Delete (Fig. 17)
Fig. 17 Final chart
Now, I’m happy with my chart with unique target for months, groups, shops, etc.
Today, we are going to talk about bank rounding, or half-way-even rounding.
Bankers Rounding (Half-Way-Even) to Dollars, Pennies and Hundreds
In a classic Excel rounding, if we have 5 as the most significant number for us, we always go up. It means that errors go up. To make the distribution of our roundings a bit more even, we sometimes need to go down. That’s why we have the bank rounding which goes around the nearest, even number. Let’s take the number of 7.50. Our whole number, which is 7, is an odd number. By rounding up, we go to the nearest, even number, which is 8. However, if our whole number is even, like in 8.50, we go down to 8. The distribution of the rounding values is more even, which means that an error is less significant. It’s important especially in banking, that’s why we call it bank rounding (Fig. 1)
Fig. 1 Rounding to the nearest, even number
How can we use it in Excel, when we know that the standard ROUND function will always go up if the most significant number is 5. The answer is that we have to create a different formula. The formula that works for me, where I haven’t seen any errors is a bit complicated, however, when we start working with this, it will be quite easy to understand. First, we have to check whether our numbers are half way or not. We can see that two numbers are half way. In the remaining ones, the situation is clear, i.e. we know that we want to go up or down (Fig. 2)
Fig. 2 Halfway numbers
If we want to check whether to go up or down, we can use the MOD function, where we should write 1 to check what the divider is, i.e. we want to check what is the rest after dividing it by 1 (Fig. 3)
=MOD(A2,1)
Fig. 3 MOD function
We can see the results (Fig. 4)
Fig. 4 MOD function results
If the number is halfway, we need to round it to the nearest whole number. It means that we have to check whether the result of the MOD function is equal to 0.5. If it is, we should use the MROUND function that returns the desired number rounded to the desired multiply of even numbers from our example. It means that we want to round 207.50 to the nearest multiply of even numbers, which is 2. In other cases, we just use the ROUND function to whole numbers. In our case, the number of digits is 0 (Fig. 5)
=IF(MOD(A2,1)=0.5,MROUND(A2,2),ROUND(A2,0))
Fig. 5 Proper formula
Looking at the results, we can see that when we have an odd, whole number and we are halfway, we go up to the nearest even number. If we have an even number and we are halfway, we go down to the nearest, even number. However, if we pass the halfway threshold, we start to go up (Fig. 6)
Fig. 6 Even and odd numbers in data
Now, let’s go to the rounding to 100. We copy our previous formula, which aimed at rounding to whole numbers. Now, we want to round to 100, which is 100 times more than in the previous example. It means that we have to multiply all numbers in our formula by 100 (Fig. 7)
=IF(MOD(E2,100)=50,MROUND(E2,200),ROUND(E2,-2))
Fig. 7 Multiplication by 100
And just like that we have proper results. We went halfway in two cases. Since we’re rounding to 100, we went up in the number 2350. In the second halfway number, which is 2450, we already had an even number, so we went down to the nearest even number (Fig. 8)
Fig. 8 Rounding to 100
If I change numbers in the last example and pass the halfway threshold, we can see that the rounding goes up (Fig. 9)
Fig. 9 Rounding goes up
If we want to use the bank rounding to decimal places, e.g. for pennies, it will be a bit more problematic. Here, we have to remember that we are checking the second digit to know whether it’s an even value. If the digit is odd, we round up, and if the number is even, we go down to the nearest even number. We have to remember that if we pass the halfway threshold, we start to go up (Fig. 10)
Fig. 10 Rounding with decimal places
If we want to work with this banker’s rounding rule with decimal places, we cannot use the MOD function because, when we work with e.g. pennies, the MOD function results will have some inaccuracies in digits that are far away, like on the fifteenth place after the dot, or so. In such a situation we won’t be able to check whether it is halfway, because the far places show that it’s not (Fig. 11)
Fig. 11 Inaccuracies in digits
When we work with whole numbers, we can use the MOD function because they are precise. A number in halfway will be returned by the MOD function as still a halfway number. In some other situations there can be some inaccuracies, but they don’t concern us, because they will work correctly. Here, we care only about halfway numbers, which stay halfway in the MOD function (Fig. 12)
Fig. 12 The MOD function with whole numbers
In the case of pennies, we have to use a different method. This method will show the number of digits we want to have. Let’s assume that we want to have five digits after dot. In such a case, we have to change our number to text by using the TEXT function, where we write the format number as 0.00000 (Fig. 13)
=TEXT(H2,“0.00000”)
Fig. 13 Changing numbers into text
Just like that we have numbers as text, but with five-digit precision. Now, let’s look for halfway numbers. We can see that halfway is 500 as the last three digits (Fig. 14)
Fig. 14 Numbers with five-digit precision
Now, we have to take out those three digits. Since they are located on the right side, we can use the RIGHT function, where we write 3 (Fig. 15)
=RIGHT(TEXT(H2,“0.00000”),3)
Fig. 15 The RIGHT function taking out the last five digits
And just like that we have only three, last digits. When we are in halfway, we will have exactly 500. Here, we also have to remember about different roundings. If the number before 500 is odd, we go up, but then it’s even, we go down (Fig. 16)
Fig. 16 Three, last digits
We have to check whether the number is equal to 500 using the IF function. We have remember that we are working with text, so we need to write 500 in double quote. Then, we can simply use the MROUND function, just like in previous examples, but this time our multiply isn’t 2, but 0.02. If we aren’t halfway, we can simply round our values using the ROUND function (Fig. 17)
We can see that the rounding went up when the significant number was odd, and the rounding went down when the significant number was even. When we passed the halfway threshold, we started rounding up. We have a formula that works with decimal places and the banker’s rounding, however, we have to remember about our assumptions. We set the precision to five digits, but only the last three of them are important for us. Based on the last three digits, the rounding uses the MROUND or the ROUND function (Fig. 18)
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).
Sometimes, we want to find all the matched values and combine them into one text string, i.e., we want o VLOOKUP all matched values and then combine them.
VLOOKUP all matches and combine them
I will give you two solutions. The first solution will work from Excel 2019, and the second one will work in Dynamic Array Excel. First of all, we have to check if our representative comes from the country we are looking at. Let’s take a representative from Spain. There is only one representative of Spain in our example. We can check it using the IF function, where we have to create a proper logical test. We have to take into consideration the Country column, so let’s write it by selecting the column and clicking the F4 key to lock it. Then, we have to check if the values from the column are equal to just one country (Spain) by clicking the cell with the name of Spain (D4). It means that cell D4 will be compared to each cell from the Country column individually (Fig. 1)
Fig. 1 Comparison to each cell
The logical test will return TRUE only in the case of Spain. In the case of other countries, it will return FALSE. If the logical test is in an array, we can choose proper, corresponding result as a return value. We just need to select an array of the same size, which, in our case, is the Representative column. Then, we click the F4 key to lock it. When the logical test returns FALSE, we want to see an empty text string, so we have to write a space in double quotes. Let’s close the function with a parenthesis (Fig. 2)
=IF($A$2:$A$14=D2,$B$2:$B$14,” ”)
Fig. 2 IF function
Since I’m working in Dynamic Array Excel, Excel spills the results to the whole column (Fig. 3).
Fig. 3 IF function results
We can see that we have only one representative of Spain, and many empty cells. If we want to find the remaining representatives, we have to join the information we have. To do that, we can use the TEXTJOIN function, which is available from Excel 2019. We have to give the function a delimiter. Let’s write a comma and a space in double quotes. Then, we have to decide whether we want to ignore empty cells. We definitely do, so let’s write TRUE, or its shorter version, which is 1. Then, we just have to close the whole formula once again (Fig. 4).
After entering the formula and copying it down, we have our results (Fig. 5).
Fig. 5 TEXTJOIN function results
When we are in Dynamic Array Excel, we can use the FILTER function instead of the IF function. This function just filters our data. Let’s write FILTER function. Our data is located only in one column, which is the Representative column, so we have write the range and lock it with the F4key. Then, we have to create an array of the same size with logical values of TRUE or FALSE. It means that we have to create the same logical test as we did with the IF function. Let’s add the Country column (A2:A14), F4key to lock it, then an equal sign to check whether any of the value from the Country column is equal to Spain (D2). After closing it, we have a ready formula (Fig. 6).
=FILTER($B$2:$B$14,A$2:$A$14,=D2)
Fig. 6 FILTER function
We can see that Spain has only one representative, but Poland has got three (Fig. 7).
Fig. 7 FILTER function results
That’s why we have to put our FILTER function results into the TEXTJOIN function. And, once again, we have to write a comma and a space in double quotes. Then, we have to decide that we want to ignore empty cells and close the whole formula (Fig. 8)
=TEXTJOIN(“, “,1,FILTER($B$2:$B$14,A$2:$A$14=D2))
Fig. 8 TEXTJOIN with FILTER function
We can see that the results in the first solution and in the second solution are the same (Fig. 9)
