04-04-2019 12:24 PM
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.
04-04-2019 12:39 PM
Check the order of operation with formula evaluation.
-A1^2 -> -5 -> -5^2 -> 25
-(A1^2) -> -(5^2) -> -(25) -> -25
04-04-2019 01:09 PM
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 |
04-04-2019 03:38 PM
04-04-2019 09:13 PM
*
@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)
04-05-2019 02:35 AM