Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 26
Default Excel Math Bug

I paid a consultant for a bunch of formulas
and built a spreadsheet with them.
The results didn't match those of the consultant.
I thought some formulas must have been keyed in wrong, so we
went over everything 15 times. Days later, that was ruled out.

Next, we suspected the consultants math.
After several consultant-hours we nailed it down to
a math bug in Excel.

After nearly a decade I can't believe it still exists.
I tried it in Excel 2002 and 2003 -- both have the bug.
It's not an everyday thing,
but it's not rare or esoteric either.
When consultant found it, he was shocked and said
they "should be sued".
It's not arguable -- it's wrong, it's a bug.

Since it cost me $2000 to find it, I'd like to know
if Microsoft has any kind of pay-for-bugs programs.
Or maybe magazines that would buy this info.

Any ideas for how I can get reimbursed?

Fred


  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 7,247
Default Excel Math Bug

Fred,

Perhaps you could provide an example of the bug?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"fred" wrote in message
...
I paid a consultant for a bunch of formulas
and built a spreadsheet with them.
The results didn't match those of the consultant.
I thought some formulas must have been keyed in wrong, so we
went over everything 15 times. Days later, that was ruled out.

Next, we suspected the consultants math.
After several consultant-hours we nailed it down to
a math bug in Excel.

After nearly a decade I can't believe it still exists.
I tried it in Excel 2002 and 2003 -- both have the bug.
It's not an everyday thing,
but it's not rare or esoteric either.
When consultant found it, he was shocked and said
they "should be sued".
It's not arguable -- it's wrong, it's a bug.

Since it cost me $2000 to find it, I'd like to know
if Microsoft has any kind of pay-for-bugs programs.
Or maybe magazines that would buy this info.

Any ideas for how I can get reimbursed?

Fred




  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,885
Default Excel Math Bug

Hi
in addition to Chip. Are your sure this isn't just a rounding issue?
Bus as Chip said please provide an example

--
Regards
Frank Kabel
Frankfurt, Germany


fred wrote:
I paid a consultant for a bunch of formulas
and built a spreadsheet with them.
The results didn't match those of the consultant.
I thought some formulas must have been keyed in wrong, so we
went over everything 15 times. Days later, that was ruled out.

Next, we suspected the consultants math.
After several consultant-hours we nailed it down to
a math bug in Excel.

After nearly a decade I can't believe it still exists.
I tried it in Excel 2002 and 2003 -- both have the bug.
It's not an everyday thing,
but it's not rare or esoteric either.
When consultant found it, he was shocked and said
they "should be sued".
It's not arguable -- it's wrong, it's a bug.

Since it cost me $2000 to find it, I'd like to know
if Microsoft has any kind of pay-for-bugs programs.
Or maybe magazines that would buy this info.

Any ideas for how I can get reimbursed?

Fred

  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 923
Default Excel Math Bug

Chip, Sounds like Fred want to keep it to himself and get paid first! No
doubt he believes it exists but I guess someones got to prove it for real?

Cheers
Nigel


"Chip Pearson" wrote in message
...
Fred,

Perhaps you could provide an example of the bug?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"fred" wrote in message
...
I paid a consultant for a bunch of formulas
and built a spreadsheet with them.
The results didn't match those of the consultant.
I thought some formulas must have been keyed in wrong, so we
went over everything 15 times. Days later, that was ruled out.

Next, we suspected the consultants math.
After several consultant-hours we nailed it down to
a math bug in Excel.

After nearly a decade I can't believe it still exists.
I tried it in Excel 2002 and 2003 -- both have the bug.
It's not an everyday thing,
but it's not rare or esoteric either.
When consultant found it, he was shocked and said
they "should be sued".
It's not arguable -- it's wrong, it's a bug.

Since it cost me $2000 to find it, I'd like to know
if Microsoft has any kind of pay-for-bugs programs.
Or maybe magazines that would buy this info.

Any ideas for how I can get reimbursed?

Fred






  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,651
Default Excel Math Bug

On Sat, 31 Jul 2004 18:41:57 GMT, "fred" wrote:

After nearly a decade I can't believe it still exists.
I tried it in Excel 2002 and 2003 -- both have the bug.
It's not an everyday thing,
but it's not rare or esoteric either.


Virtually every time a message with "math bug" in the subject has been posted
here, the problem has turned on a lack of understanding of Excel's published
specifications and limitations, rather than a true bug.

Did your consultant know that you were going to be using his formulas in an
Excel workbook? And did your contract reflect that?


--ron


  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 27,285
Default Excel Math Bug

http://support.microsoft.com/default...13&Product=xlw
XL: Floating-Point Arithmetic May Give Inaccurate Results

