error in calculation when sequence is changed

%3CLINGO-SUB%20id%3D%22lingo-sub-3017056%22%20slang%3D%22en-US%22%3Eerror%20in%20calculation%20when%20sequence%20is%20changed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3017056%22%20slang%3D%22en-US%22%3E%3CP%3EI%20discovered%20an%20issue%20with%20the%20formula%20shown%20below%20(2nd%20row%20is%20the%20wrong%20answer).%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFormula%20is%20actually%20the%20same%20but%20answers%20are%20different%3C%2FP%3E%3CTABLE%20width%3D%22340%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22132%22%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%2025%2C289.87%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22208%22%3E%3CP%3E%3D%2BPI()*(%2BB3%5E4-B1%5E4)%2F64%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22132%22%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20638%2C882.19%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22208%22%3E%3CP%3E%3D%2BPI()*(-B1%5E4%2BB3%5E4)%2F64%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECell%20B1%20is%20Di%20(50)%2C%20cell%20B3%20is%20Do%20(51)%3C%2FP%3E%3CTABLE%20width%3D%22200%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2268%22%3EDi%3C%2FTD%3E%3CTD%20width%3D%22132%22%3E50%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CTABLE%20width%3D%22200%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2268%22%3EDo%3C%2FTD%3E%3CTD%20width%3D%22132%22%3E51%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3017056%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3017192%22%20slang%3D%22en-US%22%3ERe%3A%20error%20in%20calculation%20when%20sequence%20is%20changed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3017192%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1233266%22%20target%3D%22_blank%22%3E%40msmchou%3C%2FA%3E%26nbsp%3BChange%20the%20second%20formula%20to%26nbsp%3B%3CSPAN%3E%3D%2BPI()*(-%3CSTRONG%3E%3CFONT%20color%3D%22%23FF0000%22%3E(%3C%2FFONT%3E%3C%2FSTRONG%3EB1%5E4%3CSTRONG%3E%3CFONT%20color%3D%22%23FF0000%22%3E)%3C%2FFONT%3E%3C%2FSTRONG%3E%2BB3%5E4)%2F64%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3020942%22%20slang%3D%22en-US%22%3ERe%3A%20error%20in%20calculation%20when%20sequence%20is%20changed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3020942%22%20slang%3D%22en-US%22%3EThanks%2C%20Riny%2C%20for%20your%20reply.%3CBR%20%2F%3EWhat%20I%20don't%20understand%20is%20(-B1%5E4)%20should%20be%20the%20same%20as%20-(B1%5E4)%20but%20when%20placed%20immediately%20after%20the%20bracket%2C%20it%20behaves%20differently.%20What%20is%20the%20logic%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3021147%22%20slang%3D%22en-US%22%3ERe%3A%20error%20in%20calculation%20when%20sequence%20is%20changed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3021147%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1233266%22%20target%3D%22_blank%22%3E%40msmchou%3C%2FA%3E%26nbsp%3BWell%2C%20that's%20basic%20mathematics.%26nbsp%3B%3C%2FP%3E%3CP%3EFirst%20of%20all%2C%20when%20you%26nbsp%3Bexponentiate%20a%20negative%20number%20to%20an%20even%20power%2C%20the%20result%20becomes%20positive.%20So%2C%20minus%2050%20to%20the%20power%20of%204%20equals%206%2C250%2C000.%3C%2FP%3E%3CP%3EIn%20the%20first%20equation%20you%20have%20%22something%22%20minus%206%2C250%2C000.%20In%20the%20second%20one%20you%20have%206%2C250%2C000%20plus%20%22something%22.%20I'm%20not%20a%20maths%20teacher%2C%20so%20perhaps%20I'm%20not%20very%20clear%2C%20but%20you%20need%20the%20brackets%20to%20make%20the%20first%20term%20a%20negative%20number.%20Think%20of%20it%20like%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E(-50)%5E4%20%3D%206%2C250%2C000%3C%2FP%3E%3CP%3E-(-50%5E4)%20%3D%20-6%2C250%2C000%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnother%20example%20with%20smaller%20numbers.%3C%2FP%3E%3CP%3E10%20-%203%5E2%20gets%20evaluated%20as%2010%20-%20(3%5E2)%20%3D%2010%20-%209%20%3D%201%3C%2FP%3E%3CP%3E-3%5E2%20%2B%2010%20get%20evaluated%20as%20(-3%5E2)%20%2B%2010%20%3D%209%20%2B%2010%20%3D%2019%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3021506%22%20slang%3D%22en-US%22%3ERe%3A%20error%20in%20calculation%20when%20sequence%20is%20changed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3021506%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1233266%22%20target%3D%22_blank%22%3E%40msmchou%3C%2FA%3E%26nbsp%3B%20wrote%3A%20%60%60(-B1%5E4)%20should%20be%20the%20same%20as%20-(B1%5E4)%20but%20when%20placed%20immediately%20after%20the%20bracket%2C%20it%20behaves%20differently.%20What%20is%20the%20logic%3F%20%60%60%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20is%20no%20%22logic%22%20to%20understand.%26nbsp%3B%20Yes%2C%20in%20the%20language%20of%20math%2C%20exponentiation%20has%20higher%20precedence%20than%20unary%20minus.%26nbsp%3B%20So%20when%20we%20write%20effectively%20-2%5E4%2C%20it%20is%20parsed%20as%20-(2%5E4)%2C%20and%20the%20result%20is%20-16.%26nbsp%3B%20And%20actually%2C%20that%20is%20implied%20by%20the%20superscript%204.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20in%20the%20language%20of%20Excel%2C%20MSFT%20chose%20the%20opposite%20order%20precedence.%26nbsp%3B%20Refer%20to%20%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fcalculation-operators-and-precedence-in-excel-48be406d-4975-4d31-b2b8-7af9e0e2878a%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fcalculation-operators-and-precedence-in-excel-48be406d-4975-4d31-b2b8-7af9e0e2878a%3C%2FA%3E%20.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20-2%5E4%20is%20parsed%20as%20(-2)%5E4%2C%20and%20the%20result%20is%2016.%26nbsp%3B%20It%20is%20a%20choice%20that%20the%20Excel%20designers%20made.%26nbsp%3B%20Actually%2C%20that%20choice%20might%20have%20been%20made%20by%20Visicalc%20or%20Lotus%201-2-3%2C%20and%20Multiplan%2FExcel%20simply%20chose%20to%20be%20compatible.%26nbsp%3B%20I%20don't%20remember.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20is%20no%20right%20or%20wrong.%26nbsp%3B%20Each%20language%20makes%20its%20own%20choice%20about%20precedence%20--%20and%20even%20order%20of%20evaluation.%26nbsp%3B%20In%20the%20computer%20language%20APL%2C%20there%20is%20no%20operator%20precedence%2C%20and%20expressions%20are%20evaluate%20from%20right-to-left.%26nbsp%3B%20So%204*3%2B2%20is%2020%2C%20not%2014.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20Excel%2C%20if%20we%20want%20-2%5E4%20to%20be%20treated%20the%20same%20as%20it%20is%20in%20math%2C%20we%20must%20use%20parentheses%20to%20override%20the%20default%20order%20precedence.%26nbsp%3B%20That%20is%20why%20we%20must%20write%20-(2%5E4)%20or%20-POWER(2%2C4).%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3022797%22%20slang%3D%22en-US%22%3ERe%3A%20error%20in%20calculation%20when%20sequence%20is%20changed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3022797%22%20slang%3D%22en-US%22%3EMany%20thanks%20for%20your%20reply!%3C%2FLINGO-BODY%3E
New 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!