Notation Bugs

  • Thread starter Thread starter gciriani
  • Start date Start date
G

gciriani

Did anybody notice that Excel handles math notations (symbols) in an
incorrect way?
Try this: write -2 in cell A1.
Write =1-A1^2 in a different cell.
Write =-A1^2+1 in another cell.

The two expressions are equivalent, shouldn't they? Well, the former
gives -3, the latter gives 5.
 
The formulas are not equivalent

Try =-(A1^2) + 1 for second formula.


Gord Dibben MS Excel MVP
 
Look up "Operator precedence" in excel's help.

You'll see that it doesn't conform to what you were taught in school (well,
probably).

I know that I always include parens when I want to make sure the expression does
what I want.
 
Hi Giovanni,

There is nothing odd there at all,
A1^2 is the same as A1*A1
so your formulae are like this

1 - A1 multiplied by A1 (multiplication is done before addition or
subtraction)
so -2 multiplied -2 = 4 and 1 - 4 = -3

-A1 multiplied by -A1 (minus minus 2 is equal to 2) therefore
2 multiplied 2 = 4 and + 1 = 5

That's the standard for all equations, multiplication first
dividion second addition third and subtraction last.

HTH
Martin
 
Look up "Operator precedence" in excel's help.

You'll see that it doesn't conform to what you were taught in school (well,
probably).

I know that I always include parens when I want to make sure the expression does
what I want.
That's exactly what I meant, giving precedence to the negation, over
the exponent, is not correct. Negation should be treated exactly like
a subtraction from 0. As you pointed out, this is not the notation we
were taught in school. The notation learned in school has a reason to
exist: it is the only one respecting the commutative law of addition
and subtraction: changing the order of the numbers does not change the
result:
A-B = -B+A

You are smart to use parenthesis, to make sure that Excel doesn't get
it wrong, but what you are doing, is you are interpreting Excel
peculiar notation rule. Instead it should be Excel interpreting one's
notations correctly, not the other way around.
 
Did anybody notice that Excel handles math notations (symbols)
in an incorrect way?
Try this: write -2 in cell A1.
Write =1-A1^2 in a different cell.
Write =-A1^2+1 in another cell.
The two expressions are equivalent, shouldn't they?

Using Excel Help, search for "operators", click on "About calculation
operators", then click on "The order ....".

You will see that unary negation is performed before exponentiation.
So the second formula is evaluate as (-A1)^2-1. In contrast, binary
subtraction is performed after exponentiation. So the first formula
is evaluated as 1-(A1^2), which is equivalent to -(A1^2)+1.

When in doubt, fully parenthesize an expression. But I encourage
moderation because too many nested parenthetical expressions can
obscure the formula and lead to other human errors.
 
The formulas are not equivalent

Try  =-(A1^2) + 1  for second formula.
The formulas may not be equivalent to Excel, but the notation I use
complies with the commutative law.
A-B = -B+A
Or do you advocate that from now we will have to write it as
A-B = -(B)+A ?
 
If you want to play in excel, you'll have to play by its rules. And in this
case, excel actually documents its rules.
 
Joe's post gets to the heart of the issue. The negation operator (-) is near top of the
precedence list, whereas the subtraction operator (also -) is near the bottom. We have to
remember that formulas are not the same as equations. Have lots of parentheses in your
pocket.

--
Regards from Virginia Beach,

Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
Did anybody notice that Excel handles math notations (symbols)
in an incorrect way?
Try this: write -2 in cell A1.
Write =1-A1^2 in a different cell.
Write =-A1^2+1 in another cell.
The two expressions are equivalent, shouldn't they?

Using Excel Help, search for "operators", click on "About calculation
operators", then click on "The order ....".

You will see that unary negation is performed before exponentiation.
So the second formula is evaluate as (-A1)^2-1. In contrast, binary
subtraction is performed after exponentiation. So the first formula
is evaluated as 1-(A1^2), which is equivalent to -(A1^2)+1.

When in doubt, fully parenthesize an expression. But I encourage
moderation because too many nested parenthetical expressions can
obscure the formula and lead to other human errors.
 
Back
Top