Nov 30 2021 12:35 AM
Nov 30 2021 12:35 AM
I discovered an issue with the formula shown below (2nd row is the wrong answer).
Formula is actually the same but answers are different
Cell B1 is Di (50), cell B3 is Do (51)
Nov 30 2021 05:37 PM
Nov 30 2021 08:29 PM
@msmchou Well, that's basic mathematics.
First of all, when you exponentiate a negative number to an even power, the result becomes positive. So, minus 50 to the power of 4 equals 6,250,000.
In the first equation you have "something" minus 6,250,000. In the second one you have 6,250,000 plus "something". I'm not a maths teacher, so perhaps I'm not very clear, but you need the brackets to make the first term a negative number. Think of it like this:
(-50)^4 = 6,250,000
-(-50^4) = -6,250,000
Another example with smaller numbers.
10 - 3^2 gets evaluated as 10 - (3^2) = 10 - 9 = 1
-3^2 + 10 get evaluated as (-3^2) + 10 = 9 + 10 = 19
Nov 30 2021 11:42 PM - edited Dec 01 2021 06:33 AM
@msmchou wrote: ``(-B1^4) should be the same as -(B1^4) but when placed immediately after the bracket, it behaves differently. What is the logic? ``
There is no "logic" to understand. Yes, in the language of math, exponentiation has higher precedence than unary minus. So when we write effectively -2^4, it is parsed as -(2^4), and the result is -16. And actually, that is implied by the superscript 4.
But in the language of Excel, MSFT chose the opposite order precedence. Refer to https://support.microsoft.com/en-us/office/calculation-operators-and-precedence-in-excel-48be406d-49... .
So -2^4 is parsed as (-2)^4, and the result is 16. It is a choice that the Excel designers made. Actually, that choice might have been made by Visicalc or Lotus 1-2-3, and Multiplan/Excel simply chose to be compatible. I don't remember.
There is no right or wrong. Each language makes its own choice about precedence -- and even order of evaluation. In the computer language APL, there is no operator precedence, and expressions are evaluate from right-to-left. So 4*3+2 is 20, not 14.
In Excel, if we want -2^4 to be treated the same as it is in math, we must use parentheses to override the default operator precedence. That is why we must write -(2^4) or -POWER(2,4).