# Strange round behaviour?

round(12.5) should return 13, not 12, yes???

round (12.51) returns 13, so - what's happening here???

And I get the same error(??) with cint(12.5).. which also should return 13, not 12 (cint(12.51) returns 13) - if I've not forgotten all I've learnt at school many years ago..

«1

• : round(12.5) should return 13, not 12, yes???
:
: round (12.51) returns 13, so - what's happening here???
:
: And I get the same error(??) with cint(12.5).. which also should
: return 13, not 12 (cint(12.51) returns 13) - if I've not forgotten
: all I've learnt at school many years ago..
:
School wasn't so many years ago for me, but you've remembered correctly. 12.5 should round up. VB has it wrong. D'oh.

Jonathan

###
for(74,117,115,116){\$::a.=chr};((\$_.='qwertyui')&&
(tr/yuiqwert/her anot/))for(\$::b);for(\$::c){\$_.=\$^X;
/(p.{2}l)/;\$_=\$1}\$::b=~/(..)\$/;print("\$::a\$::b \$::c hack\$1.");

• Thanx!
So then I have to use something like this in stead..?

Function rund%(ByVal i As Double)
Dim des As Double
des = i - Int(i)
If des >= 0.5 Then
rund = Int(i) + 1
Else
rund = Int(i)
End If
End Function

OK for me, but it seems strange that it seems to be no litterature around this behaviour on the net. This should be regarded as a serious error in VB, if I (and you) are right, I suppose..

: : round(12.5) should return 13, not 12, yes???
: :
: : round (12.51) returns 13, so - what's happening here???
: :
: : And I get the same error(??) with cint(12.5).. which also should
: : return 13, not 12 (cint(12.51) returns 13) - if I've not forgotten
: : all I've learnt at school many years ago..
: :
: School wasn't so many years ago for me, but you've remembered correctly. 12.5 should round up. VB has it wrong. D'oh.
:
: Jonathan
:
: ###
: for(74,117,115,116){\$::a.=chr};((\$_.='qwertyui')&&
: (tr/yuiqwert/her anot/))for(\$::b);for(\$::c){\$_.=\$^X;
: /(p.{2}l)/;\$_=\$1}\$::b=~/(..)\$/;print("\$::a\$::b \$::c hack\$1.");
:
:

• : Thanx!
: So then I have to use something like this in stead..?
:
: Function rund%(ByVal i As Double)
: Dim des As Double
: des = i - Int(i)
: If des >= 0.5 Then
: rund = Int(i) + 1
: Else
: rund = Int(i)
: End If
: End Function
:
Yeah, for the subset of Round() functionality you want. Remember it has an optional second argument, but if you don't need that, don't worry.

: OK for me, but it seems strange that it seems to be no litterature
: around this behaviour on the net. This should be regarded as a
: serious error in VB, if I (and you) are right, I suppose..
:
I'd not noticed it until you mentioned it, but everything I've ever been taught says 0.5 rounds up, and I'm onto degree level stuff. Then, once you reach degree level maths you don't really have numbers any more, just letters, half of which are Greek, and weird symbols. :-)

The MSDN doesn't document a value at which it decides to round up or down. Sounds like someone in Redmond forget their = in their >= conditional when implementing the VB runtime library, or some people have a different opinion about rounding to you and I. I'd really love to hear their argument.

Jonathan

###
for(74,117,115,116){\$::a.=chr};((\$_.='qwertyui')&&
(tr/yuiqwert/her anot/))for(\$::b);for(\$::c){\$_.=\$^X;
/(p.{2}l)/;\$_=\$1}\$::b=~/(..)\$/;print("\$::a\$::b \$::c hack\$1.");

• Seems like Basic's rounding has always been messed up...

I think CInt/CByte/CLng will drop the fractional portion, not round.

Try this:

[code]CInt(Num + 0.5)
[/code]

