Is there a limit to the number of decimal places allowed in a formula?

Goo afternoon miamianderson

Excel can display up to 30 decimal places, but can only calculate to 15 significant figures.
If you really need that kind of accuracy, then Excel may not be the right software for your purposes.

https://en.wikipedia.org/wiki/Numeri...icrosoft_Excel

HTH

DominicB

Hi,

Yes you're limited to 15 decimal places.

Depending on what you're doing there might be some fancy way of slicing a text string of digit characters and manipulating the sub strings as if individually they were numerical decimals.

Excel stores numbers as double precision floating point numbers, which means about 15 digits. So a solution like PetABC123 suggests will add a bunch of trailing 0s beyond what double precision allows. If you need more than 15 digits of precision, then you need something other than Excel.

VBA has a "Decimal" data type that, I believe, will store numbers to about 28 digits https://msdn.microsoft.com/VBA/Langu...imal-data-type If that is enough precision, then you could store numbers as text in Excel, bring them into VBA, perform the calculations, then output the numbers back to Excel as text strings (otherwise Excel will convert them to double precision and you will lose the extra precision).

Other programming languages have other data types (like Quad precision) that may allow even more precision.

I am aware (but have never used) arbitrary precision algorithms. I have seen one example on this forum: https://www.excelforum.com/excel-pro...ith-excel.html but a resolution was never posted. The Wikipedia link may be useful: https://en.wikipedia.org/wiki/Arbitr...ion_arithmetic If you are allowed to use programming languages other than Excel/VBA, you should be able to find a programming language with an available library for these calculations that may save you developing your own procedures for implementing these algorithms.

At this point, I would say that the big question is how much precision do you need?

"the big question is how much precision do you need?" -- MrShorty
I know that the question is directed toward the OP, however I thought that it might be of some help in making that determination to note that this NASA article states "For JPL's highest accuracy calculations, which are for interplanetary navigation, we use...pi rounded to the 15th decimal".

When all else fails, try multiplying the number by something like 10 000. Although excel will still only show 15 characters

Having spent years in analytical chemistry, wrestling with Measurement Uncertainty, I do struggle to envisage anything that justifies the use of 25 dps....

Originally Posted by Glenn Kennedy

Having spent years in analytical chemistry, wrestling with Measurement Uncertainty, I do struggle to envisage anything that justifies the use of 25 dps....

An excellent point.