Be careful when using Excel
 

"Antonio Vernucci" wrote in
:

Subject: Be careful when using Excel
From: "Antonio Vernucci"
Newsgroups: rec.radio.amateur.antenna
Reply-To: "Antonio Vernucci"



--
------------------------------------------------------
Antonio Vernucci, I0JX US call: K0JX
Beacons: 50.004 MHz & 70.088 MHz
Home page: http://www.qsl.net/i0jx
e-mail: k0jx {at} amsat {dot} org
------------------------------------------------------
"Jeff" ha scritto nel messaggio
. com...
"Antonio Vernucci" wrote in message
.. .
I think this message can be of interest for those using Excel for
antenna calculations.

Write in one cell: =(-A1^2 + 8) Note: the exponent of A1 is 2
(and not 2+8=10) because Excel performs squaring before summing

Write in another cell: =(8 - A1^2)

They look pretty much the same

But give A1 any non-zero value and see what happen.

73

Tony I0JX
Rome, Italy


I see it give the correct answers!!!

eg when A1 =2 the first formula =12 and the second =4

-2 squared =4
So 4 plus 8 = 12

2 squared =4
So 8 - 4 = 4

8 - (2 squared) = 4

Yes, but the way Excel works is deceiving.

As a matter of fact, with reference to the general mathematical
principle A+B = B+A, let us have:
A= -A1^2
B = 8

Then, one would expect that -A1^2 + 8 is the same as 8 - A1^2, which
is not the way Excel works.

So just what were you expecting to get? Were you expecting -A1^2 to
really be -(A1^2)?

-Bruce

Be careful when using Excel
 

"Antonio Vernucci" wrote in message
. ..
In correct mathematical convention the expression
let A1 = 2 then

-A1^2 must yield
-A1^2 = -4

otherwise we would have to write
(-A1)^2 = 4

I share your opinion. But there are two Microsofts.

- for Microsoft#1, that is Excel: (-2^2 + 8) = 12

- for Microsoft#2, that is Visual Basic: (-2^2 + 8) = 4


at least they are doing what their help says they should do. in vb6
exponentiation is done before negation. in excel negation is done before
exponentiation. when in doubt use more parenthesis than you think you
should to be sure the calculation is done in the order you are expecting.

Be careful when using Excel
 

"Antonio Vernucci" wrote in
:

I think this message can be of interest for those using Excel for
antenna calculations.

Write in one cell: =(-A1^2 + 8) Note: the exponent of A1 is 2 (and
not 2+8=10) because Excel performs squaring before summing

Write in another cell: =(8 - A1^2)

Do you realise that the '-' in both expressions are quite different in
meaning, one is a unary negation operator, the other is the binary
subract operator.

No magic there, force the - to be a unary operator, and it behaves
consistently, eg 8+-2^2 gives 12.



They look pretty much the same

Not to a programmer!

Owen

But give A1 any non-zero value and see what happen.

73

Tony I0JX
Rome, Italy

Be careful when using Excel
 

Do you realise that the '-' in both expressions are quite different in
meaning, one is a unary negation operator, the other is the binary
subract operator.

I realise, but Microsoft apparently does not:

- for Excel: (-2^2 + 8) = 12
- for Visual Basic: (-2^2 + 8) = 4

They look pretty much the same
Not to a programmer!

I would expect that some programmers may get confused, due to the inconsistency
of software products of the same company.

Tony I0JX

Be careful when using Excel
 

"Antonio Vernucci" wrote in
:

Do you realise that the '-' in both expressions are quite different
in meaning, one is a unary negation operator, the other is the binary
subract operator.

I realise, but Microsoft apparently does not:

- for Excel: (-2^2 + 8) = 12
- for Visual Basic: (-2^2 + 8) = 4

They look pretty much the same
Not to a programmer!

I would expect that some programmers may get confused, due to the
inconsistency of software products of the same company.

Do not assume that all languages or all products use the same operator
precedence rules.

The Excel rules are spelled out in the Excel help.

The key thing is that there is a difference in the meaning of '-' in the
uses as the negation operator and subtraction operator, same symbol, two
meanings, you need to understand that when looking in the list of
operator precedence.

Excel spells out clearly that unary negation has higher precedence than
exponentiation, and subtraction is lower than both... so keep that in
mind when writing formulae.

If you are writing VB, use the precedence rules for VB. The VB rules
clearly state that exponentiation has higher precedence than negation,
and subtraction is lower. This may have its roots before the existence of
Microsoft.

Many languages do not have an exponentiation operator, requiring
functions instead (often pow()). Such use eliminates the operator
precedence issue.

PERL does have an exponentiation operator, and it is higher precedence
than unary + and -.

FORTRAN90 does have an exponentiation operator, and it is higher
precedence than unary + and -.

It is part of the programming landscape... algebraic expressions are not
universal, they need to be written for the specific language.

Now, I hear that you think that is a bad idea, but it isn't likely to
change. It is most unlikely that a new version of any language will
change operator precedence, it has too many issues.

Owen

Be careful when using Excel
 

"Antonio Vernucci" wrote in message
...
Do you realise that the '-' in both expressions are quite different in
meaning, one is a unary negation operator, the other is the binary
subract operator.

I realise, but Microsoft apparently does not:

- for Excel: (-2^2 + 8) = 12
- for Visual Basic: (-2^2 + 8) = 4