: : Thanx!
: : So then I have to use something like this in stead..?
: :
: : Function rund%(ByVal i As Double)
: : Dim des As Double
: : des = i - Int(i)
: : If des >= 0.5 Then
: : rund = Int(i) + 1
: : Else
: : rund = Int(i)
: : End If
: : End Function
: :
: Yeah, for the subset of Round() functionality you want. Remember it has an optional second argument, but if you don't need that, don't worry.
:
: : OK for me, but it seems strange that it seems to be no litterature
: : around this behaviour on the net. This should be regarded as a
: : serious error in VB, if I (and you) are right, I suppose..
: :
: I'd not noticed it until you mentioned it, but everything I've ever been taught says 0.5 rounds up, and I'm onto degree level stuff. Then, once you reach degree level maths you don't really have numbers any more, just letters, half of which are Greek, and weird symbols. :-)
:
: The MSDN doesn't document a value at which it decides to round up or down. Sounds like someone in Redmond forget their = in their >= conditional when implementing the VB runtime library, or some people have a different opinion about rounding to you and I. I'd really love to hear their argument.
:
: Jonathan
:
: ###
: for(74,117,115,116){\$::a.=chr};((\$_.='qwertyui')&&
: (tr/yuiqwert/her anot/))for(\$::b);for(\$::c){\$_.=\$^X;
: /(p.{2}l)/;\$_=\$1}\$::b=~/(..)\$/;print("\$::a\$::b \$::c hack\$1.");
:
:

• Conversion, Rounding, and Truncation
When you convert a decimal value to an integer value, VBA rounds the number to an integer value. How it rounds depends on the value of the digit immediately to the right of the decimal place—digits less than 5 are rounded down, while digits greater than 5 are rounded up. If the digit is 5, then it's rounded down if the digit immediately to the left of the decimal place is even, and up if it's odd. When the digit to be rounded is a 5, the result is always an even integer.

For example, running the following line of code from the Immediate window prints "8," because VBA rounds down when the number immediately to the left of the decimal is even:

? CLng(8.5)

However, this code prints "10," because 9 is odd:

? CLng(9.5)

If you want to discard the decimal portion of a number, and return the integer portion, you can use either the Int or Fix function. These functions simply truncate without rounding. For example, Int(8.5) returns 8, and Int(9.5) returns 9. The Int and Fix functions behave identically unless you're working with negative numbers. The Int function rounds to the lower negative integer, while the Fix function rounds to the higher one.

For example, the following code evaluates to "-8":

? Fix(-8.2)

Using the Int function, on the other hand, yields "-9":

? Int(-8.2)

Note The Int and Fix functions always return a Double value. You may want to convert the result to a Long value before performing further operations with it.

VBA includes a new rounding function called Round, which you can use to round a floating-point or fixed-point decimal to a specified number of places. For example, the following code rounds the number 1.2345 to 1.234:

? Round(1.2345, 3)

