16 April 2023

More accurate Excel rounding to the international specification

Peter Deegan Microsoft 365, Microsoft Excel, Microsoft Office, Microsoft Office for Mac, Office 2007, Office 2010, Office 2013, Office 2016, Office 2019, Office 2021 / Office LTSC, Office 365

Here’s how to round numbers more accurately than Excel’s in-built Round() and other functions. This method uses the internationally recognized standard for rounding (yes, there is one), we’ll show the long formula, how it works plus the Let() and Lambda() versions that can make your worksheet easier to deal with.

Rounding ‘Ties to Even’

The ISO (International Organization for Standardization) specifies that numbers should be rounded using the “Ties to Even”, “Round Half to Even” or “Bankers’ Rounding” method. This rounding method rounds numbers to the nearest even number if the number to be rounded falls exactly between two possible rounded values.

It solves the bias that’s present in the standard Round() function and the way we were taught rounding in school see Why Excel’s Round() function is wrong

This method works, assuming the number range is evenly distributed between odd/even values. Normally you’d expect that to happen but there could be cases where the original numbers are skewed one way.

We found various formulas to give ISO standardized rounding and we’ll share one with you in this article. To the best of our knowledge this formula applies the standard method defined in IEEE Standard for Floating-Point Arithmetic (IEEE 754) also known as ISO/IEC 60559:2020. Our tests appear to confirm that. However, our mathematical knowledge is limited, and we may have overlooked something.

All that said, the standard Excel Round() function is more than enough for many, if not most, purposes.

We think Microsoft should provide a rounding function to match the ISO standard (perhaps Round.Math() ? ). Don’t hold your breath. These days Microsoft isn’t interested in anything unless it has the words ‘Cloud’ or ‘AI’, preferably both ☹️

Formula for ISO standard rounding in Excel

This formula is fairly clear how it works, compared to the others we looked at. It also tests that the incoming value is a number and gives control over what error to return if something isn’t right.

In Excel, you can use the following formula to round numbers to ISO specification. Change A1 to whatever cell reference you like:

=IF(ISNUMBER(A1), IF(ISODD(ROUND(A1,0)), ROUND(A1,0), IF(MOD(ROUND(A1,0),2)=0, ROUND(A1,0), ROUND(A1,0)-1)), "")

Here’s the same formula split into lines and colorized for easier reading, courtesy of the Excel Labs Advanced formula environment.

‘Real’ rounding – step by step

Let’s explain the formula step by step:

IF(ISNUMBER(A1), …): This checks if the value in cell A1 is a number. If it is, the formula proceeds to perform the rounding, otherwise, it returns an empty string (“”).
IF(ISODD(ROUND(A1,0)), ROUND(A1,0), …): if the rounded value of A1 is an odd number, use the ROUND function to round to the nearest integer.
IF(MOD(ROUND(A1,0),2)=0, ROUND(A1,0), ROUND(A1,0)-1): If the rounded value of A1 is an even number (not ISODD() ) , the formula first uses the ROUND function to round A1 to the nearest integer. THEN if that rounded number an even number (determined using MOD()), the formula returns the rounded number. If MOD() isn’t zero, it’s an odd number, the formula subtracts 1 from the rounded value to get the nearest even number.
Finally, the formula returns the rounded value, or an empty string if A1 is not a number.
1. The empty string can be replaced by #N/A to clearly show an error or NUL() for an empty cell.