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?
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 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? > > > >
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