I have recently been doing some work on a particular type of prime in Excel
when I ran into a problem with the worksheet POWER function. It became
obvious that at least on my machine there appears to be a rounding problem
with some of the calculations. I have a couple of machines and repeated it on
a second machine with Office 2003, with the same result. (The first machine
had an earlier version of Excel.) The simplest form of the calculation is
demonstrated by performing the following in a cell €˜=Power(100001,3) the
result seems to suggest that 1 x 1 = 0, The last digit being zero and not
one. I repeated the calculation with the more straightforward
€˜=sum(100001*100001*100001) with the same disconcerting result. (You can
check these calculations with the desktop calculator.) Do I need a fix or is
there some explanation for these results. I have found similar errors with
more demanding calculations and the work I am doing is grinding to a halt. I
am in the curious situation that most of my work I am now doing in Excel is
done with me doing the calculations manually to ensure the result is correct!
Yet in the documentation it is suggested that Excel can perform calculations
of the order of 1 * 10 to the power 307. (Note that all the calculations
are performed with and displayed as numbers, I do not represent anything as
floating point because it is the numbers and their properties that I am
interested in.) Can somebody help!

Excel's precision in decimals is 15 digits

See

http://support.microsoft.com/kb/78113

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"John Watt" wrote in message ...
|I have recently been doing some work on a particular type of prime in Excel
| when I ran into a problem with the worksheet POWER function. It became
| obvious that at least on my machine there appears to be a rounding problem
| with some of the calculations. I have a couple of machines and repeated it on
| a second machine with Office 2003, with the same result. (The first machine
| had an earlier version of Excel.) The simplest form of the calculation is
| demonstrated by performing the following in a cell '=Power(100001,3)' the
| result seems to suggest that 1 x 1 = 0, The last digit being zero and not
| one. I repeated the calculation with the more straightforward
| '=sum(100001*100001*100001)' with the same disconcerting result. (You can
| check these calculations with the desktop calculator.) Do I need a fix or is
| there some explanation for these results. I have found similar errors with
| more demanding calculations and the work I am doing is grinding to a halt. I
| am in the curious situation that most of my work I am now doing in Excel is
| done with me doing the calculations manually to ensure the result is correct!
| Yet in the documentation it is suggested that Excel can perform calculations
| of the order of 1 * 10 to the power 307. (Note that all the calculations
| are performed with and displayed as numbers, I do not represent anything as
| floating point because it is the numbers and their properties that I am
| interested in.) Can somebody help!

Excel is precise to 15 digits.

If you're looking for a math program Excel is not the correct piece of
software to use. Use something like mathematica. See this for more info:
http://www.cpearson.com/excel/rounding.htm

Dave

Dave
--
Brevity is the soul of wit.


"John Watt" wrote:

I have recently been doing some work on a particular type of prime in Excel
when I ran into a problem with the worksheet POWER function. It became
obvious that at least on my machine there appears to be a rounding problem
with some of the calculations. I have a couple of machines and repeated it on
a second machine with Office 2003, with the same result. (The first machine
had an earlier version of Excel.) The simplest form of the calculation is
demonstrated by performing the following in a cell €˜=Power(100001,3) the
result seems to suggest that 1 x 1 = 0, The last digit being zero and not
one. I repeated the calculation with the more straightforward
€˜=sum(100001*100001*100001) with the same disconcerting result. (You can
check these calculations with the desktop calculator.) Do I need a fix or is
there some explanation for these results. I have found similar errors with
more demanding calculations and the work I am doing is grinding to a halt. I
am in the curious situation that most of my work I am now doing in Excel is
done with me doing the calculations manually to ensure the result is correct!
Yet in the documentation it is suggested that Excel can perform calculations
of the order of 1 * 10 to the power 307. (Note that all the calculations
are performed with and displayed as numbers, I do not represent anything as
floating point because it is the numbers and their properties that I am
interested in.) Can somebody help!

Excel's display is limited to 15 digits, but its precision (like almost all
general purpose software) is limited to IEEE double precision, which can
accurately represent most 16 digit integers.
=POWER(100001,3)-POWER(100000,3)
shows that Excel calculated =POWER(100001,3) exactly, even though it would
not display the 16th significant figure.

Jerry

"Niek Otten" wrote:

Excel's precision in decimals is 15 digits

See

http://support.microsoft.com/kb/78113

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"John Watt" wrote in message ...
|I have recently been doing some work on a particular type of prime in Excel
| when I ran into a problem with the worksheet POWER function. It became
| obvious that at least on my machine there appears to be a rounding problem
| with some of the calculations. I have a couple of machines and repeated it on
| a second machine with Office 2003, with the same result. (The first machine
| had an earlier version of Excel.) The simplest form of the calculation is
| demonstrated by performing the following in a cell '=Power(100001,3)' the
| result seems to suggest that 1 x 1 = 0, The last digit being zero and not
| one. I repeated the calculation with the more straightforward
| '=sum(100001*100001*100001)' with the same disconcerting result. (You can
| check these calculations with the desktop calculator.) Do I need a fix or is
| there some explanation for these results. I have found similar errors with
| more demanding calculations and the work I am doing is grinding to a halt. I
| am in the curious situation that most of my work I am now doing in Excel is
| done with me doing the calculations manually to ensure the result is correct!
| Yet in the documentation it is suggested that Excel can perform calculations
| of the order of 1 * 10 to the power 307. (Note that all the calculations
| are performed with and displayed as numbers, I do not represent anything as
| floating point because it is the numbers and their properties that I am
| interested in.) Can somebody help!

