Re: [libreoffice-users] Re: Visible currency rounding
At 22:30 19/07/2013 -0400, Mark LaPierre wrote: On 07/19/2013 07:26 PM, Todor Takov wrote: I needed to implement the so called Bankers' Rounding Function, which would round with respect to the 4/5 rule. [...] Just one point I would like to make. If you are working with money be sure that the total of the rounded amounts adds up to match the original amount. Example: $5.39 divided among several accounts. 10.5% to Acct A = 0.56595 = $0.57 13.5% to Acct B = 0.72765 = $0.73 17% to Acct C = 0.9163 = $0.92 59% to Acct D = 3.1801 = $3.18 = Total = $5.40 Any summing error needs to be accounted for if you ever want your accounts to balance. I'm not sure you appreciated the significance of the questioner's Bankers' Rounding Function. You've given an excellent example of how this works. Using banker's rounding instead of primary school mathematics teachers's rounding on your figures, your first dividend, 0.56595, would round to 0.56, not 0.57 (with no change to the other figures), and the total of the rounded values would be the required $5.39. Brian Barker -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: Visible currency rounding
At 08:54 20/07/2013 -0700, Todor Takov wrote: I have given my reasons in an explanation to a post by Luuk in this present discussion. But I think this is not an overkill, because if we have a significantly long number and we just look at the 3 digits to the right of the comma separator and just drop the rest of the digits to the right of them - we shall be risking to get an incorrect result. The correct way to round the number will be to start at the rightmost available digit and drop one digit at a time, until we reach the desired digit position. At least this is correct in my opinion. Paradoxically, this is certainly not the way that rounding - even banker's rounding - is normally achieved. You would round 1.23456 to 1.2346 and then to 1.235 and 1.24 (using either scheme). Single rounding (again using either scheme) gives 1.23. This makes sense, since the original number is clearly closer to 1.23 than to 1.24. It is a general principle that you should always round numbers once; if you do so repeatedly, you will indeed get different results. http://en.wikipedia.org/wiki/Rounding#Double_rounding says: In _Martinez v. Allstate_ and _Sendejo v. Farmers_, litigated between 1995 and 1997, the insurance companies argued that double rounding premiums was permissible and in fact required. The US courts ruled against the insurance companies and ordered them to adopt rules to ensure single rounding. Some computer languages and the IEEE 754-2008 standard dictate that in straightforward calculations the result should not be rounded twice. This has been a particular problem with Java as it is designed to be run identically on different machines, special programming tricks have had to be used to achieve this with x87 floating point.[...] Brian Barker -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: Visible currency rounding
At 16:26 19/07/2013 -0700, Todor Takov wrote: I needed to implement the so called Bankers' Rounding Function, which would round with respect to the 4/5 rule. And finally I ended doing it myself in a short Function in LO Basic. I tried my best, although math and programming are not my strong sides. So here it is, together with a Subroutine called rounded_test and the very Function is called Rounded. [Over 120 lines of code snipped] You can call it for each line (item) in an invoice. It will take the number you throw at it and will return it rounded to the second digit after the comma or point separator. Then when you sum the invoice up - the numbers will always be consistent and correctly rounded. Isn't this somewhat overkill? If you need just to round to the second fractional place - dollars and cents, pounds and pence, whatever - the only exception is the case where the third fractional place is 5 and the second fractional place is even. In that case you need ROUNDDOWN(); in all other cases, ROUND() will do the job: =IF(AND(INT(MOD(ABS(Xn)*1000;10))=5;ISEVEN(Xn*100));ROUNDDOWN(Xn;2);ROUND(Xn;2)) (And yes: this works for negative values, too.) Brian Barker -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: Visible currency rounding
Hi :) It's really good to see more people sharing code on this list. Looks like a useful function to have. Sometimes it is more useful to have a working bit of code that is a mess rather than something elegant that doesn't work. It follows the philosophy of Release early and release often. if it's working then people use it and maybe look at it and maybe edit it to improve it. If it's beautiful code but doesn't work and doesn't get used then no-one sees it and it may never get completed. So it's great to have something working. Also i can't see anything wrong with the code anyway! If anyone can improve it or stream-line it then please post the code back to this mailing list, or at least to toodr. I noticed a couple of things in the Rem statements, Echos and commented out bits (that's the ' s, right?), ie none of which affects the running of the program and most never even gets seen by users. Digits only needs 1 g. Accepsts is a tpyo of Accepts. Exessive a tpyo of excessive. If Rems and stuff are important i'm sure the Docs Team wouldn't mind trying to de-geekify them or maybe people here might but i think it really doesn't make much difference. Just out of curiosity would it be tough to translate this sort of thing into Python and make it into an Extension? Perhaps joined with other code that has been on the mailing list here? @ Toodr: Would you mind giving permission to license your code under the copyleft licensing used by the rest of the code? So people could share and modify? Looks like you said it would be fine, informally. GPL? LGPL? Thanks and regards from Tom :) From: toodr todorta...@gmail.com To: users@global.libreoffice.org Sent: Saturday, 20 July 2013, 0:26 Subject: [libreoffice-users] Re: Visible currency rounding I had the same problem. I needed to implement the so called Bankers' Rounding Function, which would round with respect to the 4/5 rule. And finally I ended doing it myself in a short Function in LO Basic. I tried my best, although math and programming are not my strong sides. So here it is, together with a Subroutine called rounded_test and the very Function is called Rounded. Sub rounded_test N=20.45454545454545454545 N= Rounded(N) N=0 End Sub REM Banker's Rounding Function REM Accepsts parameter as Double with or without a sign +/- REM Returns Double with or without a sign +/-,rounded to the second diggit after the comma separator Function Rounded (NumD As Double) Dim NumI as Integer Dim position as Integer position=0 Dim overflow as Integer overflow=0 Dim NumS as String REM initilize 2 arrays (integer and string) and we save the number into them NumS=Format( NumD, 0.) len01=LEN(NumS) Dim Stringarray(len01-1) as String Dim Integerarray(len01-1) as Integer For i=1 to len01 string0=Mid(NumS,i,1) If string0=. Or string0=, Then Stringarray(i-1)=. Integerarray(i-1)=0 position=i Else Stringarray(i-1)=string0 Integerarray(i-1)=Val(string0) End if next i string0= 'Emptying the variable which will be used to return the number - string REM Rounding If position=0 Then 'an integer without a fraction part - return the number as it is! Rounded=NumD Exit Function Else End If If len01-position2 Then 'will be rounding For j=1 to len01-position-2 '(len01-position-2) number of diggits till the end of the number string which will be dropped out If Integerarray(len01-j)5 Or overflow=1 Or (Integerarray(len01-j)=5 And (Stringarray(len01-j-1)=1 Or Stringarray(len01-j-1)=3 Or Stringarray(len01-j-1)=5 Or Stringarray(len01-j-1)=7 Or Stringarray(len01-j-1)=9)) Then overflow=1 If Integerarray(len01-j-1)+overflow=9 Then Integerarray(len01-j-1)=Integerarray(len01-j-1)+overflow overflow=0 Else Integerarray(len01-j-1)=0 overflow=1 End if Integerarray(len01-j)=0 Stringarray(len01-j)=0 Stringarray(len01-j-1)=CStr(Integerarray(len01-j-1)) Else Integerarray(len01-j)=0 Stringarray(len01-j)=0 End If Next j 'If we have some left over, remaining after the removal of the exessive diggits, we shall distribute it 'among the remaining integer and fractional part If overflow=1 Then For k=position to 0 step -1 If k=position-1 Then goto Lbl 'skip this if it is a comma or point separator If k=0 And (Stringarray(k)=- Or Stringarray(k)=+) Then goto Lbl 'skip this if it is a +/- sign If Integerarray(k)+overflow=9 Then Integerarray(k)=Integerarray(k)+overflow overflow=0 Else Integerarray(k)=0 overflow=1 End if Stringarray(k)=CStr(Integerarray(k)) Lbl: next k Else End If 'Check if we have a +/- sign in front If
Re: [libreoffice-users] Re: Visible currency rounding
On 07/19/2013 07:26 PM, toodr wrote: I had the same problem. I needed to implement the so called Bankers' Rounding Function, which would round with respect to the 4/5 rule. And finally I ended doing it myself in a short Function in LO Basic. I tried my best, although math and programming are not my strong sides. So here it is, together with a Subroutine called rounded_test and the very Function is called Rounded. A whole bunch of code stripped out here You can call it for each line (item) in an invoice. It will take the number you through at it and will return it rounded to the second diggit after the comma or point separator. Then when you sum the invoice up - the numbers will always be consistent and correctly rounded. This is not the best of programming though, but it works. I hope it will help someone. Feel free to modify and use it as you please. Cheers, toodr Just one point I would like to make. If you are working with money be sure that the total of the rounded amounts adds up to match the original amount. Example: $5.39 divided among several accounts. 10.5% to Acct A = 0.56595 = $0.57 13.5% to Acct B = 0.72765 = $0.73 17% to Acct C = 0.9163 = $0.92 59% to Acct D = 3.1801 = $3.18 = Total = $5.40 Any summing error needs to be accounted for if you ever want your accounts to balance. -- _ °v° /(_)\ ^ ^ Mark LaPierre Registered Linux user No #267004 https://linuxcounter.net/ -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: Visible currency rounding
HI! You are right. I think the rules is simular in Sweden. I think this tradition to have the rounded sum on the invoice is to make it easier during accounting. But this is not the motivation to make this to complicated. Many invoice in Sweden today has no visible currency rounding. So, I think I will change this to just delete all decimals of all sums in the bottom of the invoice. Thanks for all help! Regards, Marino -Ursprungligt meddelande- From: Tom Davies Sent: Tuesday, July 16, 2013 2:21 AM To: Tinkerer ; users@global.libreoffice.org Subject: Re: [libreoffice-users] Re: Visible currency rounding Hi :) Ahhh, that makes sense. HMRC are the Vat collectors for the Uk but the Swedish one is likely to have similar rules. Regards from Tom :) From: Tinkerer j_taylo...@btinternet.com To: users@global.libreoffice.org Sent: Monday, 15 July 2013, 19:38 Subject: [libreoffice-users] Re: Visible currency rounding Tom Let me put it another way. Take the following sales: 50 items @ 1.37 VAT @20% = 68.50 VAT = 13.70 If the same items are sold separately, the VAT would be different. 1 item @ 1.37 VAT @ 20% = 0.274 for the VAT This would be rounded to 27p and 50 x 0.27 = 13.50 Rounding has caused a difference of 20p When paying tax, you are required to pay what you have collected. If you calculate as you suggest you may hand over more tax than you have collected. What I think he is looking for is a simple way of calculating the VAT due on his total with each individual item rounded down and that cannot be done. Each item has to be calculated, rounded down and then a total obtained for the rounded figures. HMRC take the view that VAT is payable on the total value of cash sales plus the VAT calculated on Invoiced sales. In my examples the VAT due would be 27.20 assuming that the 100 items sold would consist of 50 invoiced and 50 cash sales. Tink. -- View this message in context: http://nabble.documentfoundation.org/Visible-currency-rounding-tp4065342p4065571.html Sent from the Users mailing list archive at Nabble.com. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: Visible currency rounding
Hi :) Is it the AVKORTA that chops off the end of the decimals? English is really a few different languages added together so to be posh we use truncate for this sort of thing. It means the same as chop off but Maths geniuses(? genii?) seem to prefer the posher word. Regards from Tom :) From: Marino / WellnessWebshop.se mar...@wellnesswebshop.se To: Tom Davies tomdavie...@yahoo.co.uk; Tinkerer j_taylo...@btinternet.com; users@global.libreoffice.org Sent: Tuesday, 16 July 2013, 9:12 Subject: Re: [libreoffice-users] Re: Visible currency rounding HI! You are right. I think the rules is similar in Sweden. I think this tradition to have the rounded sum on the invoice is to make it easier during accounting. But this is not the motivation to make this to complicated. Many invoice in Sweden today has no visible currency rounding. So, I think I will change this to just delete all decimals of all sums in the bottom of the invoice. Thanks for all help! Regards, Marino -Ursprungligt meddelande- From: Tom Davies Sent: Tuesday, July 16, 2013 2:21 AM To: Tinkerer ; users@global.libreoffice.org Subject: Re: [libreoffice-users] Re: Visible currency rounding Hi :) Ahhh, that makes sense. HMRC are the Vat collectors for the Uk but the Swedish one is likely to have similar rules. Regards from Tom :) From: Tinkerer j_taylo...@btinternet.com To: users@global.libreoffice.org Sent: Monday, 15 July 2013, 19:38 Subject: [libreoffice-users] Re: Visible currency rounding Tom Let me put it another way. Take the following sales: 50 items @ 1.37 VAT @20% = 68.50 VAT = 13.70 If the same items are sold separately, the VAT would be different. 1 item @ 1.37 VAT @ 20% = 0.274 for the VAT This would be rounded to 27p and 50 x 0.27 = 13.50 Rounding has caused a difference of 20p When paying tax, you are required to pay what you have collected. If you calculate as you suggest you may hand over more tax than you have collected. What I think he is looking for is a simple way of calculating the VAT due on his total with each individual item rounded down and that cannot be done. Each item has to be calculated, rounded down and then a total obtained for the rounded figures. HMRC take the view that VAT is payable on the total value of cash sales plus the VAT calculated on Invoiced sales. In my examples the VAT due would be 27.20 assuming that the 100 items sold would consist of 50 invoiced and 50 cash sales. Tink. -- View this message in context: http://nabble.documentfoundation.org/Visible-currency-rounding-tp4065342p4065571.html Sent from the Users mailing list archive at Nabble.com. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: Visible currency rounding
HI! AVKORTA is the commando that takes the sum to nearast even sum down = .1-4 will be “AVKORTA” down to 0.0. When “HELTAL” will rounding up .5-9. Regards, Marino From: Tom Davies Sent: Tuesday, July 16, 2013 3:03 PM To: Marino / WellnessWebshop.se ; Tinkerer ; users@global.libreoffice.org Subject: Re: [libreoffice-users] Re: Visible currency rounding Hi :) Is it the AVKORTA that chops off the end of the decimals? English is really a few different languages added together so to be posh we use truncate for this sort of thing. It means the same as chop off but Maths geniuses(? genii?) seem to prefer the posher word. Regards from Tom :) -- From: Marino / WellnessWebshop.se mar...@wellnesswebshop.se To: Tom Davies tomdavie...@yahoo.co.uk; Tinkerer j_taylo...@btinternet.com; users@global.libreoffice.org Sent: Tuesday, 16 July 2013, 9:12 Subject: Re: [libreoffice-users] Re: Visible currency rounding HI! You are right. I think the rules is similar in Sweden. I think this tradition to have the rounded sum on the invoice is to make it easier during accounting. But this is not the motivation to make this to complicated. Many invoice in Sweden today has no visible currency rounding. So, I think I will change this to just delete all decimals of all sums in the bottom of the invoice. Thanks for all help! Regards, Marino -Ursprungligt meddelande- From: Tom Davies Sent: Tuesday, July 16, 2013 2:21 AM To: Tinkerer ; users@global.libreoffice.org Subject: Re: [libreoffice-users] Re: Visible currency rounding Hi :) Ahhh, that makes sense. HMRC are the Vat collectors for the Uk but the Swedish one is likely to have similar rules. Regards from Tom :) From: Tinkerer j_taylo...@btinternet.com To: users@global.libreoffice.org Sent: Monday, 15 July 2013, 19:38 Subject: [libreoffice-users] Re: Visible currency rounding Tom Let me put it another way. Take the following sales: 50 items @ 1.37 VAT @20% = 68.50 VAT = 13.70 If the same items are sold separately, the VAT would be different. 1 item @ 1.37 VAT @ 20% = 0.274 for the VAT This would be rounded to 27p and 50 x 0.27 = 13.50 Rounding has caused a difference of 20p When paying tax, you are required to pay what you have collected. If you calculate as you suggest you may hand over more tax than you have collected. What I think he is looking for is a simple way of calculating the VAT due on his total with each individual item rounded down and that cannot be done. Each item has to be calculated, rounded down and then a total obtained for the rounded figures. HMRC take the view that VAT is payable on the total value of cash sales plus the VAT calculated on Invoiced sales. In my examples the VAT due would be 27.20 assuming that the 100 items sold would consist of 50 invoiced and 50 cash sales. Tink. -- View this message in context: http://nabble.documentfoundation.org/Visible-currency-rounding-tp4065342p4065571.html Sent from the Users mailing list archive at Nabble.com. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: Visible currency rounding
Hi :) Ahhh, that makes sense. HMRC are the Vat collectors for the Uk but the Swedish one is likely to have similar rules. Regards from Tom :) From: Tinkerer j_taylo...@btinternet.com To: users@global.libreoffice.org Sent: Monday, 15 July 2013, 19:38 Subject: [libreoffice-users] Re: Visible currency rounding Tom Let me put it another way. Take the following sales: 50 items @ 1.37 VAT @20% = 68.50 VAT = 13.70 If the same items are sold separately, the VAT would be different. 1 item @ 1.37 VAT @ 20% = 0.274 for the VAT This would be rounded to 27p and 50 x 0.27 = 13.50 Rounding has caused a difference of 20p When paying tax, you are required to pay what you have collected. If you calculate as you suggest you may hand over more tax than you have collected. What I think he is looking for is a simple way of calculating the VAT due on his total with each individual item rounded down and that cannot be done. Each item has to be calculated, rounded down and then a total obtained for the rounded figures. HMRC take the view that VAT is payable on the total value of cash sales plus the VAT calculated on Invoiced sales. In my examples the VAT due would be 27.20 assuming that the 100 items sold would consist of 50 invoiced and 50 cash sales. Tink. -- View this message in context: http://nabble.documentfoundation.org/Visible-currency-rounding-tp4065342p4065571.html Sent from the Users mailing list archive at Nabble.com. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
