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.