Prev: acSpreadsheetTypeExcel
Next: Compile error in hidden module
From: Atreides on 22 Feb 2005 03:39
Ken, I am in immense respect of you! I've just read the entire topic on this
bug on the google newsgroup... no-one really listening, just arguing, ego's
flying etc. Your humilty and gentleness is very pleasantly refreshing!

Cheers to you! :)
From: Dana DeLouis on 22 Feb 2005 11:23
I think the consensus is that Excel is wrong in this calculation, and most
likely will never be fixed. I think the problem is that Excel can not look
ahead in its interpretation of the equation to see that ^ would come first,
and then take the negative of this number. I agree with Harlan as it
appears Excel can only read Left to Right, and that's it. A program like
Mathematica can read the whole expression correctly.

For a small demo, Excel does the following left to right only...
=4^3^2
4096

But Mathematica will do this correctly as 4^(3^2)
4^3^2
262144

As you can see, Excel just can't look ahead to do it properly.

I've never liked the help file explanation on Operator precedence.
"Negation (as in -1)" Negation I think usually means True / False. I think
we have to guess that what they mean is that it will flip the sign bit of
the number if this is what's seen first (and disregard anything later as in
^). My thoughts are this is not a very good explanation.

http://mathworld.wolfram.com/Negation.html

Just some other thoughts. A nice feature of Excel though is its ability to
interpret text as numbers where appropriate. This is a "nice" feature for
Excel, but not for a math program. For example, if A1 had the text '5

You could use = - - A1 to get the number 5.
Same with =A1+0

Of course, in Mathematica you could not add the text "5" and zero to get 5.
And the - - A1 is the PreDecrement operator, so this would not make sense in
Mma.

If A1 held the number 5, in Excel, you could have a formula like:
=A1--------2
7

This would not make sense in Mathematica.

You may want to look at the "InputForm" of Mma equation and use the same
"Power" function with Excel. Using Excel's Power function is a good way to
make it clear what you are doing. I have experimented with putting a "Hold"
around the equation (via "HoldForm") and work with the Power pattern, but
never had much luck with this approach myself. Once you put a Release on
the hold, the equation will simplify again. I do have a custom //Vba
function that transforms the output into the format for Excel's vba, but it
doesn't cover everything.

For a simple demo. If you were not sure, and want to enter =4^3^2 in Excel,
you may want to take a look at how this is done, and use the power function
in Excel.

FullForm[HoldForm[4^3^2]]

Power[4,Power[3,2]

I would take the hint and do it like this in Excel
=POWER(4,POWER(3,2))

And for those interested ...
FullForm[HoldForm[-5^2]]
Times[-1, Power[5, 2]]

--
Dana DeLouis
Win XP & Office 2003


"Atreides" <atreides1AThotmailD0Tcom> wrote in message
news:4CED2A2B-FB5C-400A-8E7A-3E7710305545(a)microsoft.com...
> Ken, I am in immense respect of you! I've just read the entire topic on
> this
> bug on the google newsgroup... no-one really listening, just arguing,
> ego's
> flying etc. Your humilty and gentleness is very pleasantly refreshing!
>
> Cheers to you! :)


From: Harlan Grove on 22 Feb 2005 13:18
Royman101 wrote...
>Maybe is skewed. I was taught that -3^2=9, and not -9. The integer
used
>is -3, not -1*3. You may think I was taught wrong. I submit to you I
was not
>taught in this country, rather, I grew up in Europe. Could it be that
Excel
>had some European programmers....

I have a few German language math texts that show equations that
clearly show they follow exponentiation before negation operator
precedence. Perhaps it may be unreasonable to extrapolate from that
that Germany follows the same operator precedence convention as the US,
but I'd be willing to bet that was so.

From: Harlan Grove on 22 Feb 2005 14:34
Dana DeLouis wrote...
>I think the consensus is that Excel is wrong in this calculation, and
most
>likely will never be fixed. I think the problem is that Excel can not
look
>ahead in its interpretation of the equation to see that ^ would come
first,
>and then take the negative of this number. I agree with Harlan as it
>appears Excel can only read Left to Right, and that's it. A program
like
>Mathematica can read the whole expression correctly.
>
>For a small demo, Excel does the following left to right only...
>=4^3^2
>4096
>
>But Mathematica will do this correctly as 4^(3^2)
>4^3^2
>262144
>
>As you can see, Excel just can't look ahead to do it properly.

