Financial rounding 1

[SBerthold]

Who has a correct formula to financial round with?
(regardless if the number is positive, negative, whole, fractional)

The Function needs to work like the VB Round function.

I have yet to find one, or create a way that is correct, but still reasonably fast.

 

[boflexson]

How about this::

Public Function RoundAmount(ByVal dblAmount As Double, ByVal lngDecimalPlaces As Long) As Double
Dim dblWholeNumber As Double
Dim intSign As Integer
Dim dblFactor As Double
Dim lngCounter As Long

If dblAmount < 0 Then
intSign = -1
Else
intSign = 1
End If

' If it is a negative number make it positive
dblAmount = dblAmount * intSign
dblFactor = 1
If lngDecimalPlaces > 0 Then
For lngCounter = 1 To lngDecimalPlaces
dblFactor = dblFactor * 10
Next
End If

dblWholeNumber = dblAmount * dblFactor
dblWholeNumber = Abs(Fix(dblWholeNumber))
If (Abs(Val((dblAmount * dblFactor) - dblWholeNumber)) > 0.4999995) Then
dblWholeNumber = dblWholeNumber + 1
End If
RoundAmount = (dblWholeNumber / dblFactor) * intSign

End Function
-Adam T. Courtney
Stratagem, Inc.

http://www.Stratagemnet.com

 

[SBerthold]

Appreciate your help!

But the function doesn't always round correctly.

 

[sunaj]

