Visible currency rounding

[Marino_WellnessWebsh]

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

But something is wrong, sometimes there is a diff of 0,01 in the sum. Perhaps there is a better formula for this?

My invoice looks like this, on the bottom rows:
- Sum for all products on invoice
- Tax
- Currency rounding
- Sum to pay

Best regards,
Marino

[Tom_Davies]

Hi
Is AVKORTA = Truncate, ie just chopping the last digits off?

What does HELTAL do?  Is it some sort of "Average" or a division such as x/3 or x/2?

Usually computer calculations work well if you avoid doing any rounding-off until the final figure.  Even then avoid rounding off.  Just use formatting to limit the number of figures displayed.  The problem with that is if you are doing "VAT Returns" or something else where
you just truncate the number rather than rounding properly.

In proper "rounding" the number is rounded to the nearest figure.  So ,005 or ,006 or above goes up to ,01.  While ,006 is obviously closer to ,01 than it is to ,00 it is not so obvious with ,005.  With ,006 we see that
,01 - ,006 = ,004
but with ,005
,01 - ,005 =,005
I think it's an example of a potential "fence-post error"
,000  ,001  ,002  ,003  ,004  all round down to ,00 but
,005  ,006  ,007  ,008  ,009  should all round up to ,01

So, i suspect that in some of those calculations.  For example;
AVKORTA((HELTAL(K50+K51)-(K50+K51);3) = ,0066  which is getting truncated to  ,00 instead of the more correct rounding to  ,01 
In 1/3 calculations it is wrong.  When the result happens to be ,0000 or ,0033 the truncated answer is the right answer.  However that rounding might sometimes be corrected by a problem in the next equation if
AVKORTA(HELTAL((K50+K51)+1)-(K50+K51);2) = ,005  then that too is getting truncated to ,00 instead of being properly rounded to ,01
So this 2nd equation might be mitigating against the problem of the 1st equation.  Two wrongs do sometimes make a right = quite the contrary of the 'wisdom' of the common phrase.

Unfortunately i don't really know what those equations are doing because they are in Swedish (i think).  So i don't really know what AVKORTA, HELTAL or OM are doing.  However, a ,01 inaccuracy appearing intermittently could be explained by my above assumptions.

If i am right then maybe this equation might be better?

=OM(HELTAL(K50+K51)-(K50+K51)>-0,5;AVKORTA((HELTAL(K50+K51)-(K50+K51);3);(HELTAL((K50+K51)+1)-(K50+K51);2)))

ie, just move the truncate command outside of the calculation? 
Regards from
Tom

[Tinkerer]

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.

[Marino_WellnessWebsh]

Hi Tom!

Thanks for your answer. I have tried to chance to your example, but it don´t work. A small explanation of the Swedish words (you are right; this is Swedish ), maybe this will make it easier:
OM = IF
HELTAL = to make it round up/down to a sum without any decimal.
AVKORTA = SHORTENING

Any new idea?

Kind regards,
Marino

-----Ursprungligt meddelande-----

[Tinkerer]

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.

[Brian_Barker]

I'm not sure what you mean by "visible currency rounding" and am having to guess. I fear you have done what many an enquirer does: to give only a vague idea of what you are trying to achieve but add a formula that is the one thing you know is *not* working - so it cannot define your problem! Apart from anything else, what you will see as the result of this formula will depend on the cell formatting: how many fractional places you are choosing to display.

Is the "Sum to pay" simply the "Sum for all products on invoice" plus the "Tax"? (And I imagine that it is only the tax that will need rounding?) How are you calculating the sum to pay: are you simply summing the first two values and rounding the result, or are you using your separate rounding value to correct it? I think you must be doing the former, as using the latter you would get consistent results, even if they were wrong!

Why not do this a different way?

o Calculate your sum to pay directly from the earlier sum and the tax. You are probably doing this already, but you may be trusting the cell formatting to achieve any required rounding. Instead, modify the formula for the sum to carry out the rounding explicitly - so that the value actually stored in the cell (which you could see if you extended the displayed fractional places in the cell formatting) is correctly rounded.

o Now simply calculate the amount of rounding by finding the difference between the true sum of the earlier values and the rounded sum that you have already calculated in the bottom line. (There is no reason why the spreadsheet cannot calculate back up the column, of course.)

I trust this helps.

Brian Barker

[Tom_Davies]

Hi
I think the equation is unnecessarily over-complicated?  Surely it should just be something like

K50 = Sum of the price of all products  ?
K51 = Round (Tax)  ?

Total to pay = K50 + K51

or maybe do an extra round just to make completely sure

Total to pay = Round (K50 + K51)

So, in Swedish

K51 = HELTAL (Tax) ?

Total to pay = HELTAL (K50 + K51)

The trickiest bit should be the Tax calculation which is surely just

Tax = K50 x Vat%

Assuming none of the products is exempt from Vat or rated at a different Vat%!  In England i think we have to truncate instead of Round.  So

K51 = AVKORTA ( K50 x Vat% )

but even if you do have to do that in Sweden the final figure should still be rounded

Total to pay = Round (K50 + K51)

Regards from
Tom

[Tinkerer]

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.

[Tinkerer]

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.

[Tom_Davies]

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

[Marino_WellnessWebsh]

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

[Tom_Davies]

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

[Marino_WellnessWebsh]

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

[Luuk]

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)

[Tinkerer]

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.

[toodr]

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.################################################")
  len01=LEN(string0)
  For i=1 to len01
    string2=Mid(string0,i,1)
    If string2="," Then Mid(string0,i,1)="."
  next i
  Rounded=Val(string0)
Else 'if the number is integer or has a fractional part with up to 2 diggit
after the comma separator, we return it as it is
  Rounded=NumD
End if
End Function

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

[Brian_Barker]

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

[Tom_Davies]

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

[Mark_LaPierre]

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

[Luuk]

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