You're misunderstanding what I wrote *AND* attributing to me things I
didn't state.

VisiCalc uses simple L-to-R evaluation: 2+3*4 returns 20.

123 uses an operator precedence hierarchy: 2+3*4 returns 14. It gives ^
higher precedence than unary -: -3^2 returns -9.

Excel uses an operator precedence hierarchy: 2+3*4 returns 14. However,
it gives ^ *lower* precedence than unary -: -3^2 returns 9.

With regard to 4^3^2, it has nothing to do with operator *precedence* -
it's operator *associativity*. FWIW, most programming languages (other
than VB[?] and oddballs like APL and its descendants) provide R-to-L
associativity for exponentiation and L-to-R associativity for +-*/ (+
and * are associative for integers, rational, algebraic, real and
complex numbers, but not always so for binary floating point
'numbers').

Precedence determines evaluation order of *DIFFERENT* operators.
Associativity determines evaluation order of the *SAME* operator
applied multiple times in sequence.

>I've never liked the help file explanation on Operator precedence.
>"Negation (as in -1)" Negation I think usually means True / False. I
think
>we have to guess that what they mean is that it will flip the sign bit
of
>the number if this is what's seen first (and disregard anything later
as in
>^). My thoughts are this is not a very good explanation.
....

Yeah, yeah. Agreed, but in colloquial discussions it's too much of a
PITA to say additive inverse. Sign change may be an alternative.

>Of course, in Mathematica you could not add the text "5" and zero to
get 5.
>And the - - A1 is the PreDecrement operator, so this would not make
sense in
>Mma.
....

Nor in C or all the other languages it's spawned. So use -(-A1).

>And for those interested ...
>FullForm[HoldForm[-5^2]]
>Times[-1, Power[5, 2]]

And there are some like me who consider this to be a really stupid
approach due to its circular nature. Sign and exponentiation together
only make sense in rings, rings are necessarily additive groups,
additive groups have well-defined additive inverses, and

AdditiveInverse(MultiplicativeIdentity) * x = AdditiveInverse(x)

is a derived truth that necessarily relies upon additive inverse to
provide -1. So why not express -5^2 as -(5^2) or perhaps

ChangeSign[Power[5, 2]]

?

