Notation Bugs

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.

gciriani wrote:
>
> 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.
 >> Stay informed about: Notation Bugs 

On Oct 17, 5:13 pm, Dave Peterson wrote:
> 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.

> gciriani wrote:
>
> > 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.
>
> --
>
> Dave Peterson
 >> Stay informed about: Notation Bugs 

On Oct 17, 12:54 pm, gciriani wrote:
> 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.
 >> Stay informed about: Notation Bugs 

On Oct 17, 4:27 pm, Gord Dibben wrote:
> >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.
 >> Stay informed about: Notation Bugs 

If you want to play in excel, you'll have to play by its rules. And in this
case, excel actually documents its rules.



gciriani wrote:
>
> On Oct 17, 5:13 pm, Dave Peterson wrote:
> > 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.
>
> > gciriani wrote:
> >
> > > 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.
> >
> > --
> >
> > Dave Peterson
 >> Stay informed about: Notation Bugs 

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



"gciriani" wrote in message

> 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.
 >> Stay informed about: Notation Bugs 

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.
 >> Stay informed about: Notation Bugs