2022 Archives by date, by thread · List index

# Re: [libreoffice-users] ROUND function consistency

I agree this is not a bug and also find the different functions are not consistent in their approach. Some investigation and analysis shows that using bankers rounding results in less accumulated rounding error or bias when processing monetary values than the ROUND() function in Calc. I have also found that different jurisdictions may define their own rounding approach.
```
```
For instance the Australian Tax Office states that when calculating sales tax, portions of a cent 0.5 and below should be rounded down, above 0.5 rounded up. This is opposite to that performed by the ROUND() function but possibly has the logic of accumulating the rounding error in favour of the tax payer rather than the tax department and this is the approach I have adopted in my macro.
```
On 06/06/2022 07:01, Andrew Pitonyak wrote:
```
```
```
Sorry, this is NOT a bug. This is exactly the result I expect if I was using VBA in MSO. Read on to find out why.
```
```
I say this without looking at the code, but, you specifically told LO to use the VBA version of rounding, which is NOT normal rounding. VBA rounding uses bankers rounding.
```
https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/round-function

```
With "Bankers Rounding", when numbers are equidistant from the two nearest integers, they are rounded to the nearest even integer; therefore, 0.5 rounds down to 0; 1.5 rounds up to 2.
```
```
So, it looks like you are getting the correct answer because you turned on VBA support so the round function should act like the VBA round function. If you really were writing a macro using Microsoft Office (MSO), you would probably have used Math.Round() (I think that is what it is called) to use the rounding that you were expecting.
```

```
On Saturday, June 04, 2022 22:44 EDT, Steve Edmonds <steve.edmonds@ptglobal.com> wrote:
```I have just put a basic macro together so it will calculate income tax
due from our local tax rates.
I needed to round the tax to the nearest cent so taking the easy route I
added Option VBASupport 1 to use the available VB round function.

I then noticed that the VB round function with say round(12.125,2)
rounds down to 12.12 and the LO inbuilt spread sheet function ROUND with
ROUND(12.125,2) rounds up to 12.13.

Is rounding in this situation arbitrary or is there some some convention
for consistency.

Steve

--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
```
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
```Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
```
```

```
```

--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/