Although the Round function is useful for returning a number with a specified number of decimal places, you can't always predict how it will round when the rounding digit is a 5. How VBA rounds a number depends on the internal binary representation of that number. If you want to write a rounding function that will round decimal values according to predictable rules, you should write your own. For more information, see the Visual Basic Language Developer's Handbook by Ken Getz and Mike Gilbert (Sybex, 1999

• Hi Guys,
Actually Its all going in the wrong way.
VB is doing it right but you are making it a little complex.

It works as this in Pure mathematics.

Suppose we have to round off a digit to two decimal places
ie no1.xxxxxxx to no1.xx

Now the limiting point here is 5.

It means if the digit after the precision digit is 5 or big ie 5,6,7,8,9

Then we have to increment the last decimal digit by 1

Otherwise leave it as it is

For Example

[code]
If we have to round a no upto two decimal places like :

145.456789

the ans will be 145.46 as the third place from decimal is 6 and accordingly second digit from decimal should be raised by 1 ie .46 instead of .45

All these no will produce 145.46

145.455789
145.456789
145.457789
145.458789
145.459789

But all of these no will produce 145.45

145.450789
145.451789
145.452789
145.453789
145.454789

[/code]

So if the no is 5 or above the no will be raised and if its 0 to 4 the no will not be touched.

In most Engineering exams it is specified to round off the answers to 3 or 4 decimal places. So we are used to that thing.

VB is right (Mostly to be exact)

Hope it Helps !!

[b]Regards
Er. [blue]Gurpreet Singh [/blue](B.E [blue]Mech.)[/blue][/b]
[hr]

• you're all looking at this the wrong way...too much code involved. Do this:
[code]
Dim nValue As Long
nValue = 8.5 '// or whatever the value/expression

'// this will raise the value + 0.5, enough to always round up
'// but not enough to make it go one higher.
nValue = Round(nValue, 1) + .5
[/code]

If nValue = 8.0 then it will try and round 8.5, thus resulting in 8.
If nValue = 8.5 then it will try and round 9.0, thus resulting in 9.
If nValue = 8.6 then it will try and round 9.1, thus resulting in 9.
In nValue = 8.9 then it will try and round 9.4, thus resulting in 9.

As long as you add on the + .5 it will always round up.
[code]<%
'// Programmed By: Zantos
'// VisualProgramming.NET
'// http://vp.funurl.com/
'// [email protected]
%>[/code]

• Nope, CInt(Num + 0.5) does not work:
CInt(1.5 + 0.5) returns 2, not 1

This seems to work ok:

Function RightRound(ByVal i As Double) As Long
RightRound = Round(i, 1) + 0.5
End Function

Ted

: Seems like Basic's rounding has always been messed up...
:
: I think CInt/CByte/CLng will drop the fractional portion, not round.
:
: Try this:
:
: [code]CInt(Num + 0.5)
: [/code]
:
: : : Thanx!
: : : So then I have to use something like this in stead..?
: : :
: : : Function rund%(ByVal i As Double)
: : : Dim des As Double
: : : des = i - Int(i)
: : : If des >= 0.5 Then
: : : rund = Int(i) + 1
: : : Else
: : : rund = Int(i)
: : : End If
: : : End Function
: : :
: : Yeah, for the subset of Round() functionality you want. Remember it has an optional second argument, but if you don't need that, don't worry.
: :
: : : OK for me, but it seems strange that it seems to be no litterature
: : : around this behaviour on the net. This should be regarded as a
: : : serious error in VB, if I (and you) are right, I suppose..
: : :
: : I'd not noticed it until you mentioned it, but everything I've ever been taught says 0.5 rounds up, and I'm onto degree level stuff. Then, once you reach degree level maths you don't really have numbers any more, just letters, half of which are Greek, and weird symbols. :-)
: :
: : The MSDN doesn't document a value at which it decides to round up or down. Sounds like someone in Redmond forget their = in their >= conditional when implementing the VB runtime library, or some people have a different opinion about rounding to you and I. I'd really love to hear their argument.
: :
: : Jonathan
: :
: : ###
: : for(74,117,115,116){\$::a.=chr};((\$_.='qwertyui')&&
: : (tr/yuiqwert/her anot/))for(\$::b);for(\$::c){\$_.=\$^X;
: : /(p.{2}l)/;\$_=\$1}\$::b=~/(..)\$/;print("\$::a\$::b \$::c hack\$1.");
: :
: :
:
:

• Well, I'm not sure if I understand what you mean here..
Only the first digit after the potential rounding digit is intersting here: if it is 5 or higher, the potential rounding digit should go up by one, if it is 4 or lower it should be left as it is:

VB does not behave like that, producing unpredictible results:

?round (1.5)
2 (ok, as it should)

?round (12.5)
12 (not 13, as it should)

If you really think this is not an error, I'm very interested in an explanation. (Maybe that's what you tried to do in your article, but I didn't get it, I think..) =

Ted

: Hi Guys,
: Actually Its all going in the wrong way.
: VB is doing it right but you are making it a little complex.
:
: It works as this in Pure mathematics.
:
: Suppose we have to round off a digit to two decimal places
: ie no1.xxxxxxx to no1.xx
:
: Now the limiting point here is 5.
:
: It means if the digit after the precision digit is 5 or big ie 5,6,7,8,9
:
: Then we have to increment the last decimal digit by 1
:
: Otherwise leave it as it is
:
: For Example
:
: [code]
: If we have to round a no upto two decimal places like :
:
: 145.456789
:
: the ans will be 145.46 as the third place from decimal is 6 and accordingly second digit from decimal should be raised by 1 ie .46 instead of .45
:
:
: All these no will produce 145.46
:
: 145.455789
: 145.456789
: 145.457789
: 145.458789
: 145.459789
:
: But all of these no will produce 145.45
:
: 145.450789
: 145.451789
: 145.452789
: 145.453789
: 145.454789
:
:
: [/code]
:
: So if the no is 5 or above the no will be raised and if its 0 to 4 the no will not be touched.
:
: In most Engineering exams it is specified to round off the answers to 3 or 4 decimal places. So we are used to that thing.
:
: VB is right (Mostly to be exact)
:
: Hope it Helps !!
:
: [b]Regards
: Er. [blue]Gurpreet Singh [/blue](B.E [blue]Mech.)[/blue][/b]
: [hr]
:
:
:
:

• : Well, I'm not sure if I understand what you mean here..
: Only the first digit after the potential rounding digit is intersting here: if it is 5 or higher, the potential rounding digit should go up by one, if it is 4 or lower it should be left as it is:

[blue]
Yes It is that way. But there is also one point which I failed to
tell earlier which concerns with 5 as the last digit.

[/blue]

:
: VB does not behave like that, producing unpredictible results:
:
: ?round (1.5)
: 2 (ok, as it should)
:
:
: ?round (12.5)
: 12 (not 13, as it should)
:

[blue]

VB is correct, I think I would have explained better in the first place.

Well It goes like this :

If the last digit is 6 or above the no should be raised by 1.

Now If the last digit is 5 then we have to look at the preciding digit.

If the preciding digit is an even ie 0,2,4,6,8 then the digit will NOT be raised by 1.

But If the preciding digit on left of last digit (ie 5) is an
odd one ie 1,3,5,7,9 then the value is raised by 1.

For example if we take case of 12.5
The last digit is 5
The preciding digit left to it is 2 which is even no
According to rule it will not be raised and will round off at 12

But if the no's are like
13.5,15.5,17.5,19.5
then as You can see the left digit are odd
And according to rule it will be raised by 1 producing round off at
14,16,18 and 20 respectively.

[code]

So The thing is that

If the last digit at the accuracy level (ie If the accuracy wanted is
2 digit from decimal then the third digit from decimal) is 6,7,8,9 then the digit immediately left to it will be raised by 1.

If the last digit at the accuracy level (ie If the accuracy wanted is
2 digit from decimal then the third digit from decimal) is 1,2,3,4 then the digit immediately left to it will NOT be changed.

If the last digit at the accuracy level (ie If the accuracy wanted is
2 digit from decimal then the third digit from decimal) is 5 then the digit immediately left to it will be considered.
If it is even as 0,2,4,6,8 then it will NOT be Raised.
But if it is odd ie 1,3,5,7,9 then it will be Raised by 1.

[/code]

[/blue]
[b]Regards
Er. [blue]Gurpreet Singh [/blue](B.E [blue]Mech.)[/blue][/b]
[hr]

• Oh, no - this does not work, of course..:

Function nValue(ByVal i As Double) As Long
nValue = Round(i, 1) + 0.5
End Function

Testing with ?nValue(25) return 26

I think I'll have to do it the clumsy way, after all?

Ted

: you're all looking at this the wrong way...too much code involved. Do this:
: [code]
: Dim nValue As Long
: nValue = 8.5 '// or whatever the value/expression
:
: '// this will raise the value + 0.5, enough to always round up
: '// but not enough to make it go one higher.
: nValue = Round(nValue, 1) + .5
: [/code]
:
: If nValue = 8.0 then it will try and round 8.5, thus resulting in 8.
: If nValue = 8.5 then it will try and round 9.0, thus resulting in 9.
: If nValue = 8.6 then it will try and round 9.1, thus resulting in 9.
: In nValue = 8.9 then it will try and round 9.4, thus resulting in 9.
:
: As long as you add on the + .5 it will always round up.
: [code]<%
: '// Programmed By: Zantos
: '// VisualProgramming.NET
: '// http://vp.funurl.com/
: '// [email protected]
: %>[/code]
:
:

• I see! But I'm still a bit confused concerning the "rule" you are referring to. Is this a special "pure mathematic rule" or something, having been constructed lately from "pure need" because of the computer's or the OS' way of handling digital numbers (as explained by righardt earlier in this thread, I think..)?

I need to convert a simple percentage number into an integer, i.e. 12.5 - which ordinary people (not mathemathicans) still suppose to be 13, not 12.

I think I'll recieve a lot of "error reports" form my customers if I'll let both 12.5 and 11.5 return 12...

Is the conclusion that we now have two different "rounding rules", right? - One for daily life purposes (always rounding up from 5) and one for "digital handling, pure mathematican" purposes (leaving with even numbers, and rounding up with odd numbers)?

