Home

A case of wrong formula calculation in Excel?

%3CLINGO-SUB%20id%3D%22lingo-sub-395099%22%20slang%3D%22en-US%22%3EA%20case%20of%20wrong%20formula%20calculation%20in%20Excel%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-395099%22%20slang%3D%22en-US%22%3E%3CP%3EI%20like%20to%20have%20your%20opinion%20on%20the%20formula%20calculation%20performed%20by%20Excel%20in%20a%20particolar%20case%20that%20surprised%20me%20very%20much.%3CBR%20%2F%3EThis%20is%20the%20case.%3CBR%20%2F%3EA(1%2C1)%20%3D5%3CBR%20%2F%3EA(2%2C1)%20%3D%20-%20A1%5E2%3CBR%20%2F%3EExcel%20display%20result%20in%20A(2%2C1)%20%3D%20%2B25%2C%20that%20is%20wrong.%20Te%20correct%20result%20must%20be%20-25%20because%20the%20sign%20%E2%80%9C-%E2%80%9C%20in%20not%20under%20the%20square%20elevation.%3CBR%20%2F%3EI%20modified%20the%20formula%20in%20this%20way%3A%3CBR%20%2F%3EA(2%2C1)%20%3D%20-%20A1%5E2%20%2B%20A1%3CBR%20%2F%3EThe%20result%20is%20%3D%2030%20that%20is%20wrong%20again!%3CBR%20%2F%3EThe%20I%20changed%20the%20order%20of%20the%20two%20addendum%3CBR%20%2F%3EA(2%2C1)%20%3D%20A1%20-%20A1%5E2%3CBR%20%2F%3ENow%20the%20result%20is%20-20%20and%2C%20that%20is%20correct.%3CBR%20%2F%3EWhat%20is%20your%20opinion%3F%3CBR%20%2F%3ERegards%20and%20thank.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-395099%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-400672%22%20slang%3D%22en-US%22%3ERe%3A%20A%20case%20of%20wrong%20formula%20calculation%20in%20Excel%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-400672%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%20wrote%3A%3CBR%20%2F%3EAs%20you%20can%20see%20in%20the%20table%20provided%20by%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%20the%20-%20(minus%20sign)%20in%20the%20first%20case%20is%20a%20negation%20(a%20lower%20order%20then%20%5E)%20and%20in%20the%20second%20case%20a%20subtraction%20(a%20higher%20order%20then%20%5E.%3CBR%20%2F%3E-%3CBR%20%2F%3EThanks%20for%20the%20reply.%3CBR%20%2F%3EThe%20Excel%20rules%20are%20clear%20but%20unfortunately%20they%20are%20different%2C%20in%20this%20particular%20case%2C%20from%20the%20mathematical%20rules.%20This%20can%20generate%20an%20unexpected%20result%20and%20you%20have%20to%20be%20careful.%3CBR%20%2F%3EThanks%20again.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-400517%22%20slang%3D%22en-US%22%3ERe%3A%20A%20case%20of%20wrong%20formula%20calculation%20in%20Excel%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-400517%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F314091%22%20target%3D%22_blank%22%3E%40SergioSolimena%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E*%3C%2FP%3E%3CBLOCKQUOTE%3E%3CHR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F314091%22%20target%3D%22_blank%22%3E%40SergioSolimena%3C%2FA%3E%26nbsp%3Bwrote%3A%3CBR%20%2F%3EAs%20I%20shown%20in%20the%20example%2C%20the%20same%20formula%20y%3D%20-A%5E2%20%2BA%20%3D%2BA-%20A%5E2%20is%20written%20in%20two%20different%20way%2C%20only%20changing%20the%20position%20af%20the%20two%20addendum.%20The%20result%20must%20be%20exactly%20the%20same.%20But%20this%20not%20what%20Excel%20does.%3CBR%20%2F%3E*%3C%2FBLOCKQUOTE%3E%3CP%3EAs%20you%20can%20see%20in%20the%20table%20provided%20by%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3Bthe%26nbsp%3B%3CSTRONG%3E-%3C%2FSTRONG%3E%26nbsp%3B(minus%20sign)%20in%20the%20first%20case%20is%20a%20negation%20(a%20lower%20order%20then%20%3CSTRONG%3E%5E%3C%2FSTRONG%3E)%20and%20in%20the%20second%20case%20a%20subtraction%20(a%20higher%20order%20then%20%3CSTRONG%3E%5E%3C%2FSTRONG%3E).%3C%2FP%3E%3CP%3ESo%20by%20putting%20parenthesis%20around%20A%5E2%20you%20are%20changing%20the%20order%20of%20operation%3A%3C%2FP%3E%3CP%3E-(A%5E2)%2BA%20%3D%20%2BA-(A%5E2)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-397627%22%20slang%3D%22en-US%22%3ERe%3A%20A%20case%20of%20wrong%20formula%20calculation%20in%20Excel%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-397627%22%20slang%3D%22en-US%22%3EWhat%20you%20says%20is%20clear%20but%20it's%20not%20correct%20because%20in%20the%20formula%20y%3D-x%5E2%20the%20result%20is%20always%20negative%20for%20any%20x%20different%20from%20zero.%3CBR%20%2F%3EI%20don't%20have%20to%20use%20the%20parenthesis.%3CBR%20%2F%3EAs%20I%20shown%20in%20the%20example%2C%20the%20same%20formula%20y%3D%20-A%5E2%20%2BA%20%3D%2BA-%20A%5E2%20is%20written%20in%20two%20different%20way%2C%20only%20changing%20the%20position%20af%20the%20two%20addendum.%20The%20result%20must%20be%20exactly%20the%20same.%20But%20this%20not%20what%20Excel%20does.%3CBR%20%2F%3EThis%20means%20that%20it%20necessary%20to%20put%20attention%20at%20the%20order%20of%20the%20addendum%20in%20a%20sum%2C%20and%20this%20violates%20the%20commuted%20property%20of%20the%20terms%20of%20the%20sum.%3CBR%20%2F%3EBecause%20now%20I%20know%20this%20question%20I%20put%20always%20attention%20when%20i%20write%20a%20formula%2C%20but%20in%20a%20complex%20problem%20this%20not%20simple%20and%20reliable.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-395384%22%20slang%3D%22en-US%22%3ERe%3A%20A%20case%20of%20wrong%20formula%20calculation%20in%20Excel%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-395384%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20help%20page%20shows%20the%20operator%20precedence%20to%20be%20as%20shown%20in%20the%20following%20table.%3C%2FP%3E%3CP%3EIf%20in%20doubt%2C%20a%20pair%20of%20parentheses%20work%20wonders!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3E%3CP%3E%3CSTRONG%3EOperator%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E%3CSTRONG%3EDescription%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CP%3E%3A%20(colon)%3C%2FP%3E%3CP%3E(single%20space)%3C%2FP%3E%3CP%3E%2C%20(comma)%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3EReference%20operators%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CP%3E%3CSTRONG%3E%E2%80%93%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E%3CSTRONG%3ENegation%20(as%20in%20%E2%80%931)%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CP%3E%25%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3EPercent%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CP%3E%5E%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E%3CSTRONG%3EExponentiation%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CP%3E*%20and%20%2F%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3EMultiplication%20and%20division%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CP%3E%3CSTRONG%3E%2B%20%3C%2FSTRONG%3Eand%20%3CSTRONG%3E%E2%80%93%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E%3CSTRONG%3EAddition%20and%20subtraction%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CP%3E%26amp%3B%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3EConnects%20two%20strings%20of%20text%20(concatenation)%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CP%3E%3D%3CBR%20%2F%3E%26lt%3B%20%26gt%3B%3CBR%20%2F%3E%26lt%3B%3D%3CBR%20%2F%3E%26gt%3B%3D%3CBR%20%2F%3E%26lt%3B%26gt%3B%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3EComparison%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-395111%22%20slang%3D%22en-US%22%3ERe%3A%20A%20case%20of%20wrong%20formula%20calculation%20in%20Excel%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-395111%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F314091%22%20target%3D%22_blank%22%3E%40SergioSolimena%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheck%20the%20order%20of%20operation%20with%20formula%20evaluation.%3C%2FP%3E%3CP%3E-A1%5E2%20-%26gt%3B%20-5%20-%26gt%3B%20-5%5E2%20-%26gt%3B%2025%3C%2FP%3E%3CP%3E-(A1%5E2)%20-%26gt%3B%20-(5%5E2)%20-%26gt%3B%20-(25)%20-%26gt%3B%20-25%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
SergioSolimena
Occasional Contributor

I like to have your opinion on the formula calculation performed by Excel in a particolar case that surprised me very much.
This is the case.
A(1,1) =5
A(2,1) = - A1^2
Excel display result in A(2,1) = +25, that is wrong. Te correct result must be -25 because the sign “-“ in not under the square elevation.
I modified the formula in this way:
A(2,1) = - A1^2 + A1
The result is = 30 that is wrong again!
The I changed the order of the two addendum
A(2,1) = A1 - A1^2
Now the result is -20 and, that is correct.
What is your opinion?
Regards and thank.

5 Replies

@SergioSolimena 

Check the order of operation with formula evaluation.

-A1^2 -> -5 -> -5^2 -> 25

-(A1^2) -> -(5^2) -> -(25) -> -25

 

@Detlef Lewin

 

The help page shows the operator precedence to be as shown in the following table.

If in doubt, a pair of parentheses work wonders!

 

Operator

Description

: (colon)

(single space)

, (comma)

Reference operators

Negation (as in –1)

%

Percent

^

Exponentiation

* and /

Multiplication and division

+ and

Addition and subtraction

&

Connects two strings of text (concatenation)

=
< >
<=
>=
<>

Comparison

 

 

Highlighted
What you says is clear but it's not correct because in the formula y=-x^2 the result is always negative for any x different from zero.
I don't have to use the parenthesis.
As I shown in the example, the same formula y= -A^2 +A =+A- A^2 is written in two different way, only changing the position af the two addendum. The result must be exactly the same. But this not what Excel does.
This means that it necessary to put attention at the order of the addendum in a sum, and this violates the commuted property of the terms of the sum.
Because now I know this question I put always attention when i write a formula, but in a complex problem this not simple and reliable.

@SergioSolimena 

*


@SergioSolimena wrote:
As I shown in the example, the same formula y= -A^2 +A =+A- A^2 is written in two different way, only changing the position af the two addendum. The result must be exactly the same. But this not what Excel does.
*

As you can see in the table provided by @Peter Bartholomew the - (minus sign) in the first case is a negation (a lower order then ^) and in the second case a subtraction (a higher order then ^).

So by putting parenthesis around A^2 you are changing the order of operation:

-(A^2)+A = +A-(A^2)

 

@Detlef Lewin wrote:
As you can see in the table provided by @Peter Bartholomew the - (minus sign) in the first case is a negation (a lower order then ^) and in the second case a subtraction (a higher order then ^.
-
Thanks for the reply.
The Excel rules are clear but unfortunately they are different, in this particular case, from the mathematical rules. This can generate an unexpected result and you have to be careful.
Thanks again.
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
217 Replies