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)
This task is quite easy as we have proper functions, however we have to understand how the rounding works in Excel.
The most important thing is the most significant digit. When we round to whole numbers, e.g. to a dollar, the most significant number is the first digit after dot. If this number is 4 or less, the rounding goes down. However, if the number is 5 or more, the rounding goes up. If we remember this, we can start rounding in Excel (Fig. 1)
Fig. 1 The digit after dot
Now, we can use the ROUND function, give it a number and write the number of digits we want to round up. Since we want to round to whole numbers, we have to write 0 (Fig. 2)
=ROUND(A2,0)
Fig. 2 ROUND function
And just like that we have rounded to whole dollars. We can see that in the case of 49 cents, we are going down, and in the case of 50 cents, we are going up. And from 5 to the end of the list, we are only going up, as we have passed the halfway threshold (Fig. 3)
Fig. 3 Halfway threshold
If we want to round to pennies, the situation is almost the same. However, in the case of rounding to whole dollars, the most significant number was the first one after dot, and in this case, the most significant digit is the third one. If the digit is 5 or greater, it will go up by a penny. Let’s use the ROUND function again, give it a number, but now we have to write 2 in the place of the number of digits. As we know, a penny is one hundredth of a dollar, which means that it’s two digits after dot (Fig. 4)
=ROUND(D2,2)
Fig. 4 Rounding to pennies
And just like that, we have rounded numbers to pennies. We can see that when we passed the halfway threshold, we started rounding up (Fig. 5)
Fig. 5 Results
In Excel, we can round even to bigger multiplications of ten, e.g. one hundred. In this case, the second number is the most significant one. We have to remember that when rounding to one hundred or any bigger, whole numbers, we have to write the number of digits as negative. Since we are rounding to one hundred, we have to write ‑2, as there are two 0s, or two digits to the left from 1. When we go to the right from the dot, as it was in previous examples, we have to write a positive number, and when we go to the left, we write a negative number (Fig. 6)
=ROUND(G2,-2)
Fig. 6 Rounding to 100
As we can see, when we went halfway, i.e. 50, we rounded up (Fig. 7)
In the previous post, I was talking about a dominant, which is the most frequent number. Today, we are going to find the most frequent text.
Most frequent text
This task is a bit harder, because the MODE function that we used previously needs numbers. It means that we have to convert text into numbers. To do so, we can use the MATCH function. It means that we will be looking for our whole text in our whole text. We want the exact match (Fig. 1)
Fig. MATCH function to look for the exact match
While using the MATCH function, Excel starts calculations from the top. It means that each puppy in the range is referring to the first one on the list, which is on the first position is presented as 1 (Fig. 2)
Fig. 2 Puppy gets number 1
Looking at the second example, the first kitten is in position number 2, so each kitten will refer to the same number (Fig. 3)
Fig. 3 Kitten gets number 2
Now, that we have our text connected to numbers, we can add the MODE.MULT function to return the most frequent numbers, i.e. positions of text (Fig. 4)
=MODE.MULT(MATCH(A2:A9,A2:A9,0))
Fig. 4 MODE.MULT function to return the most frequent numbers
And we have the results (Fig. 5)
Fig. 5 Results
Since we have the positions, we can add the INDEX function to return text connected to those positions (Fig. 6)
=INDEX(A2:A9,MODE.MULT(MATCH(A2:A9,A2:A9,0)))
Fig. 6 INDEX function to return text
Just like that we have our solutions in the Dynamic Array Excel (Fig. 7)
Fig. 7 Solutions in Dynami Array
In the classic Legacy Excel, we should select more cells and use the Ctrl + Shift + Enter key combination to put our results in all cells (Fig. 8)
Fig. 8 Key combination
However, we are in DA Excel, so we have a simpler solution. At the end, we want to check what happens when the text appears only once in a range. We can see that the MODE.MULT function returns the #N/A error (Fig. 9)
Fig. 9 #N/A error
When text appears the same number of times, in our case it’s cat and dog, the MODE.MULT function will return those results (Fig. 10)
Today, we want to find the most frequent value/number.
Most frequent number
From statistical point of view, we want to find a dominant. This task is quite easy in Excel, however there are some nuances. It’s easy thanks to the MODE function. We can use it to find a single dominant. In this function, we just select the range, and Excel will quickly calculate it for us (Fig. 1)
=MODE(A2:A9)
Fig. 1 MODE function
We have to remember that while using the MODE or MODE.SNGL function, Excel will return only one number. It means that we cannot be sure that only this number is the most frequent one. There is a way out, as we also have the MODE.MULT function (Fig. 2)
=MODE.MULT(A2:A9)
Fig. 2 MODE.MULT function
After using this function, we can see that in our example there are two most frequent numbers: 5 and ‑1. The MODE function showed only the number that appeared at the beginning of the range. When I change the order of the two first numbers in the range, we can see that now the MODE.SNGL function returns ‑1. The MODE.MULT function will still return two number, however in different order (Fig. 3).
Fig. 3 Different order of numbers
When we use the MODE.SNGL function in a range where there isn’t any most frequent numbers and each number occurs only once, the function will return the #N/A error. In our range of Wages, each wage appears only once (Fig. 4)
Today, we are going to learn how to insert scroll bars in Excel and where we can use them.
How to Insert Scroll Bar
In our first example, we have a PMT function. The scroll bars help us change our value for the PMT function more rapidly (Fig. 1)
Fig. 1 PMT function
Let’s insert a scroll bar from the film Excel tips 43, when I was changing the time with a scroll bar. Let’s start with the Developer tab. It’s turned off by default, so we have turn it on. Just right click on the ribbon and choose the Customize the Ribbon option (Fig. 2)
Fig. 2 Customize the Ribbon option
In the window that appeared we have to find the Developer tab, select the proper checkbox and press OK (Fig.3)
Fig. 3 Developer checkbox
Now, we can go to the Insert option and select the Scroll Bars in the Form Controls area. We aren’t interested in ActiveX Controls because they are connected with VBA, and we need something simple. Now, we can draw our scroll bar. When we are holding the Alt key, Excel will fit our scroll bar to cell borders. We don’t always want to do it, but we have such an option. Even after selecting our scroll bar area, when we click the Alt key, Excel will still fit it to cell borders (Fig. 5)
Fig. 5 Cell borders
In our case it isn’t so important. Now, we have our vertical bar (Fig. 6)
Fig. 6 Vertical bar
And horizontal bars (Fig. 7)
Fig. 7 Horizontal bars
Let’s look at properties of the scroll bar. We have to right click to select the scroll bar and right click again to go to the Format Control option (Fig. 8).
Fig. 8 Going to scroll bar properties
In the window that appeared, we have some options, such as Format Control which is now 26, Minimum value which is 0, Maximum value which is 100. We have to remember that scroll bars can have only integer numbers from 0 to 30.000. In our example, I want to work with time and I want the scroll bar to have a 1‑minute precision. It means that I have to set the maximum value to 1440, as this is the number of minutes in one day. The next option, which is Incremental change, is connected to the arrows at the ends of our scroll bars. Then, we have Page change which is connected to clicking on our scroll bar. However, the most important option here is the Cell link bar. We can connect Current value to Cell link. Let’s assume that I want to connect our scroll bar to cell E1. I’m clicking on the cell and pressing OK (Fig. 9)
Fig. 9 Connecting Current value to a cell
Now, the value in cell E1 changed into 26 because that’s the current value in our scroll bar (Fig. 10)
Fig. 10 The same value
When I move the scroll bar, the value in cell E1 is also changing in accordance to the value on the bar. When we click on the arrows on our scroll bar, the time is changing minute by minute downwards or upwards. When we click on the scroll bar, but not on the arrows, we move by 10 minutes upwards or downwards, depending on which part we are clicking. We can move even more rapidly by catching the slider and moving it.
Our value from the scroll bar is an integer. Sometime, though, we need time or percentages. The question is, how can we changed integers into time or percentages? In our example, we have to just divide the value by 1440 and we will get the time in minutes (Fig. 11)
