Rounding Errors from Percent to Numbers

[akh2103]

Hello--I have a macro that inserts tables from word into excel. The
data in the word tables is in percentages (ex. 4%) where we need it to
be in simple integers (ex. 4). I have written a program that makes this
conversion and it works fine except for occasional small rounding
errors (ex. it will turn 4% into the number 5). I don't know how excel
rounds so well, but can anyone spot the place in my code (below) where
the rounding errors are occuring. Is there an easy way to solve this
problem or an easier way to make this conversion?

Thanks, Abe

Sub values()

Dim item As Range
ActiveSheet.UsedRange.Select

For Each item In Selection
If item.Value < 1 And item.Value = 0 = False Then item.Value =
item.Value * 100 Else
Next

ActiveSheet.UsedRange.Select
Selection.NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(*
""-""??_);_(@_)"
Selection.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"

End Sub

 

[Bob Phillips]

Don't know if this is the problem but there are some oddities in the code

Sub values()

Dim item As Range
With ActiveSheet
For Each item In .UsedRange
If item.Value < 1 And item.Value <> 0Then
item.Value = item.Value * 100
End If
Next
..UsedRange.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
End With

End Sub

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Jerry W. Lewis]

Can you provide more information? There are two issues with the potential
for this kind of behavior, but I can't reproduce this behavior in the number
range that you indicated (using either Excel 2000 or Excel 2003).

If you can reproduce the behavior, I would be very interested in seeing the
result of the D2B function from
http://groups.google.com/group/microsoft.public.excel/msg/b106871cf92f8465
for the cell value both before and after running your code.

Excel (and almost all other computer software) does binary math. Your code
indicates that you understand that 4% is a formatted display of 0.04. Most
terminating decimal fractions are nonterminating binary fractions that can
only be approximated (much as 1/3 can only be approximated in decimal. 100
is not a power of 2, so multiplying by 100 can potentially result in a value
that would round differently than the original number. However, I cannot
reproduce a rounding difference in the vicinity of 4.5%.

For reasons that are not clear to me, some perfectly valid binary floating
point numbers are not permitted as cell values in Excel (except as the result
of a formula). For Instance, if you put =0.5+2^-51 in A1 and Copy/Paste
Special|Values the contents of A1 into A2, then the formula =(A1-A2) will
return 4.44E-16 instead of zero because 0.5+2^-51 is not permitted as a cell
value, and therefore gets rounded to 0.5. Since your code stores a value
instead of a formula, it is possible that this "feature" has subtly changed
the resulting value. Once again however, I cannot reproduce a rounding
difference in the vicinity of 4.5%.

A third thing to be aware of is that the VBA round function rounds per the
ASTM standard (when rounding away exactly 5, round up or down as needed to
make the rounded number even) whereas the worksheet round function (and
presumably worksheet formats) use a simplified method of rounding (when
rounding away exactly 5, always round up), so rounding in VBA could produce
different results than rounding/formatting in a worksheet. However You do
not appear to be using the VBA round function, so this third option does not
seem to apply.

Jerry

 

[akh2103]

Jerry--Thanks for your help. This is exactly the sort of response I was
looking for. I am new to programming and your explanation of
binary/terminating fractions was really helpful to me. Let me take a
look and see if I can reproduce the behaviour. If I can, then I will
get back to you with the numbers that worked.

Also, I have changed my code so that the cells that I have multiplied
by 100 go into a "general" format. I thought that perhaps different
excel formats might have different rounding conventions and so shifting
from one format to another might lead to minute rounding errors.

I will let you know. (If you want to send me your email so I can keep
you posted, email it to me at (e-mail address removed))
-Abe