Excel Math Bug

[Jerry W. Lewis]

'Weekly claims of Excel Math bugs' that don't pan out,
wow, that's kind of amazing.

Weekly "mistaken" claims. Excel brings a great deal of power to people
who have limited understanding of numerical analysis issues.
Consequently many people think they have discovered "bugs in Excel"
because they haven't adequately thought about the issues and don't have
the expertise to discover that the same issues apply to essentially all
software and hardware.

Jerry

 

[Dana DeLouis]

We found another strange thing with double negatives.

It was something like "=--2" yielded a result of "5" or something

Hello. I can't think of anything off hand where "=--2", by itself, would
return 5.
Just some thoughts here: =3--2 returns 5 as expected. However, Excel will
accept extra "-" in the equation.

=3---2
returns 1, so a typo will cause a logic error on a worksheet.

In fact, Excel will accept "=3---------2". The merits of this is up to
debate. :>) One could argue either case. In mm, you can not do "3--2"
because the "--" is the "Decrement" operator on the 3, and you can't do this
on a number. The correct way is to include a space (3- -2), or better
3-(-2).

Another common use of "--" is to convert Boolean values to numbers. For
example:
=-(3>2) returns -1, and =--(3>2) returns +1. This is just a feature of
Excel.

HTH
Dana DeLouis

Yes Dana, that's it. Good deduction.

It cost me $2000, and a week, to find that out.

it happens directly after the leading equal sign or inside prens.

We found another strange thing with double negatives.
It was something like "=--2" yielded a result of "5" or something
along those lines. But we were in the middle of something
and I didn't write it down. Are you familiar with that one?

<snip>

 

[fred]

Don't you think Microsoft has legal consultants? A whole army of them!

Just to make sure they don't have to pay any money to people like you.

Of course they do, but why don't they just fix the problem.
I wasn't expecting Consequential damages, just to get paid for a bug.
Again, why don't they just fix the bug.

Actually you are very, *very* selfish. This way, nobody wins, and
surely not you and your client! Instead of keeping the information to
yourself, you should share it. Make your information free (as in
speech). Perhaps other people might benefit from your information. Make
this a nonzero equation. You should know what I mean, you and your
client have both done the relevant studies.

I agree with you to a point. For my first 15 years in computers I was that
way. My attitude has shifted. I just wasted resources on a bug. This
isn't a beta release. Office is not cheap. He's the richest man in the
world. I have to let MS software connect to the mother ship just to use it.
They crushed Wordperfect and Netscape. No, there's no more spirit of
community left. They've become the 900 lb gorilla. With a zillion users
why is this bug still in there. Are they rewriting math rules now? ...
negation is different from subtracting? Indeed! Not in any math book I've
ever seen.

Anyway, a claimed "bug" isn't a bug at all until it has been confirmed
as a bug. So give some more information please so that we can help you.

What we here in the ng are willing to do, even for free (as in beer),
is to help you find a workaround. And something else. If you had come
here sooner, you would have saved a lot of time and money!!!

It is a wonderful thing that people do this type of thing in NGs, I do it
myself. But I don't know why you don't understand that I feel let down.
I've spent thousands of hours developing Excel spreadsheets and I'm just
finding out about this? What other twists are in there? It should work as
expected, especially such a mature product. I didn't come to this NG
earlier because I was working under the assumption that Excel was working as
should be expected. Once I realized it wasn't, I did. The workaround was
simple though.

I'd really like to find out if this issue is on the list to be fixed, or if
it's some sort of "beginner user" *feature*.

 

[JE McGimpsey]

Then you need to disclose what you think the bug *is*...

 

[Alan]

Are they rewriting math rules now? ... negation is different from
subtracting? Indeed! Not in any math book I've ever seen.

This is an interesting point.

I guess I tend to view the two things as being separate and different
in nature.

Negation (in the sense that I believe you are using it above) such as
turning +5 into -5 'creates' a new number. The value of that number
is -5.

Subtraction is an operation performed on one value with another.
Therefore, subtracting +5 from a value is a different thing.

I guess where this becomes important is in the example used elsewhere
in this thread of:

=-5^2

It could be argued that it is quite valid for either answer (+25
or -25) to be correct depending on whether we are squaring -5 or
whether we are (implicitly) subtracting 5^2 from (the implicit) 0 that
is not shown.

If we put the zero in:

=0-5^2

then I would suggest that the *only* valid interpretation is that we
are subtracting 5^2 from zero, and thus the answer *must* be -25.

Given that excel must have rules, it does not seem unreasonable for
the algorithms to require an explicit subtraction, else go with the
interpretation that we are squaring -5.


Having said all of that, if we don't bother to use brackets to make
the calculations completely explicit and unambiguous, then I guess we
are each *choosing* to take the risk - whether we know it or not.

Does my thinking sound reasonable?


Alan.

 

[fred]

