|
||||||||||||
|
||||||||||||
| WARNING! Microsoft Excel Error in Order of Operations... | |||
| Post Reply | Forum | ||
| Posted by: swearingen ® 06/13/2006, 16:21:36 Author Profile eMail author Edit |
I posted this elsewhere, but I wanted to put it here as well to catch as many Excel users as possible. The order of operations that Excel uses in its formulas is incorrect for numbers raised to a power with a negative sign in front. Examples: Excel: -5^2 = 25, but it should equal -25. What's funny is when you type it like this: -5^2 + 5^2 --> Excel outputs 50 when it should be 0. Common sense tells you that it should be the same as: 5^2 - 5^2 which obviously equals 0 and Excel agrees, here.
https://support.microsoft.com/kb/q132686/ My advice: use parentheses like it's going out of style! |
| Post Reply | Tell a Friend (must be logged in) | Alert Admin About Post | View All | | Next | |
| Replies to this message |
| Re: WARNING! Microsoft Excel Error in Order of Operations... |  | ||
| Re: WARNING! Microsoft Excel Error in Order of Operations... -- swearingen | Post Reply | Top of thread | Forum |
| Posted by: fast eddie ® 06/14/2006, 12:45:59 Author Profile eMail author Edit |
Last time I was in school negative x negative = positive! therefore, -5 X -5 (-5^2) =25!! |
| Post Reply | Tell a Friend (must be logged in) | Alert Admin About Post | Where am I? Original Top of thread | | | |
| Re: Re: WARNING! Microsoft Excel Error in Order of Operations... | |||
| Re: Re: WARNING! Microsoft Excel Error in Order of Operations... -- fast eddie | Post Reply | Top of thread | Forum |
| Posted by: swearingen ® 06/14/2006, 13:28:16 Author Profile eMail author Edit |
Last time I was in school, a negative x negative = positive as well. But a positive to the second power with a negative sign in front of it was also a negative... If what you say is true, then: -5^2 + 5^2 = 50 So what does 5^2 - 5^2 equal? Another question: If, as you say, -5^2 + 5^2 = 50, then do me a favor and bring the -5^2 to the other side of the equation. What are you going to do? Add 5^2 to both sides? Play with it and see... |
| Post Reply | Tell a Friend (must be logged in) | Alert Admin About Post | Where am I? Original Top of thread | | | |
| Re: Re: Re: WARNING! Microsoft Excel Error in Order of Operations... | |||
| Re: Re: Re: WARNING! Microsoft Excel Error in Order of Operations... -- swearingen | Post Reply | Top of thread | Forum |
| Posted by: dougtheslug ® 06/16/2006, 10:38:04 Author Profile eMail author Edit |
if you have 5^2-5^2=50 when you do order of operations you would place a braket around the negative 5^2+(-5^2) |
| Post Reply | Tell a Friend (must be logged in) | Alert Admin About Post | Where am I? Original Top of thread | | | |
| Re: Re: Re: Re: WARNING! Microsoft Excel Error in Order of Operations... | |||
| Re: Re: Re: Re: WARNING! Microsoft Excel Error in Order of Operations... -- dougtheslug | Post Reply | Top of thread | Forum |
| Posted by: swearingen ® 06/16/2006, 15:19:22 Author Profile eMail author Edit |
Brackets as you have them or no, 5^2 + (-5^2) or 5^2-5^2 = 0. What's funny is that if I asked you what -x^2 + x^2 equalled you would not have hesitated to answer 0. We're just not used to seeing numerals written "-5^2", we're more used to the abstract variables. For instance: -x^2 + 5 = -20 You would have quickly subtracted 5 from both sides, divided by -1 and then taken the square root. The answer is 5, and you'd be hard pressed to find anyone that would disagree. Now plug the 5 back in. Is it suddenly wrong? How about this logic: If -5^2 = 25 = 5^2,
You see what I mean? We're just more used to working with variables... |
| Post Reply | Tell a Friend (must be logged in) | Alert Admin About Post | Where am I? Original Top of thread |
| Re: Re: Re: WARNING! Microsoft Excel Error in Order of Operations... |  | ||
| Re: Re: Re: WARNING! Microsoft Excel Error in Order of Operations... -- swearingen | Post Reply | Top of thread | Forum |
| Posted by: fast eddie ® 06/14/2006, 16:23:17 Author Profile eMail author Edit |
The notation is the confusion. -5^2, read as negative 5 squared which is different than -(5^2) which is read as the opposite of 5^2. The latter yields a negative number as it is the the opposite of 5^2 or 25. The former is actually a negative number being squared resulting a positive. The parenthesis identify the contents inside as an individual integer. Remember PEMDAS? |
| Post Reply | Tell a Friend (must be logged in) | Alert Admin About Post | Where am I? Original Top of thread | | | |
| Re: Re: Re: Re: WARNING! Microsoft Excel Error in Order of Operations... | |||
| Re: Re: Re: Re: WARNING! Microsoft Excel Error in Order of Operations... -- fast eddie | Post Reply | Top of thread | Forum |
| Posted by: swearingen ® 06/14/2006, 16:30:44 Author Profile eMail author Edit |
It is actually no different: -5^2 = -(5^2) by the order of operations. I'm not familiar with PEMDAS, but I assume in this context that it stands for Parenthesis, Eponentiation, Multiplication, Division, Addition, Subtraction. So, going through it, we'd square the 5 first (Exponentiation) and then multiply by -1. That is, in effect, what's happening here. Did you try the example I gave at the end of my last post? Jot it down on a piece of paper and try it. Here's a related link: https://mathforum.org/library/drmath/sets/select/dm_order_op.html |
| Post Reply | Tell a Friend (must be logged in) | Alert Admin About Post | Where am I? Original Top of thread | | | |
| Re: Re: Re: Re: Re: WARNING! Microsoft Excel Error in Order of Operations... | |||
| Re: Re: Re: Re: Re: WARNING! Microsoft Excel Error in Order of Operations... -- swearingen | Post Reply | Top of thread | Forum |
| Posted by: fast eddie ® 06/14/2006, 16:44:59 Author Profile eMail author Edit |
Ok, -5^2+5^2=50 bringing the -5^2 across will be 5^2=50-(-5^2)=25.
-5^2 is not the same as -(5^2). The first one is actually -5 x -5 = 25 the second one is -1 x the integer 5^2 = -25. |
| Post Reply | Tell a Friend (must be logged in) | Alert Admin About Post | Where am I? Original Top of thread | | | |
| Re: Re: Re: Re: Re: Re: WARNING! Microsoft Excel Error in Order of Operations... | |||
| Re: Re: Re: Re: Re: Re: WARNING! Microsoft Excel Error in Order of Operations... -- fast eddie | Post Reply | Top of thread | Forum |
| Posted by: swearingen ® 06/14/2006, 17:35:33 Author Profile eMail author Edit |
It doesn't prove anything if you have to use parentheses. If I put a negative sign in front of parentheses around anything, it doesn't affect what's inside. This does not show how -5^2 = 25. Do you also hold that 5^2 - 5^2 = 50? Another data point: both MathCAD and Mathematica return -25 for -5^2. So does my HP 48 calculator. So does my buddy's TI-86. Standard algebraic calculators won't because by entering -5 and then squaring it is correctly squaring the entire thing (effectively (-5)^2). But typing -5^2 into an advanced scientific calculator or program will give you the correct answer: -25. Poke around and do some research. You'll see what I mean. |
| Post Reply | Tell a Friend (must be logged in) | Alert Admin About Post | Where am I? Original Top of thread | | | |
| Re: Re: Re: Re: Re: Re: Re: WARNING! Microsoft Excel Error in Order of Operations... | |||
| Re: Re: Re: Re: Re: Re: Re: WARNING! Microsoft Excel Error in Order of Operations... -- swearingen | Post Reply | Top of thread | Forum |
| Posted by: swearingen ® 06/14/2006, 18:03:27 Author Profile eMail author Edit |
One other example: We commonly write polynomials with leading negatives that are understood to be -1. Solve the following for x: -x^2 + 5 = -20 You get 5. Plug the 5 back in: -5^2 + 5 = -20 Let's try your example with X's: -x^2 + x^2 = 50 Find an engineer or mathematician that would tell you the answer is x = 5 and I'll show you one that needs his head examined... |
| Post Reply | Tell a Friend (must be logged in) | Alert Admin About Post | Where am I? Original Top of thread | | | |
| Re: Re: Re: Re: Re: Re: Re: Re: WARNING! Microsoft Excel Error in Order of Operations... | |||
| Re: Re: Re: Re: Re: Re: Re: Re: WARNING! Microsoft Excel Error in Order of Operations... -- swearingen | Post Reply | Top of thread | Forum |
| Posted by: fast eddie ® 06/15/2006, 12:50:29 Author Profile eMail author Edit |
OK! Normally I read -5 as a negative integer and not as -1 x 5.
I see by order of operations the equation would be -25. Seems a little to politically correct but... We have one calculator here that returns -25 and it is a bout a week old. I check my Casio when i get home. By this method there is no such thing as a negative integer only multiplication equation below zero. |
| Post Reply | Tell a Friend (must be logged in) | Alert Admin About Post | Where am I? Original Top of thread | | | |
| Re: Re: Re: Re: Re: Re: Re: Re: Re: WARNING! Microsoft Excel Error in Order of Operations... | |||
| Re: Re: Re: Re: Re: Re: Re: Re: Re: WARNING! Microsoft Excel Error in Order of Operations... -- fast eddie | Post Reply | Top of thread | Forum |
| Posted by: swearingen ® 06/15/2006, 12:54:43 Author Profile eMail author Edit |
There are two ways to look at it: you can define negatives by multiplying by -1 or by subtracting from 0. -1x5^2 = 0-5^2 Either one gives -5^2 which will work out to -25. I think subtracting from 0 would yield a negative integer. |
| Post Reply | Tell a Friend (must be logged in) | Alert Admin About Post | Where am I? Original Top of thread |
| Re: WARNING! Microsoft Excel Error in Order of Operations... | |||
| Re: WARNING! Microsoft Excel Error in Order of Operations... -- swearingen | Post Reply | Top of thread | Forum |
Posted by: randykimball ®  06/13/2006, 21:56:23 Author Profile eMail author Edit |
In MS Excel, parentheses are free. I use hoards of them. For one thing they make your formula much easier to troubleshoot. 'And parentheses force a formula to act the way you intended it to. The worst suggestion of your lifetime may be the catalyst to the grandest idea of the century, never let suggestions go unsaid nor fail to listen to them. |
| Post Reply | Tell a Friend (must be logged in) | Alert Admin About Post | Where am I? Original Top of thread | | | |
Powered by Engineers Edge
© Copyright 2000 - 2024, by Engineers Edge, LLC All rights reserved. Disclaimer
