[SOLVED] 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.

"Pete_UK" <[email protected]> wrote in message
news:[email protected]...
> You can only get 15 digit precision in Excel.
>
> Pete
>

[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.14159265358979000000

[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

--
HTH. :>)
Dana DeLouis
Windows XP, Office 2003


"Spaz" <[email protected]> wrote in message
news:[email protected]...
> 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
>

[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.

--
Regards,
Tom Ogilvy

"Spaz" <[email protected]> wrote in message
news:[email protected]...
> Thanks. I'm getting 14 now with this little procedure.
>
> "Pete_UK" <[email protected]> wrote in message
> news:[email protected]...
> > You can only get 15 digit precision in Excel.
> >
> > Pete
> >
>
>

[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/micro...06871cf92f8465

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

"Spaz" wrote:

> 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
>
>
>

[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...:>(
--
HTH. :>)
Dana DeLouis
Windows XP, Office 2003


"Dana DeLouis" <[email protected]> wrote in message
news:[email protected]...
> >... 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
>
> --
> HTH. :>)
> Dana DeLouis
> Windows XP, Office 2003
>
>
> "Spaz" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>>
>
>

[Spaz]

Wow, that's some crazy code. Thanks!

"Dana DeLouis" <[email protected]> wrote in message
news:[email protected]...
> 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...:>(
> --
> HTH. :>)
> Dana DeLouis
> Windows XP, Office 2003
>
>
> "Dana DeLouis" <[email protected]> wrote in message
> news:[email protected]...
>> >... 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
>>
>> --
>> HTH. :>)
>> Dana DeLouis
>> Windows XP, Office 2003
>>
>>
>> "Spaz" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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
>>>
>>
>>
>
>

[Tom Ogilvy]

Just for interest, in xl97 it returned:

355/113

--
Regards,
Tom Ogilvy


"Dana DeLouis" wrote:

> 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...:>(
> --
> HTH. :>)
> Dana DeLouis
> Windows XP, Office 2003
>
>
> "Dana DeLouis" <[email protected]> wrote in message
> news:[email protected]...
> > >... 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
> >
> > --
> > HTH. :>)
> > Dana DeLouis
> > Windows XP, Office 2003
> >
> >
> > "Spaz" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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
> >>
> >
> >
>
>
>

[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

"Dana DeLouis" wrote:

> 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...:>(
> --
> HTH. :>)
> Dana DeLouis
> Windows XP, Office 2003
>
>
> "Dana DeLouis" <[email protected]> wrote in message
> news:[email protected]...
> > >... 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
> >
> > --
> > HTH. :>)
> > Dana DeLouis
> > Windows XP, Office 2003
> >
> >
> > "Spaz" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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
> >>
> >
> >
>
>
>