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
25,289.87 | =+PI()*(+B3^4-B1^4)/64 |
638,882.19 | =+PI()*(-B1^4+B3^4)/64 |
Cell B1 is Di (50), cell B3 is Do (51)
Di | 50 |
Do | 51 |
Nov 30 2021 01:13 AM
@msmchou Change the second formula to =+PI()*(-(B1^4)+B3^4)/64
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).