Format(12.34535234, &quot;0.00&quot Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[SBerthold]

Thankyou.

The Format function will not because the format may change depending on the number of rounding digits, as my question states it doesn't matter what the type of input is, but the output desired has to be flexible like the round function.

So, I may need to round to 2 places, or 3, or 4 or 5, etc, and I just hate the idea of a loop to add zeros (&quot;0.00000&quot in order to define the format.

The user will be able to define the desired format. So, I guess I could do a loop, but this will be much slower, especially with the Format function, and I would also like to use this also to round thousands of numbers in a database.

 

[johnwm]

Unfortunately Format uses the same internal functionality that Round does. That means that number ending in .5 will always round to the even number
eg Format(1.455, &quot;0.00&quot gives 1.46
Format(1.465, &quot;0.00&quot also gives 1.46

MSDN suggests use of Currency or Decimal datatypes (which are not fully supported in VB6, as they put it 'write your own rounding procedure'



Let me know if this helps
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'There are 10 kinds of people in the world: those who understand binary, and those who don't.'

 

[CCLINT]

I use the Decimal data type as the Currency has too few decimal places for some financial calculations.
If you realize what is being done in the first answer, and use the correct data type, you can get this down to one short line of code. [/b][/i][/u]_{*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}

 

[CCLINT]

>&quot;Format(1.465, &quot;0.00&quot also gives 1.46&quot;

johnwm: Which Format function are you using? You should get 1.47
[/b][/i][/u]_{*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}

 

[sunaj]

Format is not based on round. it always rounds .5 upwards

What about

format(TheNumberAsVariant,&quot;0.&quot; & string(TheNumberOfDecimals,&quot;0&quot) Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[johnwm]

There's something odd happening again:

This is copy and pasted from Immediate window
?format(1.465, &quot;0.00&quot
1.46
?format(1.455, &quot;0.00&quot
1.46

This is VB6.

I've just realised that I re-installed on new harddrive yesterday and haven't put SP5 on - I'll be back to you shortly Let me know if this helps
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'There are 10 kinds of people in the world: those who understand binary, and those who don't.'

 

[sunaj]

I get:
?format(1.455, &quot;0.00&quot
1.46
?format(1.465, &quot;0.00&quot
1.47 Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[johnwm]

Very odd! I've put SP5 back on and I still get the result as before
Anyone got any suggestions as to why?
Let me know if this helps
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'There are 10 kinds of people in the world: those who understand binary, and those who don't.'

 

[merlinx]

I went through this last year on a project involving very large Double's. I couldn't find a straigt through method to avoid all of the possible mistakes that can be produced. You have to take into account that the math is done on a float basis, this means that your variable will be converted to a float, processed and returned to/as your variable type. Errors which can happen are dependent on the size of your number. What I had to do was write my own rounding function, which produced what I wanted and nothing else. The Double for example is 15 point precesion and the rest are 0's. If you try to change anything outside of this area you can produce all sorts of effects, ( I had one where it rounded 0.1 + x to x+600), You should first decide what number range is needed (+/- max) and work from there.

Which range do you really need?

If your number range isn't to large you could do something like this:

if(Round(X, 1) = Round(X, 1) + 0.1)then
X = Round(X, 1) - 0.1
Else
X = Round(X, 1)
end if

 

[merlinx]

Update :

if(Round(X, 1) = Round((X + 0.1), 1)then
X = Round(X, 1) - 0.1
Else
X = Round(X, 1)
end if

 

[CCLINT]

merlinx: That's an interesting method that I want to look at more. But for now, it still seems to produce a wrong result:

x = 2.35 = 2.3
x = 2.5 = 2

here is a fast method:

RoundX = Fix(CDec(dNumber * 10 ^ lDecimalPlaces+ (Sgn(dNumber) * 0.5))) / 10 ^ lDecimalPlaces

I put this into a proceedure, and am declaring a variable to hold the 10 ^ lDecimalPlaces just to make it more visible:

Public Function RoundX(ByVal dNumber As Double, Optional ByVal lDecimalPlaces As Long = 2) As Double
Dim xFactor As Long
xFactor = 10 ^ lDecimalPlaces

RoundX = Fix(CDec(dNumber * xFactor + (Sgn(dNumber) * 0.5))) / xFactor

End Sub

The formula could also be written like this:

Dim dSubTotal As As Double
dSubTotal = dNumber * xFactor + (Sgn(dNumber) * 0.5)
RoundX = Fix(dSubTotal) / xFactor

Or this:

RoundX = Sgn(dNumber) * Fix(CDec(Abs(dNumber) * xFactor) + 0.5) / xFactor

Or this:

RoundX = Sgn(dNumber) * (Fix(CDec(Abs(dNumber) + 5 / (xFactor * 10)) * xFactor) / xFactor)


So, test which is fastest and most accurate [/b][/i][/u]_{*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}

 

[merlinx]

I tried that also. The problem was with the numbers in my case being so large that in many cases x^10 put them out of bounds. To avoid it I seperated the number into to parts p1 being left side of the decimal point and p2 the right side.
Since VB doesn't have a Trunc function, (to my knowledge), I used Format(x,&quot;#&quot and checked to see if it was rounded, if yes I subtracted the difference. The difference being p1+p2 - x.

 

[CCLINT]

merlinx: I could assume that if the decimal places was that high that you wouldn't need financial rounding, but mathematical rounding?
I never heard of any financial roundings to that many decimal places. If needed, if could easily be limited to 10 or 15 places.

Therefore the Round function should work for your purpose.

Otherwise, just change &quot;Dim xFactor&quot; to a Single or a Double and, if needed, use CDbl instead of CDec.

Somewhere you'll run into a wall no matter what and the &quot;last&quot; digit to round will not be possible. Therefore, 100% accuracy is never possible, depending upon the size of the number and number of decimal places.

Take these for instance:
?round((.4/(10^1))+(.4/(10^2))+(.5/(10^3)),3)
4.5E-2

Icrease by a factor of 17
?round((.4/(10^18))+(.4/(10^19))+(.5/(10^20)),20)
4,5E-19

Increase by a factor of 18
?round((.4/(10^19))+(.4/(10^20))+(.5/(10^21)),21)
4,4E-20

Here also a limit was reached and the last digit was ignored. [/b][/i][/u]_{*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}

 

[SBerthold]

I am still testing - so far so good!

 

[merlinx]

My project dealed with the breaking down of molecules into ions. the results dealed with mass and ion intensity. The files needed to be exported to a new format with no changes in value. Thats why the round was so important. And yes the numbers range in sci-notation. What made this difficult is that the values had to be converted to text and rounded to the preffered decimal length with out sci-notation. I wrote a module which checked the variable type and also had its own Trunc function. The Trunc function was most important for accomplishing this. The variable was not checked for negative because there are no negative values in these data bases. After Determining VarType and size I created a string of the variable, Then I divided the string into two parts, The second part was handeled added back to the first part and returned as the wished string.

 

[CCLINT]

A word of warning:
The CDec and CCur must be handled with care, and their usage must be thouroghly thought out before implementing.

It is a scaled integer data type (fixed-point data type), just like the CCur() function.

Even though it will handle a relative large number for business purposes, for anything else it may get you into trouble.

-It will handle a total of 15 digits to the left of the decimal when no digits are to the right:
999,999,999,999,999.0

- It will handle a total of 15 digits including in the count all digits to the left and right of the decimal:
9,999,999.99999999
9,999,999,999.99999
So, 15 digits and the decimal point can be anywhere

- It will handle a total of 28 digits to the Right of the decimal when no digits are present on the left of the decimal:

0.1234567890123456789012345678

more digits may not produce an error but will be returned with an unexpected value, it will start rounding and chopping of:

CDec(99,999,999,999.99999) 16 digits results in 100,000,000,000.0 Rounded

But a lower whole number with too many digits will cause the decimal digits to get chopped off:

?CDec(1,234,567,890.123456) returns 1,234,567,890.123456
But ?CDec(12345678901.123456) returns 1,234,567,890.12345
The last 5 got chopped off.

Therefore, for normal financial operations, if you convert the values with CDec or CCur before calculating, you'll probably be alright.
As said, the same problem lies with the CCur; the advantage with the CCur is that you can define a variable as currency to prevent problems, and with Decimal, there isn't a variable type (except variant)

Otherwise use CSng, and for extremely high numbers use CDbl.

And it is best to use CSng or CDbl with the example above, or better yet, use the function above that uses the dSubTotal variable.
Then there shouldn't be any problems at all.
(I also changed the xFactor variable type in order to handle larger numbers)

Public Function RoundX(ByVal dNumber As Double, Optional ByVal lDecimalPlaces As Long = 2) As Double
Dim xFactor As Double

xFactor = 10 ^ lDecimalPlaces
RoundX = Fix(CDbl(dNumber * xFactor + (Sgn(dNumber) * 0.5))) / xFactor

End Sub


[/b][/i][/u]_{*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}