CODEWITHSUNDEEP
excelvba
Excel Developers
Excel Developers

Reputation: 2825

Rounding error when using INT function

I have user input in two cells, named "UpperRangeHigh" and "UpperRangeLow". I have the following code:

dRangeUpper = [UpperRangeHigh] - [UpperRangeLow]
lLines = Int(dRangeUpper * 100 / lInterval)

The user inputs 120.3 and 120 into the input cells respectively. lInterval has the value 10. VBA produces the result of 2 for lLines, instead of 3.

I can overcome this problem by adding 0.000000001 to dRangeUpper, but I'm wondering if there is a known reason for this behaviour?

Upvotes: 2

Views: 1418

Answers (3)

user2063626
user2063626

Reputation:

Kindly use single or double when working with decimals to get more accurate results.

Sub sample()
    Dim dRangeUpper As Double
    dRangeUpper = CDbl("120.3") - CDbl("120")
    lLines = Round(CDbl(dRangeUpper * 100 / 10), 4)
End Sub

output = 3

enter image description here

Upvotes: 3

scott
scott

Reputation: 2265

This is a known Floating point issue within Excel

http://support.microsoft.com/kb/78113

From MSDN:

To minimize any effects of floating point arithmetic storage inaccuracy, use the Round() function to round numbers to the number of decimal places that is required by your calculation. For example, if you are working with currency, you would likely round to 2 decimal places:

=ROUND(1*(0.5-0.4-0.1),2)

In your case, using round() instead of INT should do the trick using 0 rather than 2

Upvotes: 0

David Zemens
David Zemens

Reputation: 53623

This appears to be a problem with Excel's calculation and significant digits. If you do:

=120.3 - 120 and format the cell to display 15 decimal places, the result appears as:

0.2999999999999970

Here is a brief overview which explains how Excel uses binary arithmetic and that this may result in results divergent from what you would expect:

http://excel.tips.net/T008143_Avoiding_Rounding_Errors_in_Formula_Results.html

You can overcome this by forcing a rounded precision, e.g., to 10 decimal places:

lLines = Int(Round(dRangeUpper, 10) * 100 / lInterval

Upvotes: 4

Related Questions