Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming,sci.math
|
|||
|
|||
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
Posted to microsoft.public.excel.programming,sci.math
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming,sci.math
|
|||
|
|||
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
Posted to microsoft.public.excel.programming,sci.math
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming,sci.math
|
|||
|
|||
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
Posted to microsoft.public.excel.programming,sci.math
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Less Excel more Math | Excel Worksheet Functions | |||
Math functions in Excel | Excel Discussion (Misc queries) | |||
Excel math formula | Excel Worksheet Functions | |||
How do I ? math/excel question | Excel Discussion (Misc queries) | |||
More math than excel, but a fun problem | Excel Worksheet Functions |