How many decimal places can a cell display?

[Spaz]

How many decimal places can be displayed in a cell? I'm running a brute
force VBA procedure of finding fractions that will approximate pi to as many
decimal places as Excel will display, but I don't know how many decimal
places Excel will display accurately. Anybody know? I guess this is also a
matter of how many decimal places VBA will calculate accurately as well.

Sub PiFractions()
Dim dividend As Integer, divisor As Integer, quotient As Double
Dim rowpointer As Byte

rowpointer = 1

For dividend = 22 To 10000
For divisor = 7 To dividend \ 3
quotient = dividend / divisor
If quotient > 3.14159 And quotient < 3.1416 Then
Cells(rowpointer, 1) = dividend
Cells(rowpointer, 2) = divisor
Cells(rowpointer, 3) = quotient
rowpointer = rowpointer + 1
End If
Next
Next

End Sub

 

[Pete_UK]

You can only get 15 digit precision in Excel.

Pete

 

[Spaz]

Thanks. I'm getting 14 now with this little procedure.

 

[daddylonglegs]

You get 15 digits in all, as Pete says, which means, in the case of Pi
that you get up to 14 decimal places.

If you put this formula in a cell

=PI() and format to, say, 20 decimal places you get

3.1415926535897900000

 

[Dana DeLouis]

... VBA procedure of finding fractions that will approximate pi to as many

decimal places as Excel will display,

Hi. At 15 digits, I believe the minimum fraction for Pi is:

=80143857/25510582

As a side note, the limit in vba is:
Num = 428224593349304#
Den = 136308121570117#

Debug.Print CDec(Num) / Den
' 3.1415926535897932384626433833

 

[Tom Ogilvy]

Here is one that shows 15 digits

? application.pi()
3.14159265358979

or in the worksheet
=pi()

and format the cell to show 14 decimals.

 

[Jerry W. Lewis]

Excel's numeric display limit is on significant figures, not decimal places.
Excel (like almost all software) follows the IEEE standard for double
precision binary representation of numbers.
http://www.cpearson.com/excel/rounding.htm
In particular, all 15 digit and most 16 digit integers can be exactly
represented. But rather than explain why some 16 digit numbers unavoidably
change value from what you enter, MS chose to display only 15 digits (See
Help for "specifications").
and It requires 17 decimal digits to uniquely specify a double precision
binary number, and An exact conversion from binary to decimal of a floating
point number may require many more than 17 decimal digits
http://groups.google.com/group/microsoft.public.excel/msg/b106871cf92f8465

If you want to write a routine that will handle more precision than Excel
natively gives, you might find the VBA code at that last link instructive.
There are some Excel add-ins like
http://digilander.libero.it/foxes/index.htm
http://precisioncalc.com/
that already implement higher precision.

Also there are commercial packages like Maple, Mathematica,
MacSyma and open source packages like Maxima
http://maxima.sourceforge.net/
that implement algebraic math and user-specified numeric precision.

Jerry

 

[Dana DeLouis]

If you want to do a program loop, this is one of a few ways to get a jump
start...

Sub Demo()
Dim s As String
s = WorksheetFunction.Rept("?", 16)
s = s & "/" & s

Range("A1").FormulaR1C1 = "=PI()"
Range("A1").NumberFormat = s
Debug.Print Range("A1").Text
End Sub

5419351/1725033

As you can see, the fraction format can get close(~14), but not quite...:>(

 

[Spaz]

Wow, that's some crazy code. Thanks!

If you want to do a program loop, this is one of a few ways to get a jump
start...

Sub Demo()
Dim s As String
s = WorksheetFunction.Rept("?", 16)
s = s & "/" & s

Range("A1").FormulaR1C1 = "=PI()"
Range("A1").NumberFormat = s
Debug.Print Range("A1").Text
End Sub

5419351/1725033

As you can see, the fraction format can get close(~14), but not
quite...:>(

 

[Tom Ogilvy]

Just for interest, in xl97 it returned:

355/113

 

[Jerry W. Lewis]

While better than xl97 (as Tom showed), formatting as a fraction is still not
entirely reliable when you request many digits. The DP (IEEE double
precision) approximation to Pi is exactly
884279719003555/281474976710656
which has a 15 digit denominator. However, you get the same value as the DP
approximation to
245850922/78256779
which only has an 8 digit denominator.

Jerry