Hi.
I have spreadsheets doing various calculations of electricity bills and the like.
Now & then I get a discrepency due to the way the companies do rounding.
They use ‘bankers rounding’ or ‘even rounding’
Is there a function I can use that will do this sort of rounding?
Read these guidelines and ask a good question. In particular, explain what ‘bankers rounding’ or ‘even rounding’ is.
Also, read about the ROUND function and its kin.
“In banker’s rounding a number with a final digit of 5 is rounded to the nearest even number rather than to the next larger number as you might expect. The idea is that statistically half of a sample of numbers are rounded up and half are rounded down.”
I want to know if there is a function that will do that sort of rounding.
If i spend days totally researching it, I would probably find the answer, but I came here hopefully to save myself lots of research about something I’m not totally understanding myself.
I was hoping that LibreOffice experts might know how the ROUND functions work and explain which one I will need.
If I understood well each way:
- Use
=MROUND(reference;0.02)for the ‘bankers rounding’; and if cents are not taken into account use=MROUND(reference;2). - Use
=ROUND(reference;2)for the ‘even rounding’; and if cents are not taken into account use=ROUND(reference;0). -
EDIT: Use
=TRUNC(reference;2)to remove excess decimal places; and if cents are not taken into account use=TRUNC(reference;0).
LibreOffice Help on TRUNC,
ROUND and MROUND.
Tested with LibreOffice 6.4.7.2 (x86); OS: Windows 6.1.
Add Answer is reserved for solutions. If you think the answer is not satisfactory, add a comment below, or click edit (below your question) to add more information. Thanks.
Check the mark (
→ 
) to the left of the answer that solves your question.
If the answer helped you, you can mark the up arrow (
) that is on the left (to vote, you need to have karma of at least 5).
OK, so I’m well confused now… I am multiplying 27 by -0.225 and on my calculator I get -6.075 I know that my electric company is also getting -6.075 but they are rounding it to -6.07
Lets say the 27 and the -0.225 are in cells A1 and A2 I do the following formulas
In B2 I have =A1*A2 (What I see is -6.075)
In C2 I have =MROUND(B2,0.02) (What I see is -6.08)
I’m looking for a function that will return -6.07 as the result, as that is what the electricity company is doing. I was assuming they were using bankers rounding?
Can anyone figure this out???
Use =TRUNC(A1*B1;2).
Look at the ROUNDDOWN().
Actually it’s not easy to implement. MROUND as suggested above would round any number to an even number; while banker’s rounding is only applied to the specific case of rounding exact half (x.5) to even neighbor (thus it rounds x.5 down in half cases, and up in the other half cases).
tdf#142922 is fixed now in v.7.2, allowing to use Round BASIC function in a user-defined function for proper banker’s rounding.