http://support.microsoft.com/default...18&Product=xlw
How to correct rounding errors in floating-point arithmetic

if these are the type of problems you are having, then your consultant needs
to provide formulas that do not accumulate rounding error.

--
Regards,
Tom Ogilvy


"fred" wrote in message
...
I paid a consultant for a bunch of formulas
and built a spreadsheet with them.
The results didn't match those of the consultant.
I thought some formulas must have been keyed in wrong, so we
went over everything 15 times. Days later, that was ruled out.

Next, we suspected the consultants math.
After several consultant-hours we nailed it down to
a math bug in Excel.

After nearly a decade I can't believe it still exists.
I tried it in Excel 2002 and 2003 -- both have the bug.
It's not an everyday thing,
but it's not rare or esoteric either.
When consultant found it, he was shocked and said
they "should be sued".
It's not arguable -- it's wrong, it's a bug.

Since it cost me $2000 to find it, I'd like to know
if Microsoft has any kind of pay-for-bugs programs.
Or maybe magazines that would buy this info.

Any ideas for how I can get reimbursed?

Fred




  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 837
Default Excel Math Bug

To expand on Ron's comment, basic math, exponential, log, and trig
functions are almost surely done by the CPU, rather than Excel
reinventing the wheel. It is extremely unlikely that there is a math
bug here, though on average there are 1-2 such mistaken claims per week
from people who don't understand the implications of finite precision
binary math (done by almost all software and hardware, not just Excel).

Another common problem is that text digits that look like a number in
input cells will either be ignored or cause an error, except for the few
cases where Excel's evaluation rules would coerce the text to a number.

Probability distribution functions (including ERF() and ERFC()), Bessel
functions and GAMMALN() were implemented in Excel using inferior
algorithms, but I don't immediately recall any other algorithm issues
that have not been improved in 2003.

If the previous paragraph does not cover your formula issues, then
rather than a "math bug", it is much more likely that either your
formula is numerically unstable or you have made some kind of mistake in
using it. We cannot help you diagnose this until you provide details.

Jerry

fred wrote:

I paid a consultant for a bunch of formulas
and built a spreadsheet with them.
The results didn't match those of the consultant.
I thought some formulas must have been keyed in wrong, so we
went over everything 15 times. Days later, that was ruled out.

Next, we suspected the consultants math.
After several consultant-hours we nailed it down to
a math bug in Excel.

After nearly a decade I can't believe it still exists.
I tried it in Excel 2002 and 2003 -- both have the bug.
It's not an everyday thing,
but it's not rare or esoteric either.
When consultant found it, he was shocked and said
they "should be sued".
It's not arguable -- it's wrong, it's a bug.

Since it cost me $2000 to find it, I'd like to know
if Microsoft has any kind of pay-for-bugs programs.
Or maybe magazines that would buy this info.

Any ideas for how I can get reimbursed?

Fred


  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 26
Default Excel Math Bug

Well, I'd really like to get reimbursed for this.

If I post the bug here the chances of that drop to near zero.
I will say this about it though:
If you consider calculating the wrong sign a math bug,
then this is a math bug. In certain situations, that is the problem.

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

I think you'll all agree with me on this though, I've had 4 years of college
calculus etc, and the consultant has a degree in physics. And Mathematica
calculates the formula differently (the right way).



"Jerry W. Lewis" wrote in message
...
To expand on Ron's comment, basic math, exponential, log, and trig
functions are almost surely done by the CPU, rather than Excel
reinventing the wheel. It is extremely unlikely that there is a math
bug here, though on average there are 1-2 such mistaken claims per week
from people who don't understand the implications of finite precision
binary math (done by almost all software and hardware, not just Excel).

Another common problem is that text digits that look like a number in
input cells will either be ignored or cause an error, except for the few
cases where Excel's evaluation rules would coerce the text to a number.

Probability distribution functions (including ERF() and ERFC()), Bessel
functions and GAMMALN() were implemented in Excel using inferior
algorithms, but I don't immediately recall any other algorithm issues
that have not been improved in 2003.

If the previous paragraph does not cover your formula issues, then
rather than a "math bug", it is much more likely that either your
formula is numerically unstable or you have made some kind of mistake in
using it. We cannot help you diagnose this until you provide details.

Jerry

fred wrote:

I paid a consultant for a bunch of formulas
and built a spreadsheet with them.
The results didn't match those of the consultant.
I thought some formulas must have been keyed in wrong, so we
went over everything 15 times. Days later, that was ruled out.

Next, we suspected the consultants math.
After several consultant-hours we nailed it down to
a math bug in Excel.

