[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-position>2 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 Stringarray(0)="-" Or Stringarray(0)="+" Then string0=Stringarray(0) 'If we have still some overflow remaining, If overflow=1 Then 'We add 1 in front but, after the sign string0=string0 & "1" 'Construct the remainder of the number For i=2 to len01 string0=string0 & Stringarray(i-1) next i Else 'Without a sign in front 'Construct the remainder of the number For i=2 to len01 string0=string0 & Stringarray(i-1) next i End If Else 'If we have still some overflow remaining, If overflow=1 Then 'We add 1 in front string0=string0 & "1" 'Construct the remainder of the number For i=1 to len01 string0=string0 & Stringarray(i-1) next i Else 'Without a sign in front 'Construct the remainder of the number For i=1 to len01 string0=string0 & Stringarray(i-1) next i End If End If string0=Format( Val(string0), "0.###
[libreoffice-users] Re: Visible currency rounding
Thank you for the reference in your answer. That is indeed the case and I must admit that my logic is wrong. I will have to rethink all this stuff. You are certainly right to point out that the original number is closer to the result as you have suggested. My fault ! And apologies to the other participants in this discussion. Regards, toodr -- View this message in context: http://nabble.documentfoundation.org/Visible-currency-rounding-tp4065342p4066516.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
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
[libreoffice-users] Re: Visible currency rounding
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, untill we reach the desired digit position. At least this is correct in my oppinion. Regards, toodr -- View this message in context: http://nabble.documentfoundation.org/Visible-currency-rounding-tp4065342p4066388.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
[libreoffice-users] Re: Visible currency rounding
You described it in the most precise way. I needed a function that would round down the number if the followin digit is less then 5, and would round up if the followin digit is greater then 5, and if the followin digit is equal to 5 then it will test if the last digit to remain in the number is odd or even and then round it up if it is odd or leave as it is if it is even. I think this is called "Round half to even" as a "tie braking rule". But even more accurate description is here: http://www.xbeat.net/vbspeed/i_BankersRounding.htm I must confess I did not have an idea that this was called "Gaussian rounding". In my case the function needs to start from the least available right standing digit for the given number and round consecutively from there to the left untill the desired digit position is reached, one digit at a time . This way it ensures that any boundary cases be treated correctly. The practical need for this is when I had to fill in an invoice, on each row I wold have an intermediate number which is a result of multiplication of amount by unit price (i.e. an item's sum), which in the general case will not be an exact number . Then I would need to round this number to a sensible (currency) format and only then to add the numbers up in order to get the final amount for the invoice. In this way all numbers add up correctly and there is no difference in the final amount. As for the example where 23,574 rounds to 23,57 , this is the intended result as described above. Thanks again for the clarification you've given in your post. Regards, toodr -- View this message in context: http://nabble.documentfoundation.org/Visible-currency-rounding-tp4065342p4066383.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
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
[libreoffice-users] Re: Visible currency rounding
On 20-07-2013 01:26, toodr wrote: I had the same problem. I needed to implement the so called Bankers' Rounding Function, if i read correctly at: http://en.wikipedia.org/wiki/Rounding Round half to even[edit] A tie-breaking rule that is less biased is round half to even, namely: If the fraction of y is 0.5, then q is the even integer nearest to y. Thus, for example, +23.5 becomes +24, as does +24.5; while −23.5 becomes −24, as does −24.5. This method treats positive and negative values symmetrically, and is therefore free of sign bias. More importantly, for reasonable distributions of y values, the expected (average) value of the rounded numbers is the same as that of the original numbers. However, this rule will introduce a towards-zero bias for even numbers (including zero), and a towards-infinity bias for odd ones. This variant of the round-to-nearest method is also called unbiased rounding, convergent rounding, statistician's rounding, Dutch rounding, Gaussian rounding, odd-even rounding,[3] bankers' rounding or broken rounding, and is widely used in bookkeeping. This is the default rounding mode used in IEEE 754 computing functions and operators. This means that your function does not work correctly i.e. 23,574 should be rounded to 23,58, you function gives 23,57 so, i tried to create a function hwich lives up to my expectations... ;) (but i NEVER used 'Bankers' Rounding'..) function bround(i as double, Optional d as integer) dim format as string dim tmp as string dim c as double svc=createUnoService("com.sun.star.sheet.FunctionAccess") if IsMissing(d) then d=2 bround = svc.callFunction("ROUND", array(i/2, d))*2 end function -- 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
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 :) 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 >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-position>2 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 >
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
[libreoffice-users] Re: Visible currency rounding
Tom In my early days of using spreadsheets, to be absolutely certain we would Round the decimal by multiplying the result by 100, then INT and then divide by 100. Tink. -- View this message in context: http://nabble.documentfoundation.org/Visible-currency-rounding-tp4065342p4065731.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
[libreoffice-users] Re: Visible currency rounding
On 14-07-2013 09:11, Marino / WellnessWebshop.se wrote: Hi! I want to have visible currency rounding on an invoice template that I have made i Libre Office. I have this formula in the cell for currency rounding: =OM(HELTAL(K50+K51)-(K50+K51)>-0,5;AVKORTA(HELTAL(K50+K51)-(K50+K51);3);AVKORTA(HELTAL((K50+K51)+1)-(K50+K51);2)) http://goo.gl/CM2Sf = IF (INT (K50 + K51) - (K50 + K51)> -0.5; TRUNC (INT (K50 + K51) - (K50 + K51), 3); TRUNC (INT ((K50 + K51) +1 ) - (K50 + K51), 2)) I only had to add some space to the original function (before and after OM, HELTAL and AVKORTA) -- 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 To: Tom Davies ; Tinkerer ; 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 >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 >To: Tom Davies ; Tinkerer >; 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 >>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! 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 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 >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
[libreoffice-users] Re: Visible currency rounding
Tom The post said he wanted to round everything including the tax on his invoice template. As I said earlier. He needs to go into Tools >Options >Calc >Calculate and check the "Precision as shown" box. This will make all his calculations precise as displayed. ie if he sets to two decimal places then Calc will concatenate the result to two places. There is no need for fancy formulas. Tink. -- View this message in context: http://nabble.documentfoundation.org/Visible-currency-rounding-tp4065342p4065572.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
[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
[libreoffice-users] Re: Visible currency rounding
Marino Format >Cells >Numbers nil decimal places. To get a correct roundup go to Tools >Options >Calc >Calculate, make sure that "Precision as shown" checked. Tink. -- View this message in context: http://nabble.documentfoundation.org/Visible-currency-rounding-tp4065342p4065363.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
[libreoffice-users] Re: Visible currency rounding
I do not understand your formula, but you can set the result to a number of decimal places and I assume that is what you have done. Calc displays the number of places you set, but this is not the exact result, eg it may display two decimal places, but in fact is carrying forward three decimal places. To correct this go to Preferences (Mac) or Tools>Options(Windows) Set the Calc > Calculate > to Precision as shown. The result will then be as shown in the cell. BTW. The UK VAT calculations allow you to round down to the nearest penny and this should equal the amount paid to the HMRC. Tink. -- View this message in context: http://nabble.documentfoundation.org/Visible-currency-rounding-tp4065342p4065358.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