Forum Discussion
Specific Rounding Rules
Howdy! My job has a bit of a weird rule for their rounding technique. I was curious if there is a way I can format my cells or write a function that allows for it.
The rule is as follows:
If the number your rounding is proceeded by a 5, instead of rounding up, you look at the number being rounded. If it is even, you rounding down else you round up.
Basically you want the number your rounding to remain at an even number if it's being rounded by 5.
Is there a way to get this rule into excel?
Thanks in advance for the help!
5 Replies
That's so called Banker's rounding. In Excel environment Power Query uses it by default. If formulae you may google for "excel bankers rounding" for workarounds and samples, e.g. .net - Banker's rounding formula in Excel - Stack Overflow
I'd never heard of "Banker's Rounding," wondered WHY? So for others who've never heard of it and the reason for it, here's what Google told me. And it makes sense!
Specific rounding rules refer to the guidelines used to adjust numbers up or down based on decimals, accuracy requirements, or calculation standards. These rules help ensure consistency, especially when dealing with pricing, measurements, statistics, or financial data. For example, some systems round 0.5 up, while others use banker's rounding to keep values balanced. In digital apps and tools, rounding rules can affect how data is displayed or processed behind the scenes. Even modern conversational apps like rely on precise calculations when handling message storage, file sizes, and usage statistics, making accurate rounding methods important for smooth performance.
Can you please give some examples as it's not clear (to me anyway) what you mean.
For rounding to whole numbers:
=IF(A1-INT(A1)=0.5, IF(MOD(INT(A1),2)=0, INT(A1), INT(A1)+1), ROUND(A1,0))