After nearly a decade I can't believe it still exists.
I tried it in Excel 2002 and 2003 -- both have the bug.
It's not an everyday thing,
but it's not rare or esoteric either.
When consultant found it, he was shocked and said
they "should be sued".
It's not arguable -- it's wrong, it's a bug.

Since it cost me $2000 to find it, I'd like to know
if Microsoft has any kind of pay-for-bugs programs.
Or maybe magazines that would buy this info.

Any ideas for how I can get reimbursed?

Fred




  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 863
Default Excel Math Bug

I highly doubt you'll get any reimbursement. Excel is probably operating
according to it's specs.


On Sun, 01 Aug 2004 22:14:47 GMT, "fred" wrote:

Well, I'd really like to get reimbursed for this.

If I post the bug here the chances of that drop to near zero.
I will say this about it though:
If you consider calculating the wrong sign a math bug,
then this is a math bug. In certain situations, that is the problem.

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

I think you'll all agree with me on this though, I've had 4 years of college
calculus etc, and the consultant has a degree in physics. And Mathematica
calculates the formula differently (the right way).



"Jerry W. Lewis" wrote in message
...
To expand on Ron's comment, basic math, exponential, log, and trig
functions are almost surely done by the CPU, rather than Excel
reinventing the wheel. It is extremely unlikely that there is a math
bug here, though on average there are 1-2 such mistaken claims per week
from people who don't understand the implications of finite precision
binary math (done by almost all software and hardware, not just Excel).

Another common problem is that text digits that look like a number in
input cells will either be ignored or cause an error, except for the few
cases where Excel's evaluation rules would coerce the text to a number.

Probability distribution functions (including ERF() and ERFC()), Bessel
functions and GAMMALN() were implemented in Excel using inferior
algorithms, but I don't immediately recall any other algorithm issues
that have not been improved in 2003.

If the previous paragraph does not cover your formula issues, then
rather than a "math bug", it is much more likely that either your
formula is numerically unstable or you have made some kind of mistake in
using it. We cannot help you diagnose this until you provide details.

Jerry

fred wrote:

I paid a consultant for a bunch of formulas
and built a spreadsheet with them.
The results didn't match those of the consultant.
I thought some formulas must have been keyed in wrong, so we
went over everything 15 times. Days later, that was ruled out.

Next, we suspected the consultants math.
After several consultant-hours we nailed it down to
a math bug in Excel.

After nearly a decade I can't believe it still exists.
I tried it in Excel 2002 and 2003 -- both have the bug.
It's not an everyday thing,
but it's not rare or esoteric either.
When consultant found it, he was shocked and said
they "should be sued".
It's not arguable -- it's wrong, it's a bug.

Since it cost me $2000 to find it, I'd like to know
if Microsoft has any kind of pay-for-bugs programs.
Or maybe magazines that would buy this info.

Any ideas for how I can get reimbursed?

Fred




  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,885
Default Excel Math Bug

Hi
I don't think MS will re-imburse you (I would assume the EULA aggrement
you signed will exclude this kind of payment/reimburesement).

To give you an example for this: Excel 2003 introduced a RAND() error
(sometimes returning a negative value). This error was admitted by MS
but I doubt anybode got money for this (even if their calculation
models were affected).

So the only 'benefit' you probably could get is post this bug to the
public / this NG, and at least got a confirmation that it is REALLY a
bug (and not an already know issue or just a simple Excel restriction)


--
Regards
Frank Kabel
Frankfurt, Germany


fred wrote:
Well, I'd really like to get reimbursed for this.

If I post the bug here the chances of that drop to near zero.
I will say this about it though:
If you consider calculating the wrong sign a math bug,
then this is a math bug. In certain situations, that is the problem.

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

I think you'll all agree with me on this though, I've had 4 years of
college calculus etc, and the consultant has a degree in physics.
And Mathematica calculates the formula differently (the right way).



"Jerry W. Lewis" wrote in message
...
To expand on Ron's comment, basic math, exponential, log, and trig
functions are almost surely done by the CPU, rather than Excel
reinventing the wheel. It is extremely unlikely that there is a

math
bug here, though on average there are 1-2 such mistaken claims per
week from people who don't understand the implications of finite
precision binary math (done by almost all software and hardware, not
just Excel).

Another common problem is that text digits that look like a number

in
input cells will either be ignored or cause an error, except for the
few cases where Excel's evaluation rules would coerce the text to a
number.

Probability distribution functions (including ERF() and ERFC()),
Bessel functions and GAMMALN() were implemented in Excel using
inferior algorithms, but I don't immediately recall any other
algorithm issues that have not been improved in 2003.

