1

I am trying to have vba calculate certain values and display only the answers in my spreadsheet. While this is working fine for the most part, for one of my answers there is a rounding error.

In the relevant cell instead of display 12,950.2217, it is only displaying 12,950.9200.

This is my formula:

Sheet2.Cells(i, 19).Value = Sheet2.Cells(i, 6).Value - Sheet2.Cells(i, 18).Value

I imagine this might have something to do with declaring variables but for all the other formulas it is working fine.

Thanks

Community
  • 1
  • 1
D. Jay
  • 13
  • 3

5 Answers5

1

The best way to avoid a rounding error in programming, when you are working with floating numbers, is to use decimal conversion.

In VBA it would look like this:

cells(1,1) = CDec(cells(2,1))-CDec(cells(3,1))

The idea of CDec or decimal in .Net is that it avoids the inaccuracy of the floating point numbers. Why Are Floating Point Numbers Inaccurate?

Vityata
  • 42,633
  • 8
  • 55
  • 100
  • 1
    While floating point numbers are inaccurate, it seems unlikely that subtracting two number should cause an error of around 0.7, as descriped in the original post. There must be something else wrong as well... – Jakob Busk Sørensen Sep 05 '17 at 08:08
0

I suspect your cells have a currency format applied. Use Value2 rather than Value:

Sheet2.Cells(i, 19).Value2 = Sheet2.Cells(i, 6).Value2 - Sheet2.Cells(i, 18).Value2
Rory
  • 32,730
  • 5
  • 32
  • 35
0

There is nothing wrong with your code. Therefore the code will provide the correct result. Try this code.

With Sheet2.Rows(i)
    Debug.print .Cells(19).Value = .Cells(6).Value - .Cells(18).Value
End With

This will print the correct result to the Immediate window. Therefore the fault is with the NumberFormat of the cell which displays the rounded result. Check the Numberformat.

Variatus
  • 14,293
  • 2
  • 14
  • 30
0

Thanks for all the responses. In the end, adding Value2 seemed to fix the problem. It is not entirely clear why there were no decimal issues for some and not others. It seems that it was only for the ones where the formula was x - y that the decimal problem arose. For formulas involving division and multiplication it was ok.

D. Jay
  • 13
  • 3
0

When grabbing decimal values, Dim your variable as Double instead of Integer. This will prevent the automatic rounding to the nearest integer. Hope this helps!

Psychonaut3
  • 26
  • 5