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: [email protected]
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
