formatting - Storing and performing calculations on long decimals in Excel without being rounded

Question

I am attempting to do some calculations in Excel on numbers that include long decimals.

However, Excel doesn't seem to let me populate the cells with the numbers I would like to use.

Example:

• If I enter 600000.00000000030000000000 into a standard cell on a new spreadsheet, it gets converted to 600000. This makes sense as the cell format is set to General.
• If I set the cell format to Number and set the decimals places to 20, I would expect to be able to enter the number properly.

Instead, the 600000.00000000030000000000 gets converted to 600000.00000000000000000000.

Does anyone know why this would happen?

Answer 1

Excel only stores 15 significant figures for numbers. Any figures beyond that automatically get rounded, regardless of number format.

Setting the cell to Text format first will store the number as a string with as many digits as you want, but Excel can't perform any calculations on it.

However, VBA can do calculations with Decimal type variables which can store up to 29 significant figures.

If you first store the values as text in Excel (setting the cell number format to Text before entering the values), you can create a User Defined Function in VBA to read the string values, convert them to Decimal values, perform your calculations and then return a string with the full precision calculated.

For example:

Function PrecisionSum(ra As Range) As String

    'Declare variables holding high precision Decimal values as Variants
    Dim decSum As Variant 

    'This loop will sum values from all cells in input range
    For Each raCell In ra
        'Read values from input cells, converting the strings to Decimals using CDec function
        decSum = decSum + CDec(raCell.Value)
    Next raCell

    'Return calculated result as a String
    PrecisionSum = Format(decSum, "0.00000000000000000000")

End Function

You'll need to write functions to do the operations that you desire.

Note that you'll still be limited by the accuracy of any functions you use in VBA. For example, the SQR function to return the square root of a number only returns a number with Double precision regardless of the precision of the input.