If the previous paragraph does not cover your formula issues, then
rather than a "math bug", it is much more likely that either your
formula is numerically unstable or you have made some kind of
mistake in using it. We cannot help you diagnose this until you
provide details.

Jerry

fred wrote:

I paid a consultant for a bunch of formulas
and built a spreadsheet with them.
The results didn't match those of the consultant.
I thought some formulas must have been keyed in wrong, so we
went over everything 15 times. Days later, that was ruled out.

Next, we suspected the consultants math.
After several consultant-hours we nailed it down to
a math bug in Excel.

After nearly a decade I can't believe it still exists.
I tried it in Excel 2002 and 2003 -- both have the bug.
It's not an everyday thing,
but it's not rare or esoteric either.
When consultant found it, he was shocked and said
they "should be sued".
It's not arguable -- it's wrong, it's a bug.

Since it cost me $2000 to find it, I'd like to know
if Microsoft has any kind of pay-for-bugs programs.
Or maybe magazines that would buy this info.

Any ideas for how I can get reimbursed?

Fred




  #11   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 27,285
Default Excel Math Bug

Precedence of operations is stated in the help. As far as I know, there is
no universally accepted order of precedence that covers all combinations and
permutations.

Again, sounds like your beef is with your consultant if you were paying for
his/her expertise in Excel.

--
Regards,
Tom Ogilvy

"fred" wrote in message
...
Well, I'd really like to get reimbursed for this.

If I post the bug here the chances of that drop to near zero.
I will say this about it though:
If you consider calculating the wrong sign a math bug,
then this is a math bug. In certain situations, that is the problem.

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

I think you'll all agree with me on this though, I've had 4 years of

college
calculus etc, and the consultant has a degree in physics. And Mathematica
calculates the formula differently (the right way).



"Jerry W. Lewis" wrote in message
...
To expand on Ron's comment, basic math, exponential, log, and trig
functions are almost surely done by the CPU, rather than Excel
reinventing the wheel. It is extremely unlikely that there is a math
bug here, though on average there are 1-2 such mistaken claims per week
from people who don't understand the implications of finite precision
binary math (done by almost all software and hardware, not just Excel).

Another common problem is that text digits that look like a number in
input cells will either be ignored or cause an error, except for the few
cases where Excel's evaluation rules would coerce the text to a number.

Probability distribution functions (including ERF() and ERFC()), Bessel
functions and GAMMALN() were implemented in Excel using inferior
algorithms, but I don't immediately recall any other algorithm issues
that have not been improved in 2003.

If the previous paragraph does not cover your formula issues, then
rather than a "math bug", it is much more likely that either your
formula is numerically unstable or you have made some kind of mistake in
using it. We cannot help you diagnose this until you provide details.

Jerry

fred wrote:

I paid a consultant for a bunch of formulas
and built a spreadsheet with them.
The results didn't match those of the consultant.
I thought some formulas must have been keyed in wrong, so we
went over everything 15 times. Days later, that was ruled out.

Next, we suspected the consultants math.
After several consultant-hours we nailed it down to
a math bug in Excel.

After nearly a decade I can't believe it still exists.
I tried it in Excel 2002 and 2003 -- both have the bug.
It's not an everyday thing,
but it's not rare or esoteric either.
When consultant found it, he was shocked and said
they "should be sued".
It's not arguable -- it's wrong, it's a bug.

Since it cost me $2000 to find it, I'd like to know
if Microsoft has any kind of pay-for-bugs programs.
Or maybe magazines that would buy this info.

Any ideas for how I can get reimbursed?

Fred






  #12   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 863
Default Excel Math Bug

Again, sounds like your beef is with your consultant if you were paying for
his/her expertise in Excel.


I agree!
  #13   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 81
Default Excel Math Bug

Well, I'd really like to get reimbursed for this.
If I post the bug here the chances of that drop to near

zero.

Can't drop below what they already are.

I think you'll all agree with me on this though, I've had
4 years of college calculus etc, and the consultant has a
degree in physics. And Mathematica
calculates the formula differently (the right way).


Google "calculus excel limitations" gives me 6880 hits.

Peter
  #14   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 634
Default Excel Math Bug

You've had all this in various posts/links, but it was a nice summary posted by
others previously that I have kept. The difference between Excel and
Mathematica is also referred to in the text:-

Excel will round all numbers to 15 significant figures. Anything over and
above this will be rounded to 0. If the data needs to be entered as for example
a credit card number, you need to precede the entry
with an apostrophe or format the cell as text before you enter the data. You can
still do calculations against a number entered as text BUT it will only use 15
significant figures in the calculation, so that doesn't buy you anything extra
doing it that way.


A slightly edited (To generalise the response only), but very comprehensive
answer to a similar question was posted by Chip Pearson - Reproduced
below in it's entirety:-