They look pretty much the same
Not to a programmer!

I would expect that some programmers may get confused, due to the
inconsistency of software products of the same company.

Tony I0JX

REAL programmers know the difference and add extra parenthesis just to be
sure the processor does what we want. for instance, i would NEVER use a
unary negate without adding parenthesis to be sure it was applied properly.
In most cases i would write that equation like: ((-1*A1)^2)+8, or even more
obvious in excel: power(-1*a1,2)+8

Be careful when using Excel
 

On Fri, 8 May 2009 18:36:08 +0200, "Antonio Vernucci"
wrote:

I think this message can be of interest for those using Excel for antenna
calculations.

Write in one cell: =(-A1^2 + 8) Note: the exponent of A1 is 2 (and not
2+8=10) because Excel performs squaring before summing

Write in another cell: =(8 - A1^2)

They look pretty much the same

But give A1 any non-zero value and see what happen.

73

Tony I0JX
Rome, Italy

It's an INTENTIONAL Microsoft bug. See:
"Formula Returns Unexpected Positive Value"
http://support.microsoft.com/kb/q132686/

Although this article applies to Excel 97 and earlier versions, the
problem is still there in later versions of Excel.

The article indicates that:
"The order of evaluation of operators dictates that a minus
sign (-) used as a negation operator (such as -1) is evaluated
before all other operators. "
which in my opinion, is wrong. The MS order of operations is:

: Range
space Intersection
, Union
- Negation
% Percentage
^ Exponentiation
* or / Multiplication or Division
+ or - Addition or Subtraction
& Text Operator
= = = Comparison Operators

Note that the negation (negative sign) operator comes before any
arithmetic operators.

I haven't checked how Open Office 3 and others do it. My guess(tm) is
that they all treat the negation operator the same as subtraction
which would be (correctly) evaluated AFTER exponentiation,
multiplication, and division.

--
Jeff Liebermann
150 Felker St #D http://www.LearnByDestroying.com
Santa Cruz CA 95060 http://802.11junk.com
Skype: JeffLiebermann AE6KS 831-336-2558

Be careful when using Excel
 

On Sat, 09 May 2009 16:48:19 +0000, Jim Higgins
wrote:

The proper precedence of mathematical operations is "PEMDAS," meaning
Parenthesis, Exponentiation, Mult/Div, Add/Sub.

Yep. The MS precidence is:
http://support.microsoft.com/kb/25189/EN-US/

: Range
space Intersection
, Union
- Negation
% Percentage
^ Exponentiation
* or / Multiplication or Division
+ or - Addition or Subtraction
& Text Operator
= = = Comparison Operators

Note that the negation (negative sign) operator comes before any
arithmetic operators. The problem comes from Excel inventing some
kind of distinction between negation (negative number) and
substraction. For arithmetic, there is none. For C programmers,
there is a difference (in the way the data is stored). More on the
subject:
http://mathforum.org/library/drmath/view/69058.html

That minus in front of the A1 is a unary negation, which is a
multiplication by minus 1, so it should be performed AFTER the
exponentiation, i.e; -A1^2 = -(A1^2)

Excel performs it before the exponentiation, i.e.; -A1^2 = (-A1)^2

The Excel answer is incorrect.

Yep. However, MS is not about to create problems by fixing the
problem. It would be a bad thing to have existing spreadsheets,
suddently give different results when run on updated and fixed
versions of Excel. Compatibility with old bugs is one reason that
bugs tend to be perpetuated. Old bugs and sleeping dogs should be
left alone.

73 de Jim, KB3PU

--
Jeff Liebermann
150 Felker St #D http://www.LearnByDestroying.com
Santa Cruz CA 95060 http://802.11junk.com
Skype: JeffLiebermann AE6KS 831-336-2558

Be careful when using Excel
 

"Jeff Liebermann" wrote in message
...
On Sat, 09 May 2009 16:48:19 +0000, Jim Higgins
wrote:

Yep. However, MS is not about to create problems by fixing the
problem. It would be a bad thing to have existing spreadsheets,
suddently give different results when run on updated and fixed
versions of Excel. Compatibility with old bugs is one reason that
bugs tend to be perpetuated. Old bugs and sleeping dogs should be
left alone.

73 de Jim, KB3PU


I sure am glad they finally fixed the simple calculator that Windows came
with. It had a major bug in it that if I remember correctly if you
substracted 3.1 from 3.11 you got zero. There were other numbers like that
also.

I think Intel had to recall a bunch of chips because of an error in the math
coprocessor part at one time.

Microsoft products are so full of 'problems' that if they ever put out an
error free product it would seem to be a mistake.

Be careful when using Excel
 

Jeff Liebermann wrote in
:

....
It's an INTENTIONAL Microsoft bug. See:

It is certainly popular to blame Microsoft with lots of things, whether
they were responsible or not matters little.

Keep in mind that Microsoft did not 'design' the algebraic operator
hierarchy for Excel, Excel was released with a claim of 100% cell formula
compatibility with the then leading spreadsheet Lotus 123. (Microsoft's
compatibility was so good, it was subject of a famous court case.)

It was much later that Microsoft conceived VBA and added it to their apps.
IIRC, Visual Basic for Applications inherits its algebraic operator
hierarchy from the BASIC language which was conceived around 1964 and
enriched progressively.

The "intentional Microsoft bug" perspective looks like just prejudice.

Owen