"Banker's Algorithm" Revealed! Use custom formulas in front-end tables to achieve "rounding to 50%"

What is the bank's profit model? Three words: poor information! Collect funds from depositors, and then lend them out, and the so-called "profit" is the difference in interest.
In my country, the People's Bank of China stipulates that the 20th of the end of each quarter is the bank interest settlement date, and interest is settled four times a year, so it is necessary to calculate the interest paid to depositors very frequently every year. When calculating interest, how the decimal point is treated becomes important and becomes a key detail in determining how much profit is made.

(The picture comes from the Internet)

Usually, we all know that rounding is often used when keeping the decimal point. Numbers less than 5 are rounded up, and numbers greater than or equal to 5 are rounded up and rounded. Since the numbers in all bits are calculated naturally, according to the probability calculation, the rounded numbers are evenly distributed between 0 and 9.
We might as well use 10 deposit interest calculations as a model and think about this algorithm as a banker:

The discarded values include: 0.000, 0.001, 0.002, 0.003, 0.004. For the bank, the discarded content no longer needs to be paid, so the discarded part can be understood as "earned".

Five entries, the rounded content includes: 0.005, 0.006, 0.007, 0.008, 0.009. For the bank, the rounded content will cause a loss, and the corresponding loss amount is: 0.005, 0.004, 0.003, 0.002, 0.001.
Because the rounding and rounding numbers are evenly distributed between 0 and 9, for the banker, the profit for every 10 deposits of interest due to rounding is:
0.000 + 0.001 + 0.002 + 0.003 + 0.004 - 0.005 - 0.004 - 0.003 - 0.002 - 0.001 = -0.005
In general, for every 10 transactions of interest, the rounding calculation will result in a loss of 0.005 yuan, that is, a loss of 0.0005 yuan per interest calculation. Assuming a bank has 50 million depositors, the amount lost each year simply due to rounding errors is:

 public class Client {  
     public static void main(String[] args) {  
          //银行账户数量，5千万  
          int accountNum =5000*10000;  
          //按照人行的规定，每个季度末月的20日为银行结息日  
          double cost = 0.0005 * accountNum * 4 ;  
          System.out.println("银行每年损失的金额：" + cost);  
     }  
}

The result of the calculation is: "Amount lost annually by the bank: 100000.0". You may find it hard to believe that rounding up a small act can result in a loss of 100,000 per year. But in a real environment, the actual loss may be more.
This situation was discovered by private bankers in the United States, and a revised algorithm was proposed to solve this situation:
"When the value of the rounded-off bit is less than 5, it is directly rounded off;
When the value of the rounded bit is greater than or equal to 6, it is rounded up after rounding;
When the value of the rounded digit is equal to 5, there are two cases: if there are other digits (non-0) after 5, they are rounded up after rounding; if there is 0 after 5 (that is, 5 is the last digit), according to The parity of a single digit is used to determine whether a carry is required, the odd number is carried, and the even number is discarded. "
All of the above can be summed up in one sentence: round up to five, add one if it is not zero after five, add one if it is zero after five, round up if it is even before five, and add one if it is odd before five.
Let's take an example to take 2 digits of precision:
10.5551= 10.56
10.555= 10.56
10.545= 10.54

(The picture comes from the Internet)

In short, with the banker's algorithm like "rounding to 50%", data can be processed more scientifically and accurately.

In practical applications, the most common situation where we use the banker's algorithm is in large-scale table calculations, but in table calculations, a series of built-in formulas need to be compounded. It is cumbersome and complicated for ordinary users to understand and use in the end.
In order to solve this problem more conveniently, we can use custom functions to fulfill such requirements, so that users only need to remember the custom function names to use functions with such a rule.
Next, let's take a look at how to quickly implement "rounding to 50%" in the front-end table.
We first need to define the name of the function, and the number of parameters in it. Because what we want to achieve is to pass two parameters, "1" is the value that needs to be reduced, and "2" is to retain the number of digits after the decimal point, and the reduction is performed according to the value and the number of digits.

 var FdaFunction = function() {
             this.name = "FDA";
             this.minArgs = 1;
             this.maxArgs = 2;
         };

The next step is to make it easier for users to understand and use, we need to add some descriptions to this custom function:

 FdaFunction.prototype.description = function() {
             return {
                 description: "对value进行四舍六入五留双修约，保留小数点后指定位数",
                 parameters: [{
                     name: "value",
                     repeatable: false,
                     optional: false
                 }, {
                     name: "places",
                     repeatable: false,
                     optional: false
                 }]
             }
         }

Finally, the key step is reached, that is, the logical operation of the function is placed in evaluate, we will make some judgments on the incoming value, and will use regular expressions to do some matching. To achieve "50% double", then we also need to judge the last bit value that needs to be repaired to decide whether to carry. For details, please refer to the attached complete demo.

 FdaFunction.prototype.evaluate = function(context, num, places) {

            if (!isNaN(parseInt(num)) && !isNaN(parseInt(places))) {
                console.log("evaluate")
                 num = numGeneral(num);
                if (!isNumber(num)) {
                    return num;
                }
                var d = places || 0;
                var m = Math.pow(10, d);
                var n = +(d ? num * m : num).toFixed(8); // Avoid rounding errors
                var i = Math.floor(n),
                    f = n - i;
                var e = 1e-8; // Allow for rounding errors in f
                var r = f > 0.5 - e && f < 0.5 + e ? (i % 2 == 0 ? i : i + 1) : Math.round(n);
                var result = d ? r / m : r;

                if (places > 0) {
                    var s_x = result.toString();
                    var pos_decimal = s_x.indexOf(".");
                    if (pos_decimal < 0) {
                        pos_decimal = s_x.length;
                        s_x += ".";
                    }
                    while (s_x.length <= pos_decimal + places) {
                        s_x += "0";
                    }
                    return s_x;
                } else {
                    return result;
                }
            }else{
                 return "#VALUE!";
            }
           
         }

Experience downloading the full demo:
https://gcdn.grapecity.com.cn/forum.php?mod=attachment&aid=MTkyNjA3fDQzMTk2ZmRhfDE2NTAyNzI0NTV8NjI2NzZ8MTQxNjY4

If you want to know more about custom formulas, you can check the link:
https://demo.grapecity.com.cn/spreadjs/SpreadJSTutorial/features/calculation/custom-functions/purejs

Extended reading

SpreadJS pure front-end form control