--------------------------------------------------------------------------------
--------------------

As you have noticed Excel handles only 15 digits of precision.
The reason is that Excel, like many other computer programs, uses
the IEEE (Institute of Electrical and Electronic Engineers) Double
Precision Floating Point number format as the most accurate
representation of a number. You can read more about this at
www.cpearson.com/excel/rounding.htm , but in an oversimplified
form, it stores numbers as

N = Integer + X*(1/2) + X*(1/4) + X*(1/8) + X*(1/16) +.....+
X*(1/2^51)

where each X is either 1 or 0. In binary format, there are 51
digits to the right of the decimal point. In decimal form, 2^51 is
about equal to 10^15, which is why you get approximately 15 digits
of precision.

Unless a fractional number can be expressed *exactly* as the sum of
1/2 + 1/4 + 1/8 + ... + 1/(2^51) it will be stored as an
approximation. This is not unique to computers. Using a finite
number of decimal places, you cannot accurately store the number
1/3. You can store it as an approximation, like 0.3 or 0.33 or
0.33333333333333 but at some point you're rounding the true value
1/3, and 0.33333333333...+0.33333333333...+0.33333333333... does
NOT equal 1. It equal 0.999999999999...... which is decidedly not
1.

This is a fact of life in computers and in the real world, and in
the realm in which the two coincide.

But what about the rest of the decimal places, and how, if at all,
can I achieve more precision?


You can *display* a number to as many decimal places as you want,
but anything past 15 is no man's land. Within Excel there is no
way to achieve additional precision. Errors in rounding can
compound, so that rounding error in one formula is compounded when
the rounded error is used by other formulas, which themselves
round.

Some computer programs use other representations of numbers, but
these programs trade performance and compatibility for precision.
Additional precision comes at the cost of performance and
compatibility with other programs. For example, a program that
stored numbers to 100 digits of precision would use a different
encoding scheme, and its data would not be compatible with the
majority of computer programs. The IEEE Double Precision standard
provides a universal format that is "good enough" for the vast
majority of uses. Not all, but most. For good reason, MS chose
years ago to use IEEE Doubles for Excel.

Can you recommend a non-Excel app that offers higher precision?


Dedicated mathematical programs like Matlab and Mathamatica can
provide much greater precision, but those results aren't compatible
with most other computer programs.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


--------------------------------------------------------------------------------
------------------------

For a calculator that will support more than 15 digits, Jerry W. Lewis has
given the following info and link:-


The decimal data type gives 28 figure if you don't need exponents and
don't mind VBA programing. I think the Windows calculator uses the same
data type.

A free quad precision (64 digit) calculator can be downloaded from
http://www.crbond.com/applications.htm
unless it has been updated, it does not support cut/copy/paste.

I think some extended precision routines using VBA and strings have been
published for Excel - search the Google archives.

In Maple, Mathematica, Matlab, Rexx, etc., you can specify the number of
output figures you want.


Jerry W. Lewis

--------------------------------------------------------------------------------
--------------------------

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"fred" wrote in message
...
Well, I'd really like to get reimbursed for this.

If I post the bug here the chances of that drop to near zero.
I will say this about it though:
If you consider calculating the wrong sign a math bug,
then this is a math bug. In certain situations, that is the problem.

<snip


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.732 / Virus Database: 486 - Release Date: 29/07/2004


  #15   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 690
Default Excel Math Bug

...the wrong sign a math bug,
...And Mathematica calculates the formula differently (the right way).


Just guessing here. The only thing that comes to mind of hand might be the
following:

In Excel:
=-5^2
returns +25.

and in mm:
-5^2
-25

A beginning "-" is a "known" little issue with Excel. I think Excel handles
negation a little differently.

Subtracting from zero changes the answer in Excel.
Excel:
=0-5^2
-25

But stays the same in mm:
0-5^2
-25

Just a wild guess of course. I think we all want to see and learn from the
issue you encountered.
HTH
Dana DeLouis


"fred" wrote in message
...
Well, I'd really like to get reimbursed for this.

If I post the bug here the chances of that drop to near zero.
I will say this about it though:
If you consider calculating the wrong sign a math bug,
then this is a math bug. In certain situations, that is the problem.

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

I think you'll all agree with me on this though, I've had 4 years of

college
calculus etc, and the consultant has a degree in physics. And Mathematica
calculates the formula differently (the right way).



"Jerry W. Lewis" wrote in message
...
To expand on Ron's comment, basic math, exponential, log, and trig
functions are almost surely done by the CPU, rather than Excel
reinventing the wheel. It is extremely unlikely that there is a math
bug here, though on average there are 1-2 such mistaken claims per week
from people who don't understand the implications of finite precision
binary math (done by almost all software and hardware, not just Excel).

