Forecasting
There’s More Than One Way to Round a Number
When we were in school, we all learned the general rule for rounding – if a significant digit is followed by a number that is greater than or equal to 5, we round the number up, otherwise we round the number down. Not surprisingly, this is the behavior we see in Excel, as the ROUND function follows this general rule.
However, this method of rounding tends to create an upward bias. For this reason, another algorithm for rounding numbers has generally become the default when it comes to computer science and software development – section 4 of the IEEE Standard for Floating-Point Arithmetic (IEEE 754) (otherwise known as “rounding to the nearest” or “Bankers’ Rounding”). For example, the Math.Round method in Microsoft’s .NET Framework employs this algorithm.
Developed by the Institute of Electrical and Electronics Engineers (IEEE), this algorithm is designed to minimize rounding bias that results from consistently rounding a midpoint value in a single direction. The way it works is, numbers, which are equidistant from the two nearest integers, are rounded to the nearest even integer. This means that a number like 63.5 would get rounded to 64.0, while a number like 62.5 would get rounded to 62.0. Below is an example generated in VBA Editor.
Essentially, values with an even significant digit (e.g., 61.45) are rounded down, while those with odd significant digits (e.g., 61.55) are rounded up. Other decimal fractions round as you would expect. Only significant digits followed by a 5 get this “special” treatment.
However, this method of rounding tends to create an upward bias. For this reason, another algorithm for rounding numbers has generally become the default when it comes to computer science and software development – section 4 of the IEEE Standard for Floating-Point Arithmetic (IEEE 754) (otherwise known as “rounding to the nearest” or “Bankers’ Rounding”). For example, the Math.Round method in Microsoft’s .NET Framework employs this algorithm.
Developed by the Institute of Electrical and Electronics Engineers (IEEE), this algorithm is designed to minimize rounding bias that results from consistently rounding a midpoint value in a single direction. The way it works is, numbers, which are equidistant from the two nearest integers, are rounded to the nearest even integer. This means that a number like 63.5 would get rounded to 64.0, while a number like 62.5 would get rounded to 62.0. Below is an example generated in VBA Editor.
Essentially, values with an even significant digit (e.g., 61.45) are rounded down, while those with odd significant digits (e.g., 61.55) are rounded up. Other decimal fractions round as you would expect. Only significant digits followed by a 5 get this “special” treatment.