Account Options

  1. Sign in
Google Groups Home
« Groups Home
microsoft.public.excel.programming
+ new post
About this group
Subscribe to this group
This is a Usenet group - learn more
View this group in the new Google Groups
Message from discussion How many decimal places can a cell display?
The group you are posting to is a Usenet group. Messages posted to this group will make your email address visible to anyone on the Internet.
Your reply message has not been sent.
Your post was successful
 
From:
To:
Cc:
Followup To:
Add Cc | Add Followup-to | Edit Subject
Subject:
Validation:
For verification purposes please type the characters you see in the picture below or the numbers you hear by clicking the accessibility icon. Listen and type the numbers you hear
 
Jerry W. Lewis  
View profile  
 More options Apr 2 2006, 6:55 pm
Newsgroups: microsoft.public.excel.programming, microsoft.public.excel.worksheet.functions, microsoft.public.excel
From: Jerry W. Lewis <post_a_reply@no_e-mail.com>
Date: Sun, 2 Apr 2006 15:55:02 -0700
Local: Sun, Apr 2 2006 6:55 pm
Subject: RE: How many decimal places can a cell display?
Print | View thread | Show original | Report this message | Find messages by this author
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/b106871cf92...

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


 
You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
Create a group - Google Groups - Google Home - Terms of Service - Privacy Policy
©2012 Google