Bankers Rounding (Half-Way-Even) to Dollars, Pennies and Hundreds

Today, we are going to talk about bank round­ing, or half-way-even rounding.

In a clas­sic Excel round­ing, if we have 5 as the most sig­nif­i­cant num­ber for us, we always go up. It means that errors go up. To make the dis­tri­b­u­tion of our round­ings a bit more even, we some­times need to go down. That’s why we have the bank round­ing which goes around the near­est, even num­ber. Let’s take the num­ber of 7.50. Our whole num­ber, which is 7, is an odd num­ber. By round­ing up, we go to the near­est, even num­ber, which is 8. How­ev­er, if our whole num­ber is even, like in 8.50, we go down to 8. The dis­tri­b­u­tion of the round­ing val­ues is more even, which means that an error is less sig­nif­i­cant. It’s impor­tant espe­cial­ly in bank­ing, that’s why we call it bank round­ing (Fig. 1)

How can we use it in Excel, when we know that the stan­dard ROUND func­tion will always go up if the most sig­nif­i­cant num­ber is 5. The answer is that we have to cre­ate a dif­fer­ent for­mu­la. The for­mu­la that works for me, where I haven’t seen any errors is a bit com­pli­cat­ed, how­ev­er, when we start work­ing with this, it will be quite easy to under­stand. First, we have to check whether our num­bers are half way or not. We can see that two num­bers are half way. In the remain­ing ones, the sit­u­a­tion is clear, i.e. we know that we want to go up or down (Fig. 2)

If we want to check whether to go up or down, we can use the MOD func­tion, where we should write 1 to check what the divider is, i.e. we want to check what is the rest after divid­ing it by 1 (Fig. 3)

=MOD(A2,1)

We can see the results (Fig. 4)

If the num­ber is halfway, we need to round it to the near­est whole num­ber. It means that we have to check whether the result of the MOD func­tion is equal to 0.5. If it is, we should use the MROUND func­tion that returns the desired num­ber round­ed to the desired mul­ti­ply of even num­bers from our exam­ple. It means that we want to round 207.50 to the near­est mul­ti­ply of even num­bers, which is 2. In oth­er cas­es, we just use the ROUND func­tion to whole num­bers. In our case, the num­ber of dig­its is 0 (Fig. 5)

=IF(MOD(A2,1)=0.5,MROUND(A2,2),ROUND(A2,0))

Look­ing at the results, we can see that when we have an odd, whole num­ber and we are halfway, we go up to the near­est even num­ber. If we have an even num­ber and we are halfway, we go down to the near­est, even num­ber. How­ev­er, if we pass the halfway thresh­old, we start to go up (Fig. 6)

Now, let’s go to the round­ing to 100. We copy our pre­vi­ous for­mu­la, which aimed at round­ing to whole num­bers. Now, we want to round to 100, which is 100 times more than in the pre­vi­ous exam­ple. It means that we have to mul­ti­ply all num­bers in our for­mu­la by 100 (Fig. 7)

=IF(MOD(E2,100)=50,MROUND(E2,200),ROUND(E2,-2))

And just like that we have prop­er results. We went halfway in two cas­es. Since we’re round­ing to 100, we went up in the num­ber 2350. In the sec­ond halfway num­ber, which is 2450, we already had an even num­ber, so we went down to the near­est even num­ber (Fig. 8)

If I change num­bers in the last exam­ple and pass the halfway thresh­old, we can see that the round­ing goes up (Fig. 9)

If we want to use the bank round­ing to dec­i­mal places, e.g. for pen­nies, it will be a bit more prob­lem­at­ic. Here, we have to remem­ber that we are check­ing the sec­ond dig­it to know whether it’s an even val­ue. If the dig­it is odd, we round up, and if the num­ber is even, we go down to the near­est even num­ber. We have to remem­ber that if we pass the halfway thresh­old, we start to go up (Fig. 10)

If we want to work with this banker’s round­ing rule with dec­i­mal places, we can­not use the MOD func­tion because, when we work with e.g. pen­nies, the MOD func­tion results will have some inac­cu­ra­cies in dig­its that are far away, like on the fif­teenth place after the dot, or so. In such a sit­u­a­tion we won’t be able to check whether it is halfway, because the far places show that it’s not (Fig. 11)

When we work with whole num­bers, we can use the MOD func­tion because they are pre­cise. A num­ber in halfway will be returned by the MOD func­tion as still a halfway num­ber. In some oth­er sit­u­a­tions there can be some inac­cu­ra­cies, but they don’t con­cern us, because they will work cor­rect­ly. Here, we care only about halfway num­bers, which stay halfway in the MOD func­tion (Fig. 12)

In the case of pen­nies, we have to use a dif­fer­ent method. This method will show the num­ber of dig­its we want to have. Let’s assume that we want to have five dig­its after dot. In such a case, we have to change our num­ber to text by using the TEXT func­tion, where we write the for­mat num­ber as 0.00000 (Fig. 13)

=TEXT(H2,“0.00000”)

Just like that we have num­bers as text, but with five-dig­it pre­ci­sion. Now, let’s look for halfway num­bers. We can see that halfway is 500 as the last three dig­its (Fig. 14)

Now, we have to take out those three dig­its. Since they are locat­ed on the right side, we can use the RIGHT func­tion, where we write 3 (Fig. 15)

=RIGHT(TEXT(H2,“0.00000”),3)

And just like that we have only three, last dig­its. When we are in halfway, we will have exact­ly 500. Here, we also have to remem­ber about dif­fer­ent round­ings. If the num­ber before 500 is odd, we go up, but then it’s even, we go down (Fig. 16)

We have to check whether the num­ber is equal to 500 using the IF func­tion. We have remem­ber that we are work­ing with text, so we need to write 500 in dou­ble quote. Then, we can sim­ply use the MROUND func­tion, just like in pre­vi­ous exam­ples, but this time our mul­ti­ply isn’t 2, but 0.02. If we aren’t halfway, we can sim­ply round our val­ues using the ROUND func­tion (Fig. 17)

=IF(RIGHT(TEXT(H2,“0.00000”),3)=“500”,MROUND(H2,0.02),ROUND(H2,2))

We can see that the round­ing went up when the sig­nif­i­cant num­ber was odd, and the round­ing went down when the sig­nif­i­cant num­ber was even. When we passed the halfway thresh­old, we start­ed round­ing up. We have a for­mu­la that works with dec­i­mal places and the banker’s round­ing, how­ev­er, we have to remem­ber about our assump­tions. We set the pre­ci­sion to five dig­its, but only the last three of them are impor­tant for us. Based on the last three dig­its, the round­ing uses the MROUND or the ROUND func­tion (Fig. 18)

https://www.youtube.com/watch?v=kUOXZFXwPzw