Excel should calculate exponentials properly (right to left)

[Guest]

As a structural engineer, I have occasional need for exponentials of
exponentials - Excel does not calculate these correctly - right to left, so I
need to put extra parentheses in the formula to adjust.

I encounter this problem with column design stress calculations, if anybody
cares. Proper scientific calculation order is
1) exponentials - right to left
2) multiplication and division left to right
3) addition and subtraction left to right.

It would be nice if Excel would follow this method so I don't need to
remember special rules for formulas in Excel that don't correspond to more
involved math oriented programs like mathematica or MathCAD. It would also
make using the program easier for structural engineers like me.



----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...dg=microsoft.public.excel.worksheet.functions

 

[JE McGimpsey]

It undoubtedly would, but don't hold your breath. Doing so would break
tens of thousands of existing applications.

It would also make exponentiation the only operator that worked
right-to-left. That alone would make things extremely confusing for
non-structural-engineers.

Operator precedence and direction of application is neither correct nor
incorrect - it's convention. And scientific convention is not the
convention that Excel was designed for. A frequent example is

-x^(2*n)

XL will always calculate this as a positive number (for integer n's)
since negation has a higher precedence than exponentiation. This seems
counterintuitive to most of those that have commented on it in these
groups. But it's consistent with XL's published specs.

 

[Harlan Grove]

expatrie wrote...

As a structural engineer, I have occasional need for exponentials of
exponentials - Excel does not calculate these correctly - right to left, so I
need to put extra parentheses in the formula to adjust.

....

Excel also doesn't follow standard conventions with respect to sign and
exponentiation, e.g., -3^2 returns +9 rather than -9. This is perhaps
unfortunate, but it *IS* documented functionality. Excel's operator
precedence is given explicitly. Its strict left to right associativity
is mentioned in passing. Note: left to right associativity is common to
*ALL* spreadsheets, not just Excel. Nonstandard associativity (and
precedence) has been around since APL was introduced in the mid-1960s.

From online help ('About Formulas' topic):

The order in which Excel performs operations in formulas

Formulas calculate values in a specific order. A formula in Excel
always begins with an equal sign (=). The equal sign tells Excel that
the succeeding characters constitute a formula. Following the equal
sign are the elements to be calculated (the operands), which are
separated by calculation operators. Excel calculates the formula from
left to right, according to a specific order for each operator in the
formula.

Operator precedence

If you combine several operators in a single formula, Excel performs
the operations in the order shown in the following table. If a formula
contains operators with the same precedence - for example, if a
formula contains both a multiplication and division operator - Excel
evaluates the operators from left to right.

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


This has been argued & argued & argued A LOT in the past in these
newsgroups. Microsoft isn't going to change Excel's specs in this
regard. Doing so would break too many existing workbooks/formulas. Your
only choices are get used to it or use different software.

 

[Guest]

Proper exponentiation calculations is not used solely in structural
engineering. It would be more common in people using statistics or higher
calculus and mathematics. All of these people are familiar with the order of
precedence rule for math (which isn't a sign convention like you discuss, it
is an accepted standard similar to base 10. I believe all mathematical
journals use this convention and it is pretty much understood as the
convention to be used in writing all formulas in all technical journals I've
read.)

Seriously, how many non-scientific people are using excel for multiple
exponentiation ("tens of thousands?")? Only people who have been trained in
college algebra/mathematics will be using multiple exponentiation in the
first place, to be using it, you would thus be familiar with the existing
sign convention. I suppose the evidence against this fact is the lack of
enthusiasm for the suggestion I submitted. I can only presume everyone just
accepts the weird calculation convention and fixes it with excessive
parenthesis.

While I find your comment about conventions mildly distressing (to use a
word), left and right are also conventions that we use every day and there
isn't any debate. If we're going to invent an Excel calculation order of
precedence rule, why not do division last, multiplication first, or copy the
calculation convention from Fourth? That it is documented somewhere in the
thousandth layer of a help file doesn't mean in can't change.

Breaking "tens of thousands" of existing spreadsheets is also moot, I think,
because to make the existing calculations work in the correct order (forcing
right to left exponentiation) requires the use of parentheses which, if they
corrected the precedence order would be meaningless because the calculation
would proceed in the correct order so the parenthesis have no affect.

example:

x^2^3 = x^8, right

to make Excel do this correctly, x^(2^3) right?

now, evaluating properly, exponentiation first, right to left, 2^3 = 8, x^8.
QED.

I realize this is a simplified example, but remember, the existing
parenthesis system in the formula must force Excel to calculate
exponentiation first, right to left. As the calculation order precedence is
changing to do this rule properly, I state with confidence that these
calculations will be unaffected,

I wonder how VBA macros would deal with this issue, is the implication that
VBA calculates in normal precedence order whereas Excel formulas calculate
strictly left to right? That would mean the two formulas would produce
different answers despite being written exactly the same? How distressing is
that?

Regarding your example below - if x is a negative number, it should produce
the proper result. The problem here is that Excel is processing the negative
sign for the number as a subtraction sign, as in minus seven to the fourth
power, not negative seven to the fourth power. I think this is a different
problem than the one I am discussing.

 

[JE McGimpsey]

The point is that it doesn't matter what the "proper" way to do it is.
It doesn't matter if every math and science journal in the world uses a
different convention. Microsoft is *not*, under any scenario I can
imagine, going to silently break existing client spreadsheets, which is
what changing the order of evaluation for the exponentiation operator
would do. Nor are they going to break those spreadsheets in a way that
forces users/developers to rewrite their formulae/code. Period.

Changing XL's order of evaluation would also have enormous repercussions
for VB/VBA, which uses the same convention as XL.

My negation operator example wasn't meant to demonstrate the same issue,
merely to illustrate another case in which, while the preponderance of
authorities dictate evaluation of exponentiation before negation in
-x^y, XL doesn't, and almost certainly never will. Negation and
subtraction are different (i.e., negative seven is a number, the
negation of seven is a unary operation, and "... minus seven" implies,
at least in my training, a binary operation) - floating point units
typically handle negation and subtraction operations very differently.

Again, we're talking about conventions here, even if your preferred
convention is nearly universal. As long as the results of the existing
convention are documented, consistent and correct, there's no business
case to change (and a substantial case against changing) when there's an
installed base of users and applications.

 

[Guest]

I agree. I also just noticed that Excel does not follow the order of
operations inside of the exponential - that is, exp(-1^2)=e, instead of 1/e,
but exp(-(1^2))=1/e. I think this qualifies as a bug!!!

Signed,

An engineering student