Does rounding work at all?

[Guest]

I still can't understand anything about this.

When I read the value of a cell in VB, the code returns a value rounded to 4
decimal places. The cell in question has a number that is out to about 8
decimals. And I'm not talking about the assignment, if you simply look at the
contents of the cell in the debugger, it's rounded off, ActiveSheet.Cells(i,
9) returns the wrong number!

But it's even worse. In order to get the SQL to find the row in the DB I
have to round both sides and compare the rounded values. So I have code
like...

theSql = theSql & " AND ROUND(value, 3) =" & Round(theTotal, 3)

The cell in question contains the value 1712.1465. Thus, the Round function
on the right side should return 1712.147. Well look what actually happens:

AND ROUND(value, 3) =1712.146

What the HECK is going on?!?

Maury

 

[Jake Marx]

Hi Maury,

The Value property should give you the actual value - I just tested and got
all 8 digits back. If your numbers are large, you will lose some precision,
as Excel will only store 15 significant digits. But you'd see that
happening on the worksheet as well.

The Round function in VBA works differently than that of the ROUND worksheet
function. The ROUND worksheet function will give you the rounding you're
used to, while the VBA Round function follows ASTM rounding (rounds down to
even numbers and rounds up to odd numbers when the next digit is 5):

?Application.Round(1712.1465, 3)
1712.147

?Round(1712.1465, 3)
1712.146

?Round(1712.1475, 3)
1712.148

--
Regards,

Jake Marx
www.longhead.com


[please keep replies in the newsgroup - email address unmonitored]

 

[Guest]

There are many ways to round:

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q194983

 

[Guest]

The Round function in VBA works differently than that of the ROUND worksheet
function. The ROUND worksheet function will give you the rounding you're
used to, while the VBA Round function follows ASTM rounding (rounds down to
even numbers and rounds up to odd numbers when the next digit is 5):

Holy! Why isn't this mentioned in the help files anywhere? I've never even
HEARD of this before!

Ok, well I know there's a trick for this but I've never had to do it before,
how do I use the spreadsheet function in my vb code? I'm assuming that's the
best solution here?

Maury

 

[Guest]

Application.round

--
Regards,
Tom Ogilvy

Holy! Why isn't this mentioned in the help files anywhere? I've never even
HEARD of this before!

Ok, well I know there's a trick for this but I've never had to do it before,
how do I use the spreadsheet function in my vb code? I'm assuming that's the
best solution here?

Maury

 

[Guest]

There are many ways to round:

Thanks Tom. I was able to fix this by doing the round in the spreadsheet and
then reading that number. This is only useful for matching though, I can't
actually use the data it finds because it's pre-rounded.

Maury

 

[Guest]

A possibly easier to follow description of the difference is that both
rounding methods round to the nearest rounded number, but handle ties
differently. The worksheet round function always rounds ties up, While the
VBA round function rounds ties up or down as needed to produce an even final
rounded digit.

Always rounding ties up can introduce an upward bias in the rounded numbers,
which is why the VBA method has been the ASTM standard for over 60 years, and
has generally been considered best practice for much longer.

Jerry

Hi Maury,

The Value property should give you the actual value - I just tested and got
all 8 digits back. If your numbers are large, you will lose some precision,
as Excel will only store 15 significant digits. But you'd see that
happening on the worksheet as well.

The Round function in VBA works differently than that of the ROUND worksheet
function. The ROUND worksheet function will give you the rounding you're
used to, while the VBA Round function follows ASTM rounding (rounds down to
even numbers and rounds up to odd numbers when the next digit is 5):

?Application.Round(1712.1465, 3)
1712.147

?Round(1712.1465, 3)
1712.146

?Round(1712.1475, 3)
1712.148

--
Regards,

Jake Marx
www.longhead.com


[please keep replies in the newsgroup - email address unmonitored]

I still can't understand anything about this.

When I read the value of a cell in VB, the code returns a value
rounded to 4 decimal places. The cell in question has a number that
is out to about 8 decimals. And I'm not talking about the assignment,
if you simply look at the contents of the cell in the debugger, it's
rounded off, ActiveSheet.Cells(i, 9) returns the wrong number!

But it's even worse. In order to get the SQL to find the row in the
DB I have to round both sides and compare the rounded values. So I
have code like...

theSql = theSql & " AND ROUND(value, 3) =" & Round(theTotal, 3)

The cell in question contains the value 1712.1465. Thus, the Round
function on the right side should return 1712.147. Well look what
actually happens:

AND ROUND(value, 3) =1712.146

What the HECK is going on?!?

Maury

 

[Jake Marx]

Hi Jerry,

A possibly easier to follow description of the difference is that both
rounding methods round to the nearest rounded number, but handle ties
differently. The worksheet round function always rounds ties up,
While the VBA round function rounds ties up or down as needed to
produce an even final rounded digit.

Thanks - I knew my explanation wasn't coming out right. <g>

--
Regards,

Jake Marx
www.longhead.com


[please keep replies in the newsgroup - email address unmonitored]

 

[Guest]

Wow, this just gets weirder and weirder.

Ok, so I followed links from the page Tom noted, and found some source code
that MS posted for doing various types of roundings. First off they decided
to use different syntax than the Round function, so to round to the 3rd
decimal it's not 3, it's 1000. Genius!

Anyway I fix that and it still doesn't work. Ready for this one? Here's the
code...

SymArith = fix(X * (10 ^ Factor) + 0.5 * Sgn(X)) / (10 ^ Factor)

In this case X is 1712.1465, and I am attempting to get it to round the same
way SQL will, which will be 1712.147. Ok, so the inside of the Fix returns
1712147 -- so in other works if you Fix this you'll get exactly what I would
expect.

Except Fix returns 1712146. So the rounding still doesn't work. Can anyone
explain THIS?

I found that if I took the inside of the Fix "out" and cast it to a Long
then it works...

y = X * (10 ^ Factor) + 0.5 * Sgn(X)
SymArith = Fix(y) / (10 ^ Factor)

However the numbers in question are fairly big, and thus overflow the Long
data type.

Wow, this is really frustrating!

Maury

 

[Guest]

In VBA
Factor = 3
X = 1712.1465
SymArith = Fix(X * (10 ^ Factor) + 0.5 * Sgn(X)) / (10 ^ Factor)
assigns the value 1712.147 to SymArith. Therefore your x must be
<1712.1465. What do you get for 1712.1465-X? Assuming that X contains the
result of calculations that equals 1712.1465 to 15 figures, then you might be
happier with
SymArith = Fix(CStr(X) * (10 ^ Factor) + 0.5 * Sgn(X)) / (10 ^ Factor)
which will get rid of accumulated binary approximations from the previous
calculations that might cause discrepancies beyond the 15th figure. (Recall
that the vast majority of terminating decimal fractions are non-terminating
binary fractions that can only be approximated, much as 1/3 can only be
approximated in decimal).

Jerry

 

[Gary Keramidas]

just curious what would happen if you used double instead of long