Many thanks Guys.

"Dave F" wrote:

Excel is precise to 15 digits.

If you're looking for a math program Excel is not the correct piece of
software to use. Use something like mathematica. See this for more info:
http://www.cpearson.com/excel/rounding.htm

Dave

Dave
--
Brevity is the soul of wit.


"John Watt" wrote:

I have recently been doing some work on a particular type of prime in Excel
when I ran into a problem with the worksheet POWER function. It became
obvious that at least on my machine there appears to be a rounding problem
with some of the calculations. I have a couple of machines and repeated it on
a second machine with Office 2003, with the same result. (The first machine
had an earlier version of Excel.) The simplest form of the calculation is
demonstrated by performing the following in a cell €˜=Power(100001,3) the
result seems to suggest that 1 x 1 = 0, The last digit being zero and not
one. I repeated the calculation with the more straightforward
€˜=sum(100001*100001*100001) with the same disconcerting result. (You can
check these calculations with the desktop calculator.) Do I need a fix or is
there some explanation for these results. I have found similar errors with
more demanding calculations and the work I am doing is grinding to a halt. I
am in the curious situation that most of my work I am now doing in Excel is
done with me doing the calculations manually to ensure the result is correct!
Yet in the documentation it is suggested that Excel can perform calculations
of the order of 1 * 10 to the power 307. (Note that all the calculations
are performed with and displayed as numbers, I do not represent anything as
floating point because it is the numbers and their properties that I am
interested in.) Can somebody help!

..the work I am doing is grinding to a halt.

Hi. Others gave you the correct reason.
However, I do a lot of higher precision calculations in vba.
For a possible workaround still using Excel, here's a quick demo if you are
interested.

Sub Demo()
Dim Ans, n
n = CDec(100001)

Ans = n * n * n
Debug.Print Ans
End Sub

1000030000300001

I have recently been doing some work on a particular type of prime in Excel

Note that this number is not prime.
PrimeQ[100001]
False

--
HTH :)
Dana DeLouis
Windows XP & Office 2003


"John Watt" wrote in message
...
I have recently been doing some work on a particular type of prime in Excel
when I ran into a problem with the worksheet POWER function. It became
obvious that at least on my machine there appears to be a rounding problem
with some of the calculations. I have a couple of machines and repeated it
on
a second machine with Office 2003, with the same result. (The first
machine
had an earlier version of Excel.) The simplest form of the calculation is
demonstrated by performing the following in a cell '=Power(100001,3)' the
result seems to suggest that 1 x 1 = 0, The last digit being zero and not
one. I repeated the calculation with the more straightforward
'=sum(100001*100001*100001)' with the same disconcerting result. (You can
check these calculations with the desktop calculator.) Do I need a fix or
is
there some explanation for these results. I have found similar errors with
more demanding calculations and the work I am doing is grinding to a halt.
I
am in the curious situation that most of my work I am now doing in Excel
is
done with me doing the calculations manually to ensure the result is
correct!
Yet in the documentation it is suggested that Excel can perform
calculations
of the order of 1 * 10 to the power 307. (Note that all the calculations
are performed with and displayed as numbers, I do not represent anything
as
floating point because it is the numbers and their properties that I am
interested in.) Can somebody help!

Just for the record 3.141256789 is a real number
56789 is an integer number
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"John Watt" wrote in message
...
I have recently been doing some work on a particular type of prime in Excel
when I ran into a problem with the worksheet POWER function. It became
obvious that at least on my machine there appears to be a rounding problem
with some of the calculations. I have a couple of machines and repeated it
on
a second machine with Office 2003, with the same result. (The first
machine
had an earlier version of Excel.) The simplest form of the calculation is
demonstrated by performing the following in a cell '=Power(100001,3)' the
result seems to suggest that 1 x 1 = 0, The last digit being zero and not
one. I repeated the calculation with the more straightforward
'=sum(100001*100001*100001)' with the same disconcerting result. (You can
check these calculations with the desktop calculator.) Do I need a fix or
is
there some explanation for these results. I have found similar errors with
more demanding calculations and the work I am doing is grinding to a halt.
I
am in the curious situation that most of my work I am now doing in Excel
is
done with me doing the calculations manually to ensure the result is
correct!
Yet in the documentation it is suggested that Excel can perform
calculations
of the order of 1 * 10 to the power 307. (Note that all the calculations
are performed with and displayed as numbers, I do not represent anything
as
floating point because it is the numbers and their properties that I am
interested in.) Can somebody help!