Ted

• : I see! But I'm still a bit confused concerning the "rule" you are referring to.

[blue]
Well most persons are at first. But once you understand its value and importance specially in scientific and Engineering calculations you have to use it.

And I think you are confusing between "Rounding Off" & "Integer value".
Both are different things.
Vb Uses Round() for "Rounding Off"
& Int() for "Integer value"

[/blue]

:Is this a special "pure mathematic rule" or something, having been constructed lately from "pure need" because of the computer's or the OS' way of handling digital numbers (as explained by righardt earlier in this thread, I think..)?
[blue]

Totally wrong here dude.
Computers were born to help in mathematics , Mathematics was not born for computer.

Yes many will argue the existance of binary is only for Comp but things
like Rounding Off and basic calculations were discovered thousand of years ago.

Well I was reading an article about ancient Indian Veda's and guess what I found.

A sanskrit shlok(Paragraph) which was initially thought to be praise of some ancient god, is now revealed as value of Pi correct upto Ten Thousand Digits.

Mathematics was discovered long before we can think.

There is a separate branch of mathematics famous in India called Vedic Mathematics , In which you can calculate cube roots,suuare roots, squares etc faster than an calculator in your mind.

I tried myself few tricks and found its much great.

If we follow that logic to calculates square root and things like that , great time saving are recorded if tested in big loops.

I am still testing some tricks in comp.

SORRY , I drifted quite apart from the subject.

Actually Rounding Off and Integer value are very basic mathematical functions.

Rounding off means to cut few digits from the right of decimal in order to simplify the answer.

Integer Value is to remove decimnal part and use the original value.

For Example

Round(324.567,2) will yield 324.57
Int(324.567,2) will yield 324

Rounding Off is very very usefull in Engineering and Scientific Calculations and in some cases essential.

[/blue]

:
: I need to convert a simple percentage number into an integer, i.e. 12.5 - which ordinary people (not mathemathicans) still suppose to be 13, not 12.

[blue]
In that case use Int(num) function
It will give what you want but it cannot be called rounding off.
It will be called Integer value.

int(12.0)
int(12.1)
to...int(12.9) will yield 12

[/blue]

: Is the conclusion that we now have two different "rounding rules", right? - One for daily life purposes (always rounding up from 5) and one for "digital handling, pure mathematican" purposes (leaving with even numbers, and rounding up with odd numbers)?

[blue]
Both right and Wrong.
Usually people say rounding off when they are actually taking Integer Value.
But in Engineering calculations Rounding Off means to remove
all the digits which are right to the "Significant digit" by predetermined rules.

And These rules were born long before the existance of electricity.

Let me give an example of rounding off at Engineering level.
I hope That will clear Your Doubts.

Suppose in a Numerical we have to calculate value of X.

Now value of X depends on four variables ie A,B,C and D
like
X = (5*A + 4.56 * B + 9.7 * C ) * D

Now suppose each A, B , C and D are derives by other functions.

Suppose A = (45/22.7) * 834.67

now A value is
1653.8392070484581497797356828194

now suppose there are 1000 persons calculating A value and each person chosses a different value of A
ie

Mr Jonathan Chooses 1653.839207048458149779
Mr Ted chooses 1653.839207
Er. Gurpreet Singh Chooses 1653.84

and so on..

Now as you see the answer of X will be different if we choose different limit for A,B,C and D.

So there was a need of some standard of calculation limit.

So the Rounding Off came in practice.

The Teacher can be highly frustated if he has to check all the steps performed by 1000 students.

So they just check that whether the final answer is within limit or not.

Mostly the limit of 4 places of decimal is good in normal scientific practice.

But it will not be usefull to banks as they can't charge less than a penny.

So the Bank rounding off limit would be 2 places from decimal.

So each person uses different Rounding limit.

In case of calculator U can give two buttons ie
Integer Value and Round Off Value.

I think It will clear few doubts.

And one more thing.

We dont use only right digit from significant value.

Actually we rounf off the whole number fomr right

