Problem rounding decimals 1

[jar251169]

Hi all, I have a problema rounding decimals. I perform a calculations and the result is currency with a no more than 2 decimals (Round in cents of EURO). Well, following rules of round to EURO (€), round must be: 1..4=down, 5..9=up ""but"" ROUND function follows mathematical rules: 1..4=down, 6..9=up and 5=down if number of the left is pair and 5=up if number of the left is odd. Ex:

Number to test / Result of Round / Result must be(currency)
158,115..............158,12...............158,12
158,125..............158,12...............158,13

To test:

Private Sub Form_Load()
Dim test as Currency
test= (number to test)
Me.Label1.Caption= Round (test, 2)
End Sub

This is a big problem because you can loss much money ony one second.

Is there a function to round currency correctly???
Any custom function???
Any idea????

Thanks in advance.

Delgado.

 

[CajunCenturion]

you might want to try:

Value = (int((Value * 100) + 0.5)) / 100

 

[jmarler]

Take the number that you need to round and divide it by 100, add .5 , multiply by 100, and take the integer value. for eample . . .


10.116 (Starting Value)
1011.6 (Multiply by 100)
1012.1 (Add .5)
1012.0 (Take the integral value)
10.12 (Divide by 100)

Or in code

Dim RoundedValue As Currency
Dim OriginalValue as currency

OriginalValue = 10.125

RoundedValue  = Int((OriginalValue *100 + .5))/100

- Jeff Marler B-)

 

[Jon4747]

Hi Delgado,

Use the FormatCurrency Statement, it uses the Regional Setting -> Currency tab and rounds properly. e.g.
a = 158.125
Text1.Text = FormatCurrency(a)

output is $158.13

Jon

 

[jmarler]

Hey Jon,
Thanks for the info . . . I did not know that there was a formatcurrency command . . . you learn soething new everyday! Have a star! - Jeff Marler B-)

 

[jar251169]

OK, works, I knew this custom function already but it doesn't work if you define variables as double and use numbers as 128.105, 138.105, 139.105, etcccc, with 127.105, 126.105, etccc works fine. I suppose that this is a bug (with this custom function) of int and fix functions because if you use int and fix normally works correctly.

Formatcurrency () works fine but, is possible remove currency symbol (in my case €) from result?.

Thaks by all. Regards.

Delgado.

 

[petermeachem]

If I were you I would stick with the Int method and leave the format currency alone. I've had similar problems with VAT calculations where the VAT has to be rounded up to the nearest 1/2 p so the tax people can't complain they are being diddled. I had to Int round all the items, work out the VAT and round up and then work out the total of all the rounded values. I wish I hadn't used currency types in the first place. Life would have been much easier. Peter Meachem
peter@accuflight.com