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 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+unsubscr...@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+unsubscr...@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
Re: [libreoffice-users] ROUND function consistency
I just performed a bit of a test.
Cells across.
A1| =RANDBETWEEN(0,10)/100 # simulate dollar and cent amounts
between 0.00 and $1000.00
B1| =A1*0.15 # simulate a tax of 15% on the amounts in A
C1| =ROUND(B1,2) # round the tax amount to 2 places
(whole cents) using the Calc ROUND function
D1| =VBRND(B1,2) # round the tax to 2 places using a
macro function that uses the VBA compatible round function (bankers
rounding)
E1| =INT(B1*100+0.5)/100 # round the tax the old school way.
Function VBRND(c,d)
VBRND = round(c,d)
End Function
I then copied this row down another 999 rows and performed some
summation at the bottom of the 1000 results. I regenerated the random
values in A ten times and recorded the summations below.
(sum RNDBETWEEN) * 0.15 sum (RNDBETWEEN * 0.15) Sum ROUND() ABS error
ROUND()
sum VBRND() ABS error VBRND() sum int(val*100+0.5)/100 ABS Error old
school
75056.829 75056.829 75057.07 0.241908 75056.74 0.089000
75057.07 0.241908
75388.419 75388.419 75388.59 0.1712095 75388.4 0.019000
75388.59 0.1712095
74833.6095 74833.6095 74833.83 0.22049995809 74833.44 0.169500
74833.83 0.22049995809
73028.487 73028.487 73028.76 0.2731048 73028.42 0.067000
73028.76 0.2731048
73042.698 73042.698 73042.95 0.2513131 73042.69 0.008000
73042.95 0.2513131
74173.5075 74173.5075 74173.8 0.29249995925 74173.46 0.047500
74173.8 0.29249995925
75420.3825 75420.3825 75420.64 0.25749992433 75420.4 0.017500
75420.64 0.25749992433
74621.5965 74621.5965 74621.85 0.2535000617 74621.55 0.046500
74621.85 0.2535000617
75481.0995 75481.0995 75481.41 0.31050006868 75481.12 0.020500
75481.41 0.31050006868
73694.1585 73694.1585 73694.39 0.23149994412 73694.03 0.128500
73694.39 0.23149994412
Av. Err. 0.2502697 Av. Err. 0.061300 Av. Err.
0.2502697
Std. Dev. 0.038875Std. Dev. 0.053381Std.
Dev. 0.038875
I found that the average magnitude of the error was least with the
bankers rounding although this ad a little more spread. Bankers rounding
also had less bias.
Steve
On 05/06/2022 21:55, Michael D. Setzer II wrote:
One windows excell page mentions that it uses the
banking rounding method, which I believe is the round to
nearest even. So, using that method 12.125 would round
to 12.12, while 12.135 would round to 12.14..
With this method if next digit is 1,2,3,4 the number
rounds down. If it is 6,7,8,9 it rounds up. If 0 it does
nothing, but if it is 5, it has to check if the key pervious
digit is an even or old number. If it is even, then it rounds
down, if odd it rounds up.
The regular LibreOffice round uses the standard round
function.
Usually, the standard round is used, but know that some
government regulations require the banking/roundeven
or roundodd method.
Was surprized that excell's would default to banking?
Note sure why they don't provide one for each method, or
have an option to set.
On 5 Jun 2022 at 11:37, Johnny Rosenberg wrote:
From: Johnny Rosenberg
Date sent: Sun, 5 Jun 2022 11:37:04 +0200
Subject:Re: [libreoffice-users] ROUND function
consistency
To: LibreOffice Användare
Looks like a bug to me. 12.125 returns 12.12, but 12.1251 returns 12.13.
Both should return the same.
I guess this bug should be reported.
Meanwhile, you can make your own function that you can use. Here are two
different suggestions.
1: This one use the Calc built-in ROUND() cell function. Place it in "My
macros & dialogs". VBA compatibility mode is not needed.
Public Function faRound(x As Double, d As Integer)
Dim Calc
Calc=createUnoService("com.sun.star.sheet.FunctionAccess")
faRound=Calc.callFunction("ROUND", Array(x, d))
End Function
2: This one use the method we learned at school:
Public Function fRound(x As Double, d As Integer)
fRound=Int(10^d*x+.5)/10^d
End Function
Test:
Print afRound(12.125, 2)
Result: 12.13
Print fRound(12.125, 2)
Result: 12.13
Print fRound(12.125, 1)
Result: 12.1
... and so on.
Kind regards
Johnny Rosenberg
Den sön 5 juni 2022 kl 04:46 skrev Steve Edmonds
:
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+unsubscr...@global.libreoffice.org
Problems?
ht
Re: [libreoffice-users] ROUND function consistency
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 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+unsubscr...@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+unsubscr...@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
Re: [libreoffice-users] ROUND function consistency
On 6/5/2022 6:54 AM, Michael H wrote: Are you sure it isn't somewhere else? I.e. the input to the actual round step from the vb and the LO function? LO automatically shortens numbers and rounds them as it does so... so a that shows 12.15 may actually contain 12.1499 or 12.151 This whole discussion reminds me of a problem I had some 30+ years ago when I was the treasurer for my children's preschool. Every week, when I prepared the paychecks, one of them kept coming out one penny short. At the time, I used an old DOS spreadsheet to calculate the paycheck, and I had created a long complex formula to compute the paychecks. At one point in the computation, the computer had to divide 28 by 7. Now every third grader knows that 28 divided by 7 equals 4, but for some reason, my spreadsheet formula gave a result of 3.99, and that answer, when combined with other steps in the computation, resulted in a paycheck one cent short. A computer friend of mine said that my program was being more precise, but in my simplistic brain, I couldn't see how a wrong answer was more precise than a correct answer. Oddly enough, when I switched to a Basic program on my antique Tandy Model 100 laptop, I got the correct answer and all was right with the paychecks. Virgil -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] ROUND function consistency
=https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/round-function
Was the page I found that mentioned it, but other pages
didn't mention the banking rounding? So not sure if excell
would use regular rounding in excell, but uses the
banking rounding if using the VBA? Don't have access to
excell so no way to test, and might even vary depending
on what version of excell and/or windows??
Note
This VBA function returns something commonly
referred to as bankers rounding. So be careful
before using this function. For more predictable
results, use Worksheet Round functions in Excel
VBA.
Note sure why neither Calc or Excell give option to select
which type of rounding to use?
Made a test formula to do it.
=IF(AND(RIGHT(TEXT(A1,##."),2)="50",ISEVEN(INT(A1*100))=1),INT(A1*100)/100,ROUND(A1,2))
That looked at 3 and 4 digits and would round down for 0
to 49 and round up for 51 to 99. 50 would depend on the
original digit to 2nd decimal being even or odd to
determine.
=IF(AND(RIGHT(TEXT(A1,##.000"),1)="5",ISEVEN(INT(A1*100))=1),INT(A1*100)/100,ROUND(A1,2))
Would work with just looking at 3 digit, and would do same for 0 to 4, and 6 to
9, and then vary if 3rd was 5?
Might be another way, but that seemed to work..
On 5 Jun 2022 at 12:49, Johnny Rosenberg wrote:
From: Johnny Rosenberg
Date sent:60;Sun, 5 Jun 2022 12:49:42 +0200
Subject: ;Re: [libreoffice-users] ROUND function
consistency
To: 0;LibreOffice Användare
> Den sön 5 juni 2022 kl 11:55 skrev Michael D. Setzer II <;msetze...@gmail.com
> >:
>
> > One windows excell page mentions that it uses the
> > banking rounding method, which I believe is the round to
> > nearest even. So, using that method 12.125 would round
> > to 12.12, while 12.135 would round to 12.14..> >
>
> Oh, yes. I didn't think of that. Yes, that makes sense, and I remember we
> learned that method in school too. So no bug report necessary, then\u2026
> Unless
> we want the documentation to be clearer on this.>
>
>
> Kind regards
>
> Johnny Rosenberg
>
>
> > With this method if next digit is 1,2,3,4 the number> > rounds down. If it
> > is 6,7,8,9 it rounds up. If 0 it does
> > nothing, but if it is 5, it has to check if the key pervious
> > digit is an even or old number. If it is even, then it rounds
> > down, if odd it rounds up.
> >
> > The regular LibreOffice round uses the standard round
> > function.
> >
> > Usually, the standard round is used, but know that some
> > government regulations require the banking/roundeven> > or roundodd method.
> >
> > Was surprized that excell's would default to banking?
> > Note sure why they don't provide one for each method, or
> > have an option to set.
> >
> >
> >
> > On 5 Jun 2022 at 11:37, Johnny Rosenberg wrote:
> >
> > From: Johnny Rosenberg
> >
> > Date sent: Sun, 5 Jun 2022 11:37:04 +0200> > Subject:
> > 60; Re: [libreoffice-users] ROUND function
> > consistency
> > To: LibreOffice Användare
> >
> >
> > > Looks like a bug to me. 12.125 returns 12.12, but 12.1251 returns 12.13.
> > > Both should return the same.
> > > I guess this bug should be reported.> > > Meanwhile, you can make your
> > > own function that you can use. Here are two
> > > different suggestions.
> > >
> > > 1: This one use the Calc built-in ROUND() cell function. Place it in "My
> > > macros & dialogs". VBA compatibility mode is not needed.
> > >
> > > Public Function faRound(x As Double, d As Integer)
> > > Dim Calc
> > > Calc=createUnoService("com.sun.star.sheet.FunctionAccess")
> > > faRound=Calc.callFunction("ROUND", Array(x, d))
> > > End Function
> > >
> > > 2: This one use the method we learned at school:
> > > Public Function fRound(x As Double, d As Integer)
> > > fRound=Int(10^d*x+.5)/10^d
> > > End Function
> > >
> > > Test:
> > > Print afRound(12.125, 2)
> > > Result: 12.13
> > >
> > > Print fRound(12.125, 2)
> > > Result: 12.13
> > >
> > > Print fRound(12.125, 1)
> > > Result: 12.1
> > >
> > > ... and so on.
> > >
> > > Kind regards
> > >
> > > Johnny Rosenberg
> > >
> > > Den sön 5 juni 2022 kl 04:46 skrev Steve Edmonds <
> > steve.edmo...@ptglobal.com
> > > >:
> > >
> > > > I have just put a basic macro toge
Re: [libreoffice-users] ROUND function consistency
Are you sure it isn't somewhere else? I.e. the input to the actual round step from the vb and the LO function? LO automatically shortens numbers and rounds them as it does so... so a that shows 12.15 may actually contain 12.1499 or 12.151 On Sat, Jun 4, 2022 at 9:45 PM Steve Edmonds 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+unsubscr...@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+unsubscr...@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
Re: [libreoffice-users] ROUND function consistency
Den sön 5 juni 2022 kl 11:55 skrev Michael D. Setzer II :
> One windows excell page mentions that it uses the
> banking rounding method, which I believe is the round to
> nearest even. So, using that method 12.125 would round
> to 12.12, while 12.135 would round to 12.14..
>
Oh, yes. I didn't think of that. Yes, that makes sense, and I remember we
learned that method in school too. So no bug report necessary, then… Unless
we want the documentation to be clearer on this.
Kind regards
Johnny Rosenberg
> With this method if next digit is 1,2,3,4 the number
> rounds down. If it is 6,7,8,9 it rounds up. If 0 it does
> nothing, but if it is 5, it has to check if the key pervious
> digit is an even or old number. If it is even, then it rounds
> down, if odd it rounds up.
>
> The regular LibreOffice round uses the standard round
> function.
>
> Usually, the standard round is used, but know that some
> government regulations require the banking/roundeven
> or roundodd method.
>
> Was surprized that excell's would default to banking?
> Note sure why they don't provide one for each method, or
> have an option to set.
>
>
>
> On 5 Jun 2022 at 11:37, Johnny Rosenberg wrote:
>
> From: Johnny Rosenberg
>
> Date sent: Sun, 5 Jun 2022 11:37:04 +0200
> Subject:Re: [libreoffice-users] ROUND function
> consistency
> To: LibreOffice Användare
>
>
> > Looks like a bug to me. 12.125 returns 12.12, but 12.1251 returns 12.13.
> > Both should return the same.
> > I guess this bug should be reported.
> > Meanwhile, you can make your own function that you can use. Here are two
> > different suggestions.
> >
> > 1: This one use the Calc built-in ROUND() cell function. Place it in "My
> > macros & dialogs". VBA compatibility mode is not needed.
> >
> > Public Function faRound(x As Double, d As Integer)
> > Dim Calc
> > Calc=createUnoService("com.sun.star.sheet.FunctionAccess")
> > faRound=Calc.callFunction("ROUND", Array(x, d))
> > End Function
> >
> > 2: This one use the method we learned at school:
> > Public Function fRound(x As Double, d As Integer)
> > fRound=Int(10^d*x+.5)/10^d
> > End Function
> >
> > Test:
> > Print afRound(12.125, 2)
> > Result: 12.13
> >
> > Print fRound(12.125, 2)
> > Result: 12.13
> >
> > Print fRound(12.125, 1)
> > Result: 12.1
> >
> > ... and so on.
> >
> > Kind regards
> >
> > Johnny Rosenberg
> >
> > Den sön 5 juni 2022 kl 04:46 skrev Steve Edmonds <
> steve.edmo...@ptglobal.com
> > >:
> >
> > > 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+unsubscr...@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+unsubscr...@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
>
>
> ++
> Michael D. Setzer II - Computer Science Instructor
> (Retired)
> mailto:mi...@guam.net
> mailto:msetze...@gmail.com
> Guam - Where America's Day Begins
> G4L Disk Imaging Project maintainer
> http://sourceforge.net/projects/g4l/
> ++
>
>
>
>
--
To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] ROUND function consistency
One windows excell page mentions that it uses the
banking rounding method, which I believe is the round to
nearest even. So, using that method 12.125 would round
to 12.12, while 12.135 would round to 12.14..
With this method if next digit is 1,2,3,4 the number
rounds down. If it is 6,7,8,9 it rounds up. If 0 it does
nothing, but if it is 5, it has to check if the key pervious
digit is an even or old number. If it is even, then it rounds
down, if odd it rounds up.
The regular LibreOffice round uses the standard round
function.
Usually, the standard round is used, but know that some
government regulations require the banking/roundeven
or roundodd method.
Was surprized that excell's would default to banking?
Note sure why they don't provide one for each method, or
have an option to set.
On 5 Jun 2022 at 11:37, Johnny Rosenberg wrote:
From: Johnny Rosenberg
Date sent: Sun, 5 Jun 2022 11:37:04 +0200
Subject:Re: [libreoffice-users] ROUND function
consistency
To: LibreOffice Användare
> Looks like a bug to me. 12.125 returns 12.12, but 12.1251 returns 12.13.
> Both should return the same.
> I guess this bug should be reported.
> Meanwhile, you can make your own function that you can use. Here are two
> different suggestions.
>
> 1: This one use the Calc built-in ROUND() cell function. Place it in "My
> macros & dialogs". VBA compatibility mode is not needed.
>
> Public Function faRound(x As Double, d As Integer)
> Dim Calc
> Calc=createUnoService("com.sun.star.sheet.FunctionAccess")
> faRound=Calc.callFunction("ROUND", Array(x, d))
> End Function
>
> 2: This one use the method we learned at school:
> Public Function fRound(x As Double, d As Integer)
> fRound=Int(10^d*x+.5)/10^d
> End Function
>
> Test:
> Print afRound(12.125, 2)
> Result: 12.13
>
> Print fRound(12.125, 2)
> Result: 12.13
>
> Print fRound(12.125, 1)
> Result: 12.1
>
> ... and so on.
>
> Kind regards
>
> Johnny Rosenberg
>
> Den sön 5 juni 2022 kl 04:46 skrev Steve Edmonds >:
>
> > 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+unsubscr...@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+unsubscr...@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
++
Michael D. Setzer II - Computer Science Instructor
(Retired)
mailto:mi...@guam.net
mailto:msetze...@gmail.com
Guam - Where America's Day Begins
G4L Disk Imaging Project maintainer
http://sourceforge.net/projects/g4l/
++
--
To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] ROUND function consistency
I just found that you can do this with the already existing Format()
function. No need for making your own function and no need for using VBA
compatibility:
Print Format(12.125, "0.00")
The second parameter is what you would have written in the ”Format code”
field when formatting a cell in Calc.
Result: 12.13
Kind regards
Johnny Rosenberg
Den sön 5 juni 2022 kl 11:37 skrev Johnny Rosenberg :
> Looks like a bug to me. 12.125 returns 12.12, but 12.1251 returns 12.13.
> Both should return the same.
> I guess this bug should be reported.
> Meanwhile, you can make your own function that you can use. Here are two
> different suggestions.
>
> 1: This one use the Calc built-in ROUND() cell function. Place it in ”My
> macros & dialogs”. VBA compatibility mode is not needed.
>
> Public Function faRound(x As Double, d As Integer)
> Dim Calc
> Calc=createUnoService("com.sun.star.sheet.FunctionAccess")
> faRound=Calc.callFunction("ROUND", Array(x, d))
> End Function
>
> 2: This one use the method we learned at school:
> Public Function fRound(x As Double, d As Integer)
> fRound=Int(10^d*x+.5)/10^d
> End Function
>
> Test:
> Print afRound(12.125, 2)
> Result: 12.13
>
> Print fRound(12.125, 2)
> Result: 12.13
>
> Print fRound(12.125, 1)
> Result: 12.1
>
> … and so on.
>
> Kind regards
>
> Johnny Rosenberg
>
> Den sön 5 juni 2022 kl 04:46 skrev Steve Edmonds <
> steve.edmo...@ptglobal.com>:
>
>> 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+unsubscr...@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+unsubscr...@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
Re: [libreoffice-users] ROUND function consistency
Looks like a bug to me. 12.125 returns 12.12, but 12.1251 returns 12.13.
Both should return the same.
I guess this bug should be reported.
Meanwhile, you can make your own function that you can use. Here are two
different suggestions.
1: This one use the Calc built-in ROUND() cell function. Place it in ”My
macros & dialogs”. VBA compatibility mode is not needed.
Public Function faRound(x As Double, d As Integer)
Dim Calc
Calc=createUnoService("com.sun.star.sheet.FunctionAccess")
faRound=Calc.callFunction("ROUND", Array(x, d))
End Function
2: This one use the method we learned at school:
Public Function fRound(x As Double, d As Integer)
fRound=Int(10^d*x+.5)/10^d
End Function
Test:
Print afRound(12.125, 2)
Result: 12.13
Print fRound(12.125, 2)
Result: 12.13
Print fRound(12.125, 1)
Result: 12.1
… and so on.
Kind regards
Johnny Rosenberg
Den sön 5 juni 2022 kl 04:46 skrev Steve Edmonds :
> 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+unsubscr...@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+unsubscr...@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
[libreoffice-users] ROUND function consistency
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+unsubscr...@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
