Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rounding Errors from Percent to Numbers
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rounding Errors from Percent to Numbers
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) wrote in message ups.com... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rounding Errors from Percent to Numbers
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/micro...06871cf92f8465 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 " wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can rounded numbers be summed without rounding errors? | Excel Discussion (Misc queries) | |||
Formula for rounding to the nearst eighth of one percent | Excel Worksheet Functions | |||
Rounding Errors Help | Excel Discussion (Misc queries) | |||
Rounding Errors | Excel Programming | |||
Unwanted Rounding Errors | Excel Programming |