Another common problem is that text digits that look like a number in
input cells will either be ignored or cause an error, except for the few
cases where Excel's evaluation rules would coerce the text to a number.

Probability distribution functions (including ERF() and ERFC()), Bessel
functions and GAMMALN() were implemented in Excel using inferior
algorithms, but I don't immediately recall any other algorithm issues
that have not been improved in 2003.

If the previous paragraph does not cover your formula issues, then
rather than a "math bug", it is much more likely that either your
formula is numerically unstable or you have made some kind of mistake in
using it. We cannot help you diagnose this until you provide details.

Jerry

fred wrote:

I paid a consultant for a bunch of formulas
and built a spreadsheet with them.
The results didn't match those of the consultant.
I thought some formulas must have been keyed in wrong, so we
went over everything 15 times. Days later, that was ruled out.

Next, we suspected the consultants math.
After several consultant-hours we nailed it down to
a math bug in Excel.

After nearly a decade I can't believe it still exists.
I tried it in Excel 2002 and 2003 -- both have the bug.
It's not an everyday thing,
but it's not rare or esoteric either.
When consultant found it, he was shocked and said
they "should be sued".
It's not arguable -- it's wrong, it's a bug.

Since it cost me $2000 to find it, I'd like to know
if Microsoft has any kind of pay-for-bugs programs.
Or maybe magazines that would buy this info.

Any ideas for how I can get reimbursed?

Fred








  #16   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 26
Default Excel Math Bug

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?


"Dana DeLouis" wrote in message
...
...the wrong sign a math bug,
...And Mathematica calculates the formula differently (the right way).


Just guessing here. The only thing that comes to mind of hand might be

the
following:

In Excel:
=-5^2
returns +25.

and in mm:
-5^2
-25

A beginning "-" is a "known" little issue with Excel. I think Excel

handles
negation a little differently.

Subtracting from zero changes the answer in Excel.
Excel:
=0-5^2
-25

But stays the same in mm:
0-5^2
-25

Just a wild guess of course. I think we all want to see and learn from

the
issue you encountered.
HTH
Dana DeLouis


"fred" wrote in message
...
Well, I'd really like to get reimbursed for this.

If I post the bug here the chances of that drop to near zero.
I will say this about it though:
If you consider calculating the wrong sign a math bug,
then this is a math bug. In certain situations, that is the problem.

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

I think you'll all agree with me on this though, I've had 4 years of

college
calculus etc, and the consultant has a degree in physics. And

Mathematica
calculates the formula differently (the right way).



"Jerry W. Lewis" wrote in message
...
To expand on Ron's comment, basic math, exponential, log, and trig
functions are almost surely done by the CPU, rather than Excel
reinventing the wheel. It is extremely unlikely that there is a math
bug here, though on average there are 1-2 such mistaken claims per

week
from people who don't understand the implications of finite precision
binary math (done by almost all software and hardware, not just

Excel).

Another common problem is that text digits that look like a number in
input cells will either be ignored or cause an error, except for the

few
cases where Excel's evaluation rules would coerce the text to a

number.

Probability distribution functions (including ERF() and ERFC()),

Bessel
functions and GAMMALN() were implemented in Excel using inferior
algorithms, but I don't immediately recall any other algorithm issues
that have not been improved in 2003.

If the previous paragraph does not cover your formula issues, then
rather than a "math bug", it is much more likely that either your
formula is numerically unstable or you have made some kind of mistake

in
using it. We cannot help you diagnose this until you provide details.

Jerry

fred wrote:

I paid a consultant for a bunch of formulas
and built a spreadsheet with them.
The results didn't match those of the consultant.
I thought some formulas must have been keyed in wrong, so we
went over everything 15 times. Days later, that was ruled out.

Next, we suspected the consultants math.
After several consultant-hours we nailed it down to
a math bug in Excel.

After nearly a decade I can't believe it still exists.
I tried it in Excel 2002 and 2003 -- both have the bug.
It's not an everyday thing,
but it's not rare or esoteric either.
When consultant found it, he was shocked and said
they "should be sued".
It's not arguable -- it's wrong, it's a bug.

Since it cost me $2000 to find it, I'd like to know
if Microsoft has any kind of pay-for-bugs programs.
Or maybe magazines that would buy this info.

Any ideas for how I can get reimbursed?

Fred







  #17   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 102
Default Excel Math Bug

fred wrote:

I paid a consultant for a bunch of formulas
and built a spreadsheet with them.
The results didn't match those of the consultant.
I thought some formulas must have been keyed in wrong, so we
went over everything 15 times. Days later, that was ruled out.