Well, yes, that is basically the same thing a couple of my friends said.
But none of them are mathematicians. The only thing about it is that that's
not how math/algebra/calculus/etc. is done. Negation and subtraction are
one-in-the-same to the best of my somewhat-in-depth knowledge. There's even
the mnemonic device "Please Excuse My Dear Aunt Sally" to help remember the
order in which operations are supposed to be executed (Parenthesis,
Exponents, Multipication, Division, Addition, Subtraction).

To me the only valid argument, based on different priorities, is that
non-math-people, when using actual numbers, not cell references, expected
=-5^2 to be "negative five, squared". And they wanted to satisfy the
secretaries and bean counters, not the heavy-duty math people.

However, having done software development myself, I think it most likely
that it's just a bug and just hasn't been fixed yet. In fact, to me,
putting in a zero and getting a different answer proves it's wrong.

 

[fred]

I did in another sub-thread. Dana was familiar with it already.

If you lead off with a negative sign it uses the negative value inside the
exponentiation.
So, instead of =-5^2 equalling -25 it equals 25.
but, =0-5^2 is calculated correctly as -25 even though it's mathematically
the same.

 

[fred]

no, not really, I just expected Excel to do math calculations the right way.
I couldn't really have expected him to know about the bug.
His math was correct.

 

[JE McGimpsey]

XL does have rules. A unary minus, or negation, operator has higher
precedence than the exponentiation operator, so

= -5^2

is unambiguously 25.

The exponentiation operator has a higher precedence than the subtraction
operator, so

=0-5^2

is unambiguously -25.

It's not a matter of reasonable or unreasonable interpretation - it's a
strictly mechanical parsing of the formulas.


See "The order in which Microsoft Excel performs operations in formulas"
in XL Help.

 

[fred]

You are 100% right!

It sounds like he doesn't want to tell his client he wasted 1 week and
2000$ on something that simply can be found in the help. So the easy
way out is to blame MS for everything.

Welll... this is in my help (in Dutch)

Operator Beschrijving
- Negatief maken (zoals in -1)
% Procent
^ Machtsverheffen
* en / Vermenigvuldigen en delen
+ en - Optellen en aftrekken
& Twee tekenreeksen aan elkaar koppelen
= < > <= >= <> Vergelijken

As you can see, a negative sign has precendence over power.
Unfortunately for Fred, a negative sign and a subtraction are
typographically identical. There is only one good solution: using
brackets.

This is in my help too:

Reference operators
- Negation (as in -1)
% Percent
^ Exponentiation
* and / Multiplication and division
+ and - Addition and subtraction
& Connects two strings of text (concatenation)
= < > <= >= <> Comparison

But to me it's immaterial. Why don't they follow the normal mathematical
order? If I'm going to sort a list of numbers should I be expected to look
up the sort order to see if they deviated from the norm? No.

 

[JE McGimpsey]

That's clearly explained in the XL Help topic "The order in which
Microsoft Excel performs operations in formulas".

In all the math I've ever done, from grade school on, negation and
subtraction have been separate operations (often, but not always, using
separate symbols, such as a hyphen for negation and an n-dash for
subtraction), so that -5^2 has always been interpreted to equal 25.

Personally, I'd take it up with your consultant, assuming that he/she
was working on the Excel model. That problem should have been a piece of
cake for someone with even moderate expertise to identify, from the sign
change alone! There's no way you should have to pay for 20 hours of
troubleshooting (at my rates, at least).

 

[fred]

Do any of you SCI.MATH whizes want to weigh in on this?

MS Excel calculates "=-5^2" as 25, not as -25.
This is because 'negation' is handled first in Excel. (!?)
If you put a zero in the equation,
as in "=0-5^2", your answer changes to -25.

Is this in line with standard math rules?
Is negation different than subtraction?

I'm getting a lot of comments in the Excel NG
basically saying that "it's in the help section, so too bad".

I've had lots of math and as far as I know
negation and subtraction are the same thing.

 

[Rainer Rosenthal]

MS Excel calculates "=-5^2" as 25, not as -25.

Is this in line with standard math rules?

No. If all were OK, they'd call this program

Excellent

but since it lacks something it's just called

Excel

--
Rainer Rosenthal, (e-mail address removed) _____________________
| _ | |
| (_) | Given A, P and a circle. Find B, C on the |
| A P | circle with P on BC and area(ABC)=maximum. |
|__________|___(Ingmar Rubin in de.sci.mathematik) ________|

 

[Dave Peterson]

And I'd ask that consultant to be more explicit with his formulas.

Make sure he includes ()'s.

=(3)+(2)
would be a little nutso, but
=(-5)^2
would be clear to me.

And these evaluate the same, but I'd use the second:
=3*5+4*7+8
=(3*5)+(4*7)+8

 

[Alan]

And I'd ask that consultant to be more explicit with his formulas.

Make sure he includes ()'s.

=(3)+(2)
would be a little nutso, but
=(-5)^2
would be clear to me.

And these evaluate the same, but I'd use the second:
=3*5+4*7+8
=(3*5)+(4*7)+8

To my mind, from a pragmatic perspective, Dave is correctly
articulating the crux of the problem.

