Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I Calculate PI in an Excel formula?
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I Calculate PI in an Excel formula?
The value of Pi approximates 3.142 not 3.162,
A more exact value is 3.141592653589793238462643383279502884197169399375 10582097494459230781640628620899862803482534211706 79 To see more digits visit http://www.eveandersson.com/pi/digits/1000000 Excel gives Pi to 15 decimal places using =PI() -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I Calculate PI in an Excel formula?
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. -- Gary''s Student - gsnu200825 "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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I Calculate PI in an Excel formula?
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I Calculate PI in an Excel formula?
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? "Gary''s Student" wrote: 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. -- Gary''s Student - gsnu200825 "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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I Calculate PI in an Excel formula?
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 -- __________________________________ HTH Bob "jollydottie" wrote in message ... 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? "Gary''s Student" wrote: 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. -- Gary''s Student - gsnu200825 "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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I Calculate PI in an Excel formula?
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. -- Rick (MVP - Excel) "jollydottie" wrote in message ... 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? "Gary''s Student" wrote: 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. -- Gary''s Student - gsnu200825 "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. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I Calculate PI in an Excel formula?
You've forgotten the precedence of arithmetic operations. If you want
something other than the standard precedence, you need to include parentheses to specify the precedence. =E15+14.86*D15*PI()*F15*0.85 is not the same as =(E15+14.86)*D15*PI()*F15*0.85 Type the word precedence into Excel help. -- David Biddulph 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? "Gary''s Student" wrote: 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. -- Gary''s Student - gsnu200825 "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. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I Calculate PI in an Excel formula?
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 -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "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. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I Calculate PI in an Excel formula?
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 -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "jollydottie" wrote in message ... 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? "Gary''s Student" wrote: 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. -- Gary''s Student - gsnu200825 "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. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I Calculate PI in an Excel formula?
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. |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I Calculate PI in an Excel formula?
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. |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I Calculate PI in an Excel formula?
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 |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I Calculate PI in an Excel formula?
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. .... |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I Calculate PI in an Excel formula?
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. |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I Calculate PI in an Excel formula?
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. |
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I Calculate PI in an Excel formula?
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 :-). |
#19
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I Calculate PI in an Excel formula?
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. |
#20
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I Calculate PI in an Excel formula?
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 |
#21
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I Calculate PI in an Excel formula?
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 Jerry W. Lewis wrote: 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. .... |
#22
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I Calculate PI in an Excel formula?
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 "Dana DeLouis" wrote: 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 |
#23
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I Calculate PI in an Excel formula?
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. Thanks Jerry for the links. Always an interesting subject. :) (Side note...here's a test for larger x values) In [A1]... then copied down: =EXP(GAMMALN(ROW())) - FACT(ROW()-1) <snip |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 07 - Formula won't calculate - please help | Excel Discussion (Misc queries) | |||
In Excel formula to calculate the date someone will hit 65 | New Users to Excel | |||
Is there a formula in Excel to calculate a loan amount? | Excel Worksheet Functions | |||
how do i calculate time in & out in excel using a formula | Excel Worksheet Functions | |||
Suddenly Excel can't calculate formula!!! | Excel Worksheet Functions |