This entire problem is due to the laziness of mathematicians in
previous centuries who used the same character/token/sign to express
numeric sign, sign change and subtraction. An alternative convention
might have been to interpret a dash *not* immediately after a complete
expression but immediately before a literal number [e.g., x*-3^2 == x *
((-3) ^ 2)] as part of the number (so, technically, not subject to
operator precedence because it wouldn't be an operator), but between
incomplete expressions with the expression to the right *not* a literal
number treat it as a sign change operator with standard precedence
[e.g., x*-y^2 == x * (-(y^2))]. One argument against this convention is
that you'd need to remember that literal numbers and variables would be
treated differently, e.g., y = 3, -3^2 wouldn't equal -y^2.

Computers wouldn't have a problem with this now. Lexical analysis
precedes syntactic parsing, so just need to include leading - and + as
part of literal number tokens when they clearly couldn't be dyadic
operators. However that caveat implies the presence of noncapturing
assertions in the lexical analyzer, and those weren't part of most
regular expression packages until the mid 1980s.

From: Dana DeLouis on 22 Feb 2005 17:13
Thanks Harlan. I wish there were more of these discussions. I was trying
to come up with another example for the op to show that Excel is unable to
do a R-to-L "associative" operation. For the op, I guess the best one can
offer is just be aware of Excel's limitations, and try to anticipate the
differences between the two programs.

(A little off topic I know) For the op, a slightly different point from
what was mentioned is that in Mma, the Power function does not have the
"Associative property", as it does not have the Flat attribute. Flat
corresponds to the mathematical property of associativity.
Functions like Times & Plus are "Flat", and associative. Therefore...

Power[4, 3, 2]
262144

Attributes[Power]
{Listable, NumericFunction, OneIdentity, Protected}

Attributes[Times]
{Flat, ..., OneIdentity, Orderless, ....}

Again, just be aware of the differences...
--
Dana DeLouis
Win XP & Office 2003



"Harlan Grove" <hrlngrv(a)aol.com> wrote in message
news:1109100865.605220.112310(a)g14g2000cwa.googlegroups.com...
> Dana DeLouis wrote...
>>I think the consensus is that Excel is wrong in this calculation, and
> most
>>likely will never be fixed. I think the problem is that Excel can not
> look
>>ahead in its interpretation of the equation to see that ^ would come
> first,
>>and then take the negative of this number. I agree with Harlan as it
>>appears Excel can only read Left to Right, and that's it. A program
> like
>>Mathematica can read the whole expression correctly.
>>
>>For a small demo, Excel does the following left to right only...
>>=4^3^2
>>4096
>>
>>But Mathematica will do this correctly as 4^(3^2)
>>4^3^2
>>262144
>>
>>As you can see, Excel just can't look ahead to do it properly.
>
> You're misunderstanding what I wrote *AND* attributing to me things I
> didn't state.
>
> VisiCalc uses simple L-to-R evaluation: 2+3*4 returns 20.
>
> 123 uses an operator precedence hierarchy: 2+3*4 returns 14. It gives ^
> higher precedence than unary -: -3^2 returns -9.
>
> Excel uses an operator precedence hierarchy: 2+3*4 returns 14. However,
> it gives ^ *lower* precedence than unary -: -3^2 returns 9.
>
> With regard to 4^3^2, it has nothing to do with operator *precedence* -
> it's operator *associativity*. FWIW, most programming languages (other
> than VB[?] and oddballs like APL and its descendants) provide R-to-L
> associativity for exponentiation and L-to-R associativity for +-*/ (+
> and * are associative for integers, rational, algebraic, real and
> complex numbers, but not always so for binary floating point
> 'numbers').
>
> Precedence determines evaluation order of *DIFFERENT* operators.
> Associativity determines evaluation order of the *SAME* operator
> applied multiple times in sequence.
>
>>I've never liked the help file explanation on Operator precedence.
>>"Negation (as in -1)" Negation I think usually means True / False. I
> think
>>we have to guess that what they mean is that it will flip the sign bit
> of
>>the number if this is what's seen first (and disregard anything later
> as in
>>^). My thoughts are this is not a very good explanation.
> ...
>
> Yeah, yeah. Agreed, but in colloquial discussions it's too much of a
> PITA to say additive inverse. Sign change may be an alternative.
>
>>Of course, in Mathematica you could not add the text "5" and zero to
> get 5.
>>And the - - A1 is the PreDecrement operator, so this would not make
> sense in
>>Mma.
> ...
>
> Nor in C or all the other languages it's spawned. So use -(-A1).
>
>>And for those interested ...
>>FullForm[HoldForm[-5^2]]
>>Times[-1, Power[5, 2]]
>
> And there are some like me who consider this to be a really stupid
> approach due to its circular nature. Sign and exponentiation together
> only make sense in rings, rings are necessarily additive groups,
> additive groups have well-defined additive inverses, and
>
> AdditiveInverse(MultiplicativeIdentity) * x = AdditiveInverse(x)
>
> is a derived truth that necessarily relies upon additive inverse to
> provide -1. So why not express -5^2 as -(5^2) or perhaps
>
> ChangeSign[Power[5, 2]]
>
> ?
>
> This entire problem is due to the laziness of mathematicians in
> previous centuries who used the same character/token/sign to express
> numeric sign, sign change and subtraction. An alternative convention
> might have been to interpret a dash *not* immediately after a complete
> expression but immediately before a literal number [e.g., x*-3^2 == x *
> ((-3) ^ 2)] as part of the number (so, technically, not subject to
> operator precedence because it wouldn't be an operator), but between
> incomplete expressions with the expression to the right *not* a literal
> number treat it as a sign change operator with standard precedence
> [e.g., x*-y^2 == x * (-(y^2))]. One argument against this convention is
> that you'd need to remember that literal numbers and variables would be
> treated differently, e.g., y = 3, -3^2 wouldn't equal -y^2.
>
> Computers wouldn't have a problem with this now. Lexical analysis
> precedes syntactic parsing, so just need to include leading - and + as
> part of literal number tokens when they clearly couldn't be dyadic
> operators. However that caveat implies the presence of noncapturing
> assertions in the lexical analyzer, and those weren't part of most
> regular expression packages until the mid 1980s.
>





First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4
Prev: acSpreadsheetTypeExcel
Next: Compile error in hidden module