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/
> > Privacy Policy: https://www.documentfoundation.org/privacy


--
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/
Privacy Policy: https://www.documentfoundation.org/privacy