Next, we suspected the consultants math.
After several consultant-hours we nailed it down to
a math bug in Excel.

After nearly a decade I can't believe it still exists.
I tried it in Excel 2002 and 2003 -- both have the bug.
It's not an everyday thing,
but it's not rare or esoteric either.
When consultant found it, he was shocked and said
they "should be sued".
It's not arguable -- it's wrong, it's a bug.

Since it cost me $2000 to find it, I'd like to know
if Microsoft has any kind of pay-for-bugs programs.
Or maybe magazines that would buy this info.

Any ideas for how I can get reimbursed?

Fred


Fred,

From the EULA:

18. EXCLUSION OF INCIDENTAL,CONSEQUENTIAL AND CERTAIN OTHER DAMAGES. TO
THE MAXIMUM EXTENT PERMITTED BY APPLICABLE LAW, IN NO EVENT SHALL
MICROSOFT OR ITS SUPPLIERS BE LIABLE FOR ANY SPECIAL, INCIDENTAL,
PUNITIVE, INDIRECT, OR CONSEQUENTIAL BUT NOT LIMITED TO, DAMAGES FOR
LOSS OF PROFITS OR CONFIDENTIAL OR OTHER INFORMATION, FOR BUSINESS
INTERRUPTION, FOR PERSONAL INJURY, FOR LOSS OF PRIVACY, FOR FAILURE TO
MEET ANY DUTY INCLUDING OF GOOD FAITH OR OF REASONABLE CARE, FOR
NEGLIGENCE, AND FOR ANY OTHER PECUNIARY OR OTHER LOSS
WHATSOEVER)ARISING OUT OF OR IN ANY WAY RELATED TO THE USE OF OR
INABILITY TO USE THE SOFTWARE, THE PROVISION OF OR FAILURE TO PROVIDE
SUPPORT OR OTHER SERVICES, INFORMATON, SOFTWARE, AND RELATED CONTENT
THROUGH THE SOFTWARE OR OTHERWISE ARISING OUT OF THE USE OF THE
SOFTWARE, OR OTHERWISE UNDER OR IN CONNECTION WITH ANY PROVISION OF
THIS EULA, EVEN IN THE EVENT OF THE FAULT, TORT (INCLUDING NEGLIGENCE),
MISREPRESENTATION, STRICT LIABILITY, BREACH OF CONTRACT OR BREACH OF
WARRANTY OF MICROSOFT OR ANY SUPPLIER, AND EVEN IF MICROSOFT OR ANY
SUPPLIER HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.

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.

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.

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!!!

--
Amedee Van Gasse using XanaNews 1.16.3.1
If it has an "X" in the name, it must be Linux?

  #18   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 102
Default Excel Math Bug

(reading order fixed)

Fred wrote:

...the wrong sign a math bug,
...And Mathematica calculates the formula differently (the right
way).


Dana DeLouis wrote:


Just guessing here. The only thing that comes to mind of hand
might be the following:

In Excel:
=-5^2
returns +25.

and in mm:
-5^2
-25

A beginning "-" is a "known" little issue with Excel. I think Excel
handles negation a little differently.

Subtracting from zero changes the answer in Excel.
Excel:
=0-5^2
-25

But stays the same in mm:
0-5^2
-25


fred wrote:

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?


This sounds like a documented feature of Excel. So no money.
The workaround is simple: use brackets.

=-5^2
returns +25

=(-5)^2
returns +25

=-(5^2)
returns +25

A math "bug" indeed, but a small one. It took me 2 seconds to find a
workaround. Implementing it for your client might take longer, bu
surely not a whole week.

In short: "Much ado about nothing."

--
Amedee Van Gasse using XanaNews 1.16.3.1
If it has an "X" in the name, it must be Linux?
Please don't thank me in advance. Thank me afterwards if it works or
hit me in the face if it doesn't. ;-)
  #19   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 102
Default Excel Math Bug

Tom Ogilvy wrote:

Precedence of operations is stated in the help. As far as I know,
there is no universally accepted order of precedence that covers all
combinations and permutations.

Again, sounds like your beef is with your consultant if you were
paying for his/her expertise in Excel.


Tom,

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.

--
Amedee Van Gasse using XanaNews 1.16.3.1
If it has an "X" in the name, it must be Linux?
  #20   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 837
Default Excel Math Bug


Amedee Van Gasse wrote:
....

=-(5^2)
returns +25


I think you meant "returns -25"; +25 would be a bug for this one (but
not one that I can reproduce).

That -5^2 returns +25 is covered by Help for "About calculation
operators" subtopic "The order in which Excel performs operations in
formulas". As Tom noted, different programs may use different orders of
operator precedence. Programmers are responsible for learning the
languages that they use. It is a bit more problematic when one person
provides the formulas (or even the code) and another implements it, but
that is what testing is for.

I once got burned providing an algorithm that someone else then
implemented in a flavor of Basic that evaluated strictly left to right
with no algebraic hierarchy (other than parentheses) at all. I was
irritated that someone would write a language that worked that way, but
they documented how it worked, so our ignorance of their documentation
was not their fault. Fred's consultant should have known better than to
imply that any developer could be sued for producing a package that
operated as documented.

Jerry



  #21   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 837
Default Excel Math Bug

fred wrote:


'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

  #22   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 690
Default Excel Math Bug

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 he =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:
=-(32) returns -1, and =--(32) returns +1. This is just a feature of
Excel.

HTH
Dana DeLouis


"fred" wrote in message
...
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?


"Dana DeLouis" wrote in message
...
...the wrong sign a math bug,
...And Mathematica calculates the formula differently (the right way).


Just guessing here. The only thing that comes to mind of hand might be

the
following:

In Excel:
=-5^2
returns +25.

and in mm:
-5^2
-25


<snip


  #23   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 26
Default Excel Math Bug

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*.



  #24   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 4,624
Default Excel Math Bug

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

In article ,
"fred" wrote:

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*.

  #25   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 188
Default Excel Math Bug

"fred" wrote in message
...


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.






  #26   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 26
Default Excel Math Bug

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.


"Alan" wrote in message
...
"fred" wrote in message
...


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.






  #27   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 26
Default Excel Math Bug

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.



"JE McGimpsey" wrote in message
...
Then you need to disclose what you think the bug *is*...

In article ,
"fred" wrote:

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*.



  #28   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 26
Default Excel Math Bug

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.



"Myrna Larson" wrote in message
...
Again, sounds like your beef is with your consultant if you were paying

for
his/her expertise in Excel.


I agree!



  #29   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 4,624
Default Excel Math Bug

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.


In article ,
"Alan" wrote:

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.

  #30   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 26
Default Excel Math Bug

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.




  #31   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 4,624
Default Excel Math Bug

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).

In article ,
"fred" wrote:

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.

  #32   Report Post  
Posted to microsoft.public.excel.programming,sci.math
external usenet poster
 
Posts: 26
Default Excel Math Bug

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.




"JE McGimpsey" wrote in message
...
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).

In article ,
"fred" wrote:

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.



  #33   Report Post  
Posted to microsoft.public.excel.programming,sci.math
external usenet poster
 
Posts: 2
Default Excel Math Bug


"fred" wrote

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, _____________________
| _ | |
| (_) | 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) ________|




  #34   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,824
Default Excel Math Bug

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



JE McGimpsey wrote:

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).

In article ,
"fred" wrote:

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.


--

Dave Peterson

  #35   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 188
Default Excel Math Bug

"Dave Peterson" wrote in message
...

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



JE McGimpsey wrote:

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).


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.









  #36   Report Post  
Posted to microsoft.public.excel.programming,sci.math
external usenet poster
 
Posts: 783
Default Excel Math Bug

fred wrote:

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
  #37   Report Post  
Posted to microsoft.public.excel.programming,sci.math
external usenet poster
 
Posts: 6
Default Excel Math Bug

fred wrote:

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.


Alan Beban wrote:

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.


--
Stephen J. Herschkorn

  #38   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 690
Default Excel Math Bug

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" wrote in message
...
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.




"JE McGimpsey" wrote in message
...
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).

In article ,
"fred" wrote:

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.





  #39   Report Post  
Posted to microsoft.public.excel.programming,sci.math
external usenet poster
 
Posts: 26
Default Excel Math Bug


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?!


  #40   Report Post  
Posted to microsoft.public.excel.programming,sci.math
external usenet poster
 
Posts: 1
Default Excel Math Bug

On Tue, 03 Aug 2004 01:27:30 +0000, fred wrote:


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?!


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

--
Lance Lamboy

"Go F*ck Yourself" ~ Dick Cheney

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Less Excel more Math Ty Excel Worksheet Functions 1 April 16th 11 06:28 AM
Math functions in Excel Farhad Excel Discussion (Misc queries) 2 February 19th 07 05:16 PM
Excel math formula dan Excel Worksheet Functions 4 October 30th 06 05:51 PM
How do I ? math/excel question Madduck Excel Discussion (Misc queries) 3 July 26th 06 05:41 AM
More math than excel, but a fun problem S Davis Excel Worksheet Functions 4 July 6th 06 10:04 PM


All times are GMT +1. The time now is 10:18 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"