How Excel to do calculations using more than 12 digits precision?

[Guest]

I would like to do calculations in my spread sheet with 30 digits precision.
I figure this is possible because the number display allows 30 digits. I went
through the help function, and selecting the Precision as Displayed option
under the Tools, Options, Calculation tab make no difference. Excel still
rounds off at 12 digits. How do I get higher precision calculations?

 

[Guest]

Precision as displayed means that what you see is where excel stops
calculating... hopefully you have a backup of the file from before you turned
that on or can recalculate the results with it off!

The way to display all 30 digits of precision is to format the cells as a
number with 30 as the number of digits.

Select the cell or range of cells, press CTRL+1, go to the number tab,
choose 'number' from the list and change the decimal places box to 30.

 

[Niek Otten]

Excel's precision is 15 significant decimal digits. You can use numbers that are much larger, but the precision remains 15 digits.
There are several add-ins available that have more precision, but then you can't use any of the results in the standard Excel
functions.
Here's one:

http://precisioncalc.com:80/

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I would like to do calculations in my spread sheet with 30 digits precision.
| I figure this is possible because the number display allows 30 digits. I went
| through the help function, and selecting the Precision as Displayed option
| under the Tools, Options, Calculation tab make no difference. Excel still
| rounds off at 12 digits. How do I get higher precision calculations?

 

[Dana DeLouis]

What would be a typical calculation that you have in mind?
There may be an easy workaround in Excel.

 

[macropod]

Hi,

Take a look here:
http://digilander.libero.it/foxes/index.htm
This XP add-in claims to be able to give precision up to 250 significant digits. I think you can still find older versions of the
add-in for earlier Excel versions.

Cheers

--
macropod
[MVP - Microsoft Word]


| I would like to do calculations in my spread sheet with 30 digits precision.
| I figure this is possible because the number display allows 30 digits. I went
| through the help function, and selecting the Precision as Displayed option
| under the Tools, Options, Calculation tab make no difference. Excel still
| rounds off at 12 digits. How do I get higher precision calculations?

 

[Guest]

Thanks macropod, Dana DeLouis, Niek Otten, and ~L for your quick responses.
Evidently even though Excel allows the selection of 30 digits display to be
made, it only uses 15. My mistake before when I said 12. What that means to
me is that if the result of the cell formula is one third, that it will
display as 0.333333333333333000000000000000 and only 15 digits will be used
in any further calculations. I do see that there are several packages that
can be downloaded and promise to allow greater precision calculations under
Excel. I'll research them This does leave one question in my mind. Why would
Excel allow the display of 30 digits when it just plugs in zeros for the last
15?

 

[Guest]

Thanks macropod, Dana DeLouis, Niek Otten, and ~L for your quick responses.

Evidently even though Excel allows the selection of 30 digits display to be
made, it only uses 15. My mistake before when I said 12. What that means to
me is that if the result of the cell formula is one third, that it will
display as 0.333333333333333000000000000000 and only 15 digits will be used
in any further calculations. I do see that there are several packages that
can be downloaded and promise to allow greater precision calculations under
Excel. I'll research them.

This does leave one question in my mind. Why would Excel allow the display
of 30 digits when it just plugs in zeros for the last 15?

 

[Dana DeLouis]

I would like to do calculations in my spread sheet with 30 digits

precision.
... This does leave one question in my mind.
Why would Excel allow the display of 30 digits...

Hi. Not quite 30, but close. You may be interested in the following
Excel's VBA feature...

Sub Demo()
Dim n As Variant
n = CDec(11) / 7
Debug.Print FormatNumber(n, 28)
End Sub


1.5714285714285714285714285714