How do I Calculate PI in an Excel formula?

  • Thread starter Thread starter jollydottie
  • Start date Start date
J

jollydottie

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.
 
In an empty cell, enter:
=PI() and format the cell to display 15 places to see:

3.141592653589790

This is the approximation Excel uses for pi.
 
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)
 
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?
 
First, enclose the first two parts in parentheses

(E15+14.86)*...

Second, how do you multiply 209.16 by 3.142 and get 14.462?

209.16 x PI = 14.462
 
When you put your values in your calculator, you are doing your operation
cumulatively, two values at a time... that is not how Excel does its
calculations. Its operators (+,-,*,/,etc) have a precedence to them and you
must use parentheses to change them. Truthfully, I'm thinking your
calculator calculation may be wrong, but there is no way to tell from your
posting. IF the order you are performing your calculation on your calculator
is correct (note the emphasized word IF), then this is how you would have to
put the expression into Excel...

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

Note... There is something wrong with the calculation you showed us. You
have this as an intermediate step...

209.16 x PI = 14.462

But PI is more than 3, so multiplying those values can't give you a smaller
value the 209.16. Dividing instead of multiplying won't get the value you
show either, so I'm not sure what you actually meant to write.
 
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
 
You MUST remember that Excel computes formula using a certain operator
order:
* and / (multiplication & division) happen before + and - (addition &
subtraction)

Lets look at =E15+14.86*D15*PI()*F15*0.85 (It seems that E15=20,D15=6 and
F15=0.5)
This becomes 20+14.86*5*3.142*0.5*0.85
Doing the multiplication first we get 20 + 119.04
Now do addition to get 139.04

It seems that you want to add 20 to 14.86 and then do the multiplications;
so use
=(E15+14.86)*D15*PI()*F15*0.85


You wrote
=20 + 14.86 = 34.86 x 6 = 209.16 x PI = 14.462 x .500 = 7.23 x .85
=6.1465
See how you mistakenly got 209.16 but then 209.18*pi must the about 200*3 =
600 (actually 657.0955) but I must have misread your data.

Let's us know if this helps
 
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?
Click to expand...

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
 
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
 
Thank you all, I have found my error.

Ron Rosenfeld said:
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
Click to expand...
 
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

:

....
PI has been calculated to 2 billion digits, probably more by now, but Excel
is not prepared for more than 15.
Click to expand...
....
 
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
 
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.
Click to expand...

In Excel 2003, the binary representation of PI() is exactly
3.14159265358979,3115997963468544185161590576171875. According to
online sources, the value of pi calculated to that many decimal places
(plus 2) is 3.14159265358979,323846264338327950288419716939937510.
(The comma marks 15 significant digits to the left.) I'm too lazy to
compute the percent error :-).
 
Then 4*ATAN(4) and PI() must give exactly the same answer
within the precision of Excel (15 decimals)
Click to expand...

Yes. Both 4*ATAN(1) and PI() are represented in binary exactly as
3.14159265358979,3115997963468544185161590576171875. (The comma marks
15 significant digits to the left.) At least, that is the case for
Excel 2003.
 
Just for gee-wiz, here's an easy way to show how inaccurate GAMMALN is,
even at small values. This should zero out...

=EXP(GAMMALN(1/2))^2 - PI()

4.07633E-10

= = =
Dana DeLouis
 
MS rarely used more than one algorithm for any math function that was not
provided by the math coprocessor. Ln(Gamma(x)) is usually calculated by an
asymptotic expansion
6.1.41 in http://www.math.sfu.ca/~cbm/aands/page_257.htm
or its related continued fraction
6.1.48 in http://www.math.sfu.ca/~cbm/aands/page_258.htm
that converges slowly (if at all) for small x, so it should be no surprise
that its accuracy improves as x becomes large.

If you have a copy of Smith's VBA library of probability functions, you can
go through the source code and see the lengths he went to to avoid these
problems for small x.

Jerry
 
Back
Top