Whether or not an expression is 'ambiguous' *to excel* is not in
dispute; it *is* (more generally) ambiguous if two reasonable
people could interpret it in different ways in good faith.

=-5^2 *is* ambiguous since it is *not* unreasonable for someone to
expect that to return -25 even though excel may be well documented
that it returns +25.


I believe that the responsibility here lies with the person who
creates the formula in the first place, and following from that,
whoever enters it in excel.

The originator of the formula should have written it completely
unambiguously in whichever form was applicable:

(-5)^2 OR -(5^2)

The person entering it into excel is then responsible for following
that through faithfully.


Surely discussion on whether someone should have used one order of
preference over another is less helpful than avoiding the problem in
the first place, and primary responsibility must lie with the
originator of the formulae to get their part correct and totally
unambiguous?

Alan.

 

[Alan Beban]

Do any of you SCI.MATH whizes want to weigh in on this?
. . .
I've had lots of math and as far as I know
negation and subtraction are the same thing.

Well, this is an Excel forum, so one should expect a programming point
of view. But if you search on mathematical notation generally, I think
negation is viewed as a unary operator, while subtraction is viewed as a
binary operator; and the discussions are not much clearer in that
context. My own view, not as a mathematician, is that the issue
revolving around how to evaluate -1^2 depends on some *order of
precedence*, and is totally conventional as to negation and exponientation.

Alan Beban

 

[Stephen J. Herschkorn]

Do any of you SCI.MATH whizes want to weigh in on this?

MS Excel calculates "=-5^2" as 25, not as -25.
This is because 'negation' is handled first in Excel. (!?)
If you put a zero in the equation,
as in "=0-5^2", your answer changes to -25.

Is this in line with standard math rules?
Is negation different than subtraction?

I'm getting a lot of comments in the Excel NG
basically saying that "it's in the help section, so too bad".

I've had lots of math and as far as I know
negation and subtraction are the same thing.

Well, this is an Excel forum, so one should expect a programming point
of view. But if you search on mathematical notation generally, I
think negation is viewed as a unary operator, while subtraction is
viewed as a binary operator; and the discussions are not much clearer
in that context. My own view, not as a mathematician, is that the
issue revolving around how to evaluate -1^2 depends on some *order of
precedence*, and is totally conventional as to negation and
exponientation.

BOULDERDASH!!! This is a horrible bug in Excel (whereof I was
previously unaware). It is very standard that exponentiaion takes
precedence over negation. Ask any semi-decent high school student to
draw a graph of y = -x^2, and what will you get?

Stating it is a documented convention is not a legitimate argument.
What if Microsoft(R) buried in its documentation that addition takes
precedence over multiplication? That the spell checker would always
change word "friend" to "freind"? That the sum function adds only every
other term? That using a "q" in one of its products would cause the
system to reboot? These effects would be just as valid by this logic.

I have sent this comment to Microsoft(R), though I expect no good to
come of it.

 

[Dana DeLouis]

Hi Fred. I think it's just one of those things one must be aware of.
That's all. All programs are different. Whenever a formula starts with a
"-", it always triggers in my mind that () are probably needed.

It's hard to compare both programs when they are so different. (After all,
mm has a few pages of "Operator precedence's." This is off topic I know,
but the reason mm does -5^2 correctly is that "Times" (or multiplication)
has lower precedence that Power. This is different then what we think of
when using Excel and "Negation." For discussion only, here is a complex
number problem: -(3 + 4*I)^2. The term "Negation" wouldn't make sense
here. Excel is not set up to do this type of problem, so that is why it's
not fair to compare the two programs. Here's one way to do it in Excel:

=IMPRODUCT(-1,IMPOWER( COMPLEX(3,4),2))

Returns:
7-24i

(now you see why Times is used and not a "negation")

Which checks:
-(3 + 4*I)^2
7 - 24*I

For curiosity, we put a hold on the evaluation:
HoldForm[-(3 + 4*I)^2]

Times[-1, Power[Plus[3, Times[4, \[ImaginaryI]]], 2]]

We see that we needed to do something very similar in Excel to get the same
answer. :>)

Anyway, off topic I know. Just some thoughts. :>)

Dana DeLouis

 

[fred]

BOULDERDASH!!! This is a horrible bug in Excel (whereof I was
previously unaware). It is very standard that exponentiaion takes
precedence over negation. Ask any semi-decent high school student to
draw a graph of y = -x^2, and what will you get?

Thank you sir!

As another poster noted, M.P.E.P is a programming forum, but told that it
violates math convention, they still argue. They probably just didn't
believe me.

Another said "much ado about nothing", but I think this is a horrible bug
too. Excel should at least follow regular math conventions. What other
surprizes await?!

 

[Lance Lamboy]

Thank you sir!

As another poster noted, M.P.E.P is a programming forum, but told that
it violates math convention, they still argue. They probably just
didn't believe me.

Another said "much ado about nothing", but I think this is a horrible
bug too. Excel should at least follow regular math conventions. What
other surprizes await?!

I checked my copy of oocalc. (I don't use M$ products.) Much to my
chagrin it exhibited the same Excel bug.