Round Function 1

[devnull69]

Hi there,

as far as you didn't already know: The use of the function ROUND is essentially different in both VB and VBA. VB's round function performs the rounding mathematically, VBA performs it mercantilistic.

Example:

VB : Round(1.125,2) = 1.12 !!
VB : Round(1.126,2) = 1.13
VB : Round(1.135,2) = 1.14

VBA: Round(1.125,2) = 1.13 !!
VBA: Round(1.126,2) = 1.13
VBA: Round(1.135,2) = 1.14

So in VB you better use the Format function for the proper rounding.

 

[segmentationfault]

There are two definitions for rounding to the nearest integer. In one sense, x.5 is rounded up if x is odd and down if x is even. The more prevalent definition is that .5 is always rounded up.

Is this what is meant by mercantilistic? I'm not aware of different names for these two schemes, nor how merchants typically round, but either way is proper depending on the context. It would be magnificent if VB/A explicitly requested or indicated which method is being used.

 

[cheerio]

The posting is not factually correct - perhaps there are differences between versions

My machine has Vb6 and Office 2000

Rounding 1.125 to 2 decimal places gives:

VB, VBA (both Access and Excel) 1.12
Excel worksheet function 1.13
Access query 1.12

Rounding to the nearest even value may make sense when dealing with items such as commercial prices where always rounding one way might be unfair because prices are always in units of half a cent so a price of 1.123 would not arise.

If the final digit of the source data is simply random as it might be for scientific observations then it does not matter if .005 is always rounded up as it will be as frequent as any other end value.

There are arguments in favour of both methods. (Personally I like to round down when calculating amounts I must pay such as tax).

What is unforgivable is that two different functions have been given the same name creating a trap for the unwary. Secondly, it is a pity that Access and Excel are inconsistent at user interface level.

Ken

 

[devnull69]

It's even worse ... you tested the function with Access2000 and it gives 1.12 in either way (query or VBA). So that's consistent to my VB6 Enterprise. But Access97 gives 1.13, so you will have to take good care when upgrading your application from Office97 to Office2000

 

[cheerio]

Perhaps all will be resolved when we move office to the .NET libraries and share our definitions of functions like Round across all applications

Ken