Order of Operations problem in Excel

  • Thread starter Thread starter Crash Test Dummy
  • Start date Start date
C

Crash Test Dummy

I am using Excel (Office 97).

I am having problems with order of operations. Here are some examples that
are self-explanatory. Each of the examples represent formulas in cells and
the result is listed after the = sign.

34.75/90*3.141593=1.213003964
(34.75/90*3.141593)=1.213003964

COS(34.75/90*3.141593)=0.350207256 This one is wrong if the calculation
is performed in the () in order from left to right.

COS(34.75/(90*3.141593))=0.992456932 Here you can see the only way to get
the same result as other programs or using any calculator (even the Windows
calculator in Accessories) is by adding an extra set of ()'s.

I am concerned about all of the formulas in Excel that users believe the
order of operations is being correctly performed.

Please comment if you feel I am in error or this is a problem (documented
or not).
 
XL is performing the calculations in correct order according to its
specifications.

See "The order in which Microsoft Excel performs operations in formulas"
in XL Help.

FWIW: you may want to substitute the built-in function PI() for 3.141593.
 
Here is the exact wording of the Excel help you mentioned:

The order in which Microsoft Excel performs operations in formulas: If you
combine several operators in a single formula, Microsoft Excel performs the
operations in the order shown in the following table. If a formula contains
operators with the same precedence - for example, if a formula contains both
a multiplication and division operator - Microsoft Excel evaluates the operators
from left to right. To change the order of evaluation, enclose the part of
the formula to be calculated first in parentheses.

OK, to state again my point, the original equation is cos(34.75/90*3.141593).
I do not want to change the order of operation inside of the () so it should
calculate left to right inside of the () then calculate the cos of that number.

34.75/90=0.3861111
0.3861111*3.141593=1.213004
cos(1.213004)=0.9997759

When the original equation is placed in an excel cell, it produces the answer
0.350207256. If excel calculated from left to right inside of the parenthesis,
then took the cosine of that number, the answer should be 0.9997759.

Thanks for your input. Please try this same formula using a calculator or
some other piece of software. I am using Agilent VEE 6.1 and it agrees with
my handheld calculator and the calculator under Accessories in the Start
menu with the answer 0.9997759.
 
The problem is not with the order of operations. The "error" is
that Excel does all its trig operations in Radians, not Degrees.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



Crash Test Dummy said:
Here is the exact wording of the Excel help you mentioned:

The order in which Microsoft Excel performs operations in formulas: If you
combine several operators in a single formula, Microsoft Excel performs the
operations in the order shown in the following table. If a formula contains
operators with the same precedence - for example, if a formula contains both
a multiplication and division operator - Microsoft Excel evaluates the operators
from left to right. To change the order of evaluation, enclose the part of
the formula to be calculated first in parentheses.

OK, to state again my point, the original equation is cos(34.75/90*3.141593).
I do not want to change the order of operation inside of the () so it should
calculate left to right inside of the () then calculate the cos of that number.

34.75/90=0.3861111
0.3861111*3.141593=1.213004
cos(1.213004)=0.9997759

When the original equation is placed in an excel cell, it produces the answer
0.350207256. If excel calculated from left to right inside of the parenthesis,
then took the cosine of that number, the answer should be 0.9997759.

Thanks for your input. Please try this same formula using a calculator or
some other piece of software. I am using Agilent VEE 6.1 and it agrees with
my handheld calculator and the calculator under Accessories in the Start
menu with the answer 0.9997759.

formulas" examples formulas in cells
and only way to
get



----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World!
100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via
Encryption =---
 
I should have added that if you want to use degrees in trig
functions, use the RADIANS function to convert degree values to
radians. E.g.,

=COS(RADIANS(34.75/90*3.141593))

which returns the result you expected, 0.99976.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Hmm.. I don't have XL97, but I don't remember this being a problem. And
I certainly don't need a calculator or other software to know that the
cosine of 1.2 radians can't be anything near 1.

My tests in XLv.X conform exactly to the passage you cited:


A1: =34.75/90*3.141593 ==> 1.213003964

A2: =34.75/90 ==> 0.386111111
A3: =A2*3.141593 ==> 1.213003964
A4: =A1=A3 ==> TRUE

A5: =COS(34.75/90*3.141593) ==> 0.350207256
A6: =COS(1.213004) ==> 0.350207222
A7: =ROUND(A6-A5,6)=0 ==> TRUE

Are you really getting the incorrect

=COS(1.213004) ==> 0.9997759

???
 
Try same in a handheld calculator or software calculator under Start-Accessories-Calculator
(scientific mode)
 
Ah...

You're working in degrees. XL's trig functions only work in radians.

Try

=COS(RADIANS(1.213004))

if you're looking for a result of 0.999775905.
 
I misposted; the Excel function is COS not cosine. See from the Excel
help for COS:

COS
See Also

Returns the cosine of the given angle.

Syntax

COS(number)

Number is the angle in radians for which you want the cosine. If the
angle is in degrees, multiply it by PI()/180 to convert it to radians.

Examples

COS(1.047) equals 0.500171

COS(60*PI()/180) equals 0.5, the cosine of 60 degrees

Alan Beban
 
Okay, I see. The problem is that I am expecting degrees and Excel is generating
the answer in radians. It is interesting that in Excel, if you select the
function button, pick math & trig, then single click the cos function, the
description simply indicates that the function returns the cosine of (x).
If you double click the function, the description says that it ruturns the
cosine in radians. Big oversight on my part. I apologize for the error. Thanks
everyone for replying.
 
Crash,

Not exactly. The answer isn't in radians -- a cosine is just a number. But
Excel expects the argument to the Cos function be given in radians, and
you're giving it degrees, presumably. That kind of stuff makes landers
crash into Mars, unfortunately. ): That isn't why they call you Crash, is
it?

Also, you might want to use PI() in your formulas, instead of
3.14159265358979, or define a name, like pi or p. Insert - Name - Define.
In the "refers to" box, put =PI(). Then you can use pi or p in your
formulas. I'll go to a lot of trouble so I can be lazy! :)
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

Crash Test Dummy said:
Okay, I see. The problem is that I am expecting degrees and Excel is generating
the answer in radians. It is interesting that in Excel, if you select the
function button, pick math & trig, then single click the cos function, the
description simply indicates that the function returns the cosine of (x).
If you double click the function, the description says that it ruturns the
cosine in radians. Big oversight on my part. I apologize for the error. Thanks
everyone for replying.





----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption
=---
 
Also, you might want to use PI() in your formulas, instead of
3.14159265358979, or define a name, like pi or p. Insert - Name - Define.
In the "refers to" box, put =PI(). Then you can use pi or p in your
formulas.

Mmmmmm... Pie...
 
Back
Top