Suppose we have to round off 234.76369636559300111 to 2 decimal places.
It will be done in many steps to be accurate like
Original number 234.76369636559300111
234.7636963655930011
234.763696365593001
234.76369636559300
234.7636963655930
234.763696365593
234.76369636559
234.7636963656 =====> Change
234.763696366 =====> Change
234.76369637 =====> Change
234.7636964 ======> Change
234.763696
234.76370 ======> Change(Not sure in this step o raise 6 or not)
234.7637
234.764 ===> change

But its easier to use right most digit as it mostly (99.9 %) gives accurate round off)

I Hope It Sheds Some Light on the Matter.

Sorry If I bored Too Much.

[/blue]

[b]Regards
Er. [blue]Gurpreet Singh [/blue](B.E [blue]Mech.)[/blue][/b]
[hr]

• Great! And very intersting! I think I do see the reason why we have the even/odd rule now.. which I was not aware of. Few people have heard of this, I think, at school we were always told that 0-4 rounds the preceding digit down, 5-9 rounds up.

But OK. I'm tempted to reforme the world now..

Only one comment:

I'm not confusing the int and the round functions..

What I thougt I needed, was a "predictable" round function, returning 13 from 12.5, 12.6, 12.7, 12.8 and 12.9.

int(12.x) will return 12 in all cases, of course.

The "daily life" problem still is that both round(11.5) and round(11.6) returns 12 (which is what most people would expect) - but, on the other hand, round(12.5) returns 12 and round(12.6) returns 13 (which does not make much sense to the man in the street - who has never heard of the even/odd part of the scientific rounding rules).

I'm conviced - but, to be honest, I'm not really sure what to do: using the "correct" VB function, or use my own "common sense" one But that's my problem, of course

Ted

: : Well, I'm not sure if I understand what you mean here..
: : Only the first digit after the potential rounding digit is intersting here: if it is 5 or higher, the potential rounding digit should go up by one, if it is 4 or lower it should be left as it is:
:
: [blue]
: Yes It is that way. But there is also one point which I failed to
: tell earlier which concerns with 5 as the last digit.
:
: [/blue]
:
: :
: : VB does not behave like that, producing unpredictible results:
: :
: : ?round (1.5)
: : 2 (ok, as it should)
: :
: :
: : ?round (12.5)
: : 12 (not 13, as it should)
: :
:
: [blue]
:
: VB is correct, I think I would have explained better in the first place.
:
:
: Well It goes like this :
:
: If the last digit is 6 or above the no should be raised by 1.
:
: Now If the last digit is 5 then we have to look at the preciding digit.
:
: If the preciding digit is an even ie 0,2,4,6,8 then the digit will NOT be raised by 1.
:
: But If the preciding digit on left of last digit (ie 5) is an
: odd one ie 1,3,5,7,9 then the value is raised by 1.
:
: For example if we take case of 12.5
: The last digit is 5
: The preciding digit left to it is 2 which is even no
: According to rule it will not be raised and will round off at 12
:
: But if the no's are like
: 13.5,15.5,17.5,19.5
: then as You can see the left digit are odd
: And according to rule it will be raised by 1 producing round off at
: 14,16,18 and 20 respectively.
:
: [code]
:
: So The thing is that
:
:
: If the last digit at the accuracy level (ie If the accuracy wanted is
: 2 digit from decimal then the third digit from decimal) is 6,7,8,9 then the digit immediately left to it will be raised by 1.
:
:
: If the last digit at the accuracy level (ie If the accuracy wanted is
: 2 digit from decimal then the third digit from decimal) is 1,2,3,4 then the digit immediately left to it will NOT be changed.
:
:
: If the last digit at the accuracy level (ie If the accuracy wanted is
: 2 digit from decimal then the third digit from decimal) is 5 then the digit immediately left to it will be considered.
: If it is even as 0,2,4,6,8 then it will NOT be Raised.
: But if it is odd ie 1,3,5,7,9 then it will be Raised by 1.
:
:
: [/code]
:
:
: [/blue]
: [b]Regards
: Er. [blue]Gurpreet Singh [/blue](B.E [blue]Mech.)[/blue][/b]
: [hr]
:
:
:
:

• : Nope, CInt(Num + 0.5) does not work:
: CInt(1.5 + 0.5) returns 2, not 1

It should, shouldn't it?

1.0 = 1
1.4 = 1
1.499999 = 1
1.5 = 2

That's what that formula gives you and that's what rounding means (to most people anyway).

Or maybe it really has been too long since I was in school...