error in calculation when sequence is changed

Copper Contributor

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)

Di50
Do51
6 Replies

@msmchou Change the second formula to =+PI()*(-(B1^4)+B3^4)/64

Thanks, Riny, for your reply.
What I don't understand is (-B1^4) should be the same as -(B1^4) but when placed immediately after the bracket, it behaves differently. What is the logic?

@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

 

@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).

Many thanks for your reply!
Thank you for your reply!