How do I Calculate PI in an Excel formula?

On Sun, 11 Jan 2009 11:34:01 -0800, jollydottie
wrote:

on Excell
=E15+14.86*D15*PI()*F15*0.85 =139.0444
on my calc
=20 + 14.86 = 34.86 x 6 = 209.16 x PI = 14.462 x .500 = 7.23 x .85
= 6.1465- this is the right answer

So what am I doing wrong?

1. If by PI you mean the ratio of the circumference to the diameter of a
circle, then your calculated result is incorrect as the value of PI is a bit
more than 3 and no way can 209.16 x PI be less than 627. Your calculator seems
to be giving you a result of 14.462, if I understand what you have written
above.

2. In addition to that, you are probably not understanding the order in which
Excel performs operations in formulas, which is documented in HELP.

You can use parentheses to control the calculation order, so your Excel formula
might read:

=(E15+14.86)*D15*PI()*F15*0.85

But you still have your calculator doing:

209.16 * PI -- 14.462 which, since PI = 3.14159..., is incorrect.
--ron

ATAN(1), not ATAN(4), I think?
But yes, it'll work to 15 sig figs, not just 10.
--
David Biddulph

Bernard Liengme wrote:
Why only 10 decimal places?
Mathematically Atan(4) = pi()/4,
Then 4*ATAN(4) and PI() must give exactly the same answer within the
precision of Excel (15 decimals)
best wishes

"Rick Rothstein" wrote in
message ...
If you have trouble remembering or finding the constant value, but
have a good memory for simple formulas, this will generate PI to 10
significant figures (usually more than enough for any calculation
involving it)... =4*ATAN(1)

--
Rick (MVP - Excel)


"jollydottie" wrote in
message ...
Using the calculator I get a different answer, if PI = 3.162. then
*3.162 in
Excell should give me the right answer but it doesn't.

Hi,

we've exhausted this topic area so I thought I would throw in -

PI has been calculated to 2 billion digits, probably more by now, but Excel
is not prepared for more than 15.

Also, although this is not the core problem, computers work in binary, we
work in decimals - which leads to approximations.

Here is everything you need to know about this issue (and more):

http://support.microsoft.com/kb/78113/en-us
http://support.microsoft.com/kb/42980
http://support.microsoft.com/kb/214118
http://www.cpearson.com/excel/rounding.htm
http://docs.sun.com/source/806-3568/ncg_goldberg.html
--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"jollydottie" wrote:

Using the calculator I get a different answer, if PI = 3.162. then *3.162 in
Excell should give me the right answer but it doesn't.

Thank you all, I have found my error.

"Ron Rosenfeld" wrote:

On Sun, 11 Jan 2009 11:34:01 -0800, jollydottie
wrote:

on Excell
=E15+14.86*D15*PI()*F15*0.85 =139.0444
on my calc
=20 + 14.86 = 34.86 x 6 = 209.16 x PI = 14.462 x .500 = 7.23 x .85
= 6.1465- this is the right answer

So what am I doing wrong?

1. If by PI you mean the ratio of the circumference to the diameter of a
circle, then your calculated result is incorrect as the value of PI is a bit
more than 3 and no way can 209.16 x PI be less than 627. Your calculator seems
to be giving you a result of 14.462, if I understand what you have written
above.

2. In addition to that, you are probably not understanding the order in which
Excel performs operations in formulas, which is documented in HELP.

You can use parentheses to control the calculation order, so your Excel formula
might read:

=(E15+14.86)*D15*PI()*F15*0.85

But you still have your calculator doing:

209.16 * PI -- 14.462 which, since PI = 3.14159..., is incorrect.
--ron

While Excel will only display 15 digits, its value for pi is correct to
almost 17 digits. To 17 digits, Excel's value for pi is
3.1415926535897931
compared to the actual 17 digit approximation to pi of
3.1415926535897932

Jerry

"Shane Devenshire" wrote:

....
PI has been calculated to 2 billion digits, probably more by now, but Excel
is not prepared for more than 15.
....

No, I think ATAN(1) = pi()/4 so 4*ATAN(1) = pi
Please check on worksheet and let me know if I am wrong - it has happened
before!
Happy New Year
--
Bernard
remove caps from email

"David Biddulph" groups [at] biddulph.org.uk wrote in message
...
ATAN(1), not ATAN(4), I think?
But yes, it'll work to 15 sig figs, not just 10.
--
David Biddulph

Bernard Liengme wrote:
Why only 10 decimal places?
Mathematically Atan(4) = pi()/4,
Then 4*ATAN(4) and PI() must give exactly the same answer within the
precision of Excel (15 decimals)
best wishes

"Rick Rothstein" wrote in
message ...
If you have trouble remembering or finding the constant value, but
have a good memory for simple formulas, this will generate PI to 10
significant figures (usually more than enough for any calculation
involving it)... =4*ATAN(1)

--
Rick (MVP - Excel)


"jollydottie" wrote in
message ...
Using the calculator I get a different answer, if PI = 3.162. then
*3.162 in
Excell should give me the right answer but it doesn't.

You are correct that ATAN(1) = pi()/4 and that 4*ATAN(1) = pi
But in your previous message you had said Atan(4) = pi()/4 and 4*ATAN(4)
It has obviously been a long year, Bernard. :-)
--
David Biddulph

Bernard Liengme wrote:
No, I think ATAN(1) = pi()/4 so 4*ATAN(1) = pi
Please check on worksheet and let me know if I am wrong - it has
happened before!
Happy New Year

"David Biddulph" groups [at] biddulph.org.uk wrote in message
...
ATAN(1), not ATAN(4), I think?
But yes, it'll work to 15 sig figs, not just 10.
--
David Biddulph

Bernard Liengme wrote:
Why only 10 decimal places?
Mathematically Atan(4) = pi()/4,
Then 4*ATAN(4) and PI() must give exactly the same answer within the
precision of Excel (15 decimals)
best wishes

"Rick Rothstein" wrote in
message ...
If you have trouble remembering or finding the constant value, but
have a good memory for simple formulas, this will generate PI to 10
significant figures (usually more than enough for any calculation
involving it)... =4*ATAN(1)

--
Rick (MVP - Excel)


"jollydottie" wrote in
message ...
Using the calculator I get a different answer, if PI = 3.162. then
*3.162 in
Excell should give me the right answer but it doesn't.

On Jan 11, 1:22*pm, Shane Devenshire
wrote:
PI has been calculated to 2 billion digits, probably more by now,
but Excel is not prepared for more than 15. *
Also, although this is not the core problem, computers work in
binary, we work in decimals - which leads to approximations.

In Excel 2003, the binary representation of PI() is exactly
3.14159265358979,311599796346854418516159057617187 5. According to
online sources, the value of pi calculated to that many decimal places
(plus 2) is 3.14159265358979,323846264338327950288419716939937 510.
(The comma marks 15 significant digits to the left.) I'm too lazy to
compute the percent error :-).

On Jan 11, 12:02*pm, "Bernard Liengme"
wrote:
Then 4*ATAN(4) and PI() must give exactly the same answer
within the precision of Excel (15 decimals)

Yes. Both 4*ATAN(1) and PI() are represented in binary exactly as
3.14159265358979,311599796346854418516159057617187 5. (The comma marks
15 significant digits to the left.) At least, that is the case for
Excel 2003.

Want to inform us, seeing as we took the time to try and help?

--
__________________________________
HTH

Bob

"jollydottie" wrote in message
...
Thank you all, I have found my error.

"Ron Rosenfeld" wrote:

On Sun, 11 Jan 2009 11:34:01 -0800, jollydottie
wrote:

on Excell
=E15+14.86*D15*PI()*F15*0.85 =139.0444
on my calc
=20 + 14.86 = 34.86 x 6 = 209.16 x PI = 14.462 x .500 = 7.23 x
.85
= 6.1465- this is the right answer

So what am I doing wrong?

1. If by PI you mean the ratio of the circumference to the diameter of a
circle, then your calculated result is incorrect as the value of PI is a
bit
more than 3 and no way can 209.16 x PI be less than 627. Your calculator
seems
to be giving you a result of 14.462, if I understand what you have
written
above.

2. In addition to that, you are probably not understanding the order in
which
Excel performs operations in formulas, which is documented in HELP.

You can use parentheses to control the calculation order, so your Excel
formula
might read:

=(E15+14.86)*D15*PI()*F15*0.85

But you still have your calculator doing:

209.16 * PI -- 14.462 which, since PI = 3.14159..., is incorrect.
--ron