[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.################################################")
        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



--
View this message in context: 
http://nabble.documentfoundation.org/Visible-currency-rounding-tp4065342p4066292.html
Sent from the Users mailing list archive at Nabble.com.

-- 
To unsubscribe e-mail to: users+unsubscribe@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