1

I have an odd issue that I've never come across, and it seems to be specifically to one sheet of an excel file I compiled.

The sheet includes some very precise astronomical formulae, and sometimes the calculated numbers need to he accurate to 12 decimal places.

The issue is that when the calculated value has a repeating decimal, Excel is giving an inappropriately short rounding with a host of trailing zeros. E.g the number that is supposed to be output as 7.760416666667 comes out as 7.760416670000.

I don't know why it is this sheet specifically that is giving this issue. I've looked around online for people having similar issues, but I can't find any. Does anyone know how to fix this problem?

Coenraad van Schoor
  • 111

1 Answers1

1

Usually, you can improve accuracy by carefully analyzing the order of calculations and avoiding operations that reduce accuracy, such as subtracting numbers that are close to each other.
Increased calculation accuracy is theoretically possible even in Excel using VBA and the Decimal data subtype. However, such calculations are inconvenient, because numbers with increased precision must have a text representation. The matter also becomes more complicated when, in addition to the usual arithmetic operations, functions come into play. Code of a function that performs basic arithmetic operations with increased accuracy:

Function OpDec(x, y, op)
    Select Case op
       Case "+": OpDec = CStr(CDec(x) + CDec(y))
       Case "-": OpDec = CStr(CDec(x) - CDec(y))
       Case "*": OpDec = CStr(CDec(x) * CDec(y))
       Case "/": OpDec = CStr(CDec(x) / CDec(y))
       Case Else: OpDec = CVErr(xlErrValue)
    End Select
End Function

Below is an illustration of the impact of the order in which operations are performed on the accuracy of calculations.
Of course, there are specialized programs like Mathematica that provide virtually any accuracy you need.

Precision

MGonet
  • 4,015