Home |
Search |
Today's Posts |
#11
|
|||
|
|||
Be careful when using Excel
"Antonio Vernucci" wrote in
: Subject: Be careful when using Excel From: "Antonio Vernucci" Newsgroups: rec.radio.amateur.antenna Reply-To: "Antonio Vernucci" -- ------------------------------------------------------ Antonio Vernucci, I0JX US call: K0JX Beacons: 50.004 MHz & 70.088 MHz Home page: http://www.qsl.net/i0jx e-mail: k0jx {at} amsat {dot} org ------------------------------------------------------ "Jeff" ha scritto nel messaggio . com... "Antonio Vernucci" wrote in message .. . I think this message can be of interest for those using Excel for antenna calculations. Write in one cell: =(-A1^2 + 8) Note: the exponent of A1 is 2 (and not 2+8=10) because Excel performs squaring before summing Write in another cell: =(8 - A1^2) They look pretty much the same But give A1 any non-zero value and see what happen. 73 Tony I0JX Rome, Italy I see it give the correct answers!!! eg when A1 =2 the first formula =12 and the second =4 -2 squared =4 So 4 plus 8 = 12 2 squared =4 So 8 - 4 = 4 8 - (2 squared) = 4 Yes, but the way Excel works is deceiving. As a matter of fact, with reference to the general mathematical principle A+B = B+A, let us have: A= -A1^2 B = 8 Then, one would expect that -A1^2 + 8 is the same as 8 - A1^2, which is not the way Excel works. So just what were you expecting to get? Were you expecting -A1^2 to really be -(A1^2)? -Bruce |
#12
|
|||
|
|||
Be careful when using Excel
"Antonio Vernucci" wrote in message . .. In correct mathematical convention the expression let A1 = 2 then -A1^2 must yield -A1^2 = -4 otherwise we would have to write (-A1)^2 = 4 I share your opinion. But there are two Microsofts. - for Microsoft#1, that is Excel: (-2^2 + 8) = 12 - for Microsoft#2, that is Visual Basic: (-2^2 + 8) = 4 at least they are doing what their help says they should do. in vb6 exponentiation is done before negation. in excel negation is done before exponentiation. when in doubt use more parenthesis than you think you should to be sure the calculation is done in the order you are expecting. |
#13
|
|||
|
|||
Be careful when using Excel
"Antonio Vernucci" wrote in
: I think this message can be of interest for those using Excel for antenna calculations. Write in one cell: =(-A1^2 + 8) Note: the exponent of A1 is 2 (and not 2+8=10) because Excel performs squaring before summing Write in another cell: =(8 - A1^2) Do you realise that the '-' in both expressions are quite different in meaning, one is a unary negation operator, the other is the binary subract operator. No magic there, force the - to be a unary operator, and it behaves consistently, eg 8+-2^2 gives 12. They look pretty much the same Not to a programmer! Owen But give A1 any non-zero value and see what happen. 73 Tony I0JX Rome, Italy |
#14
|
|||
|
|||
Be careful when using Excel
Do you realise that the '-' in both expressions are quite different in
meaning, one is a unary negation operator, the other is the binary subract operator. I realise, but Microsoft apparently does not: - for Excel: (-2^2 + 8) = 12 - for Visual Basic: (-2^2 + 8) = 4 They look pretty much the same Not to a programmer! I would expect that some programmers may get confused, due to the inconsistency of software products of the same company. Tony I0JX |
#15
|
|||
|
|||
Be careful when using Excel
"Antonio Vernucci" wrote in
: Do you realise that the '-' in both expressions are quite different in meaning, one is a unary negation operator, the other is the binary subract operator. I realise, but Microsoft apparently does not: - for Excel: (-2^2 + 8) = 12 - for Visual Basic: (-2^2 + 8) = 4 They look pretty much the same Not to a programmer! I would expect that some programmers may get confused, due to the inconsistency of software products of the same company. Do not assume that all languages or all products use the same operator precedence rules. The Excel rules are spelled out in the Excel help. The key thing is that there is a difference in the meaning of '-' in the uses as the negation operator and subtraction operator, same symbol, two meanings, you need to understand that when looking in the list of operator precedence. Excel spells out clearly that unary negation has higher precedence than exponentiation, and subtraction is lower than both... so keep that in mind when writing formulae. If you are writing VB, use the precedence rules for VB. The VB rules clearly state that exponentiation has higher precedence than negation, and subtraction is lower. This may have its roots before the existence of Microsoft. Many languages do not have an exponentiation operator, requiring functions instead (often pow()). Such use eliminates the operator precedence issue. PERL does have an exponentiation operator, and it is higher precedence than unary + and -. FORTRAN90 does have an exponentiation operator, and it is higher precedence than unary + and -. It is part of the programming landscape... algebraic expressions are not universal, they need to be written for the specific language. Now, I hear that you think that is a bad idea, but it isn't likely to change. It is most unlikely that a new version of any language will change operator precedence, it has too many issues. Owen |
#16
|
|||
|
|||
Be careful when using Excel
"Antonio Vernucci" wrote in message ... Do you realise that the '-' in both expressions are quite different in meaning, one is a unary negation operator, the other is the binary subract operator. I realise, but Microsoft apparently does not: - for Excel: (-2^2 + 8) = 12 - for Visual Basic: (-2^2 + 8) = 4 They look pretty much the same Not to a programmer! I would expect that some programmers may get confused, due to the inconsistency of software products of the same company. Tony I0JX REAL programmers know the difference and add extra parenthesis just to be sure the processor does what we want. for instance, i would NEVER use a unary negate without adding parenthesis to be sure it was applied properly. In most cases i would write that equation like: ((-1*A1)^2)+8, or even more obvious in excel: power(-1*a1,2)+8 |
#17
|
|||
|
|||
Be careful when using Excel
On Fri, 8 May 2009 18:36:08 +0200, "Antonio Vernucci"
wrote: I think this message can be of interest for those using Excel for antenna calculations. Write in one cell: =(-A1^2 + 8) Note: the exponent of A1 is 2 (and not 2+8=10) because Excel performs squaring before summing Write in another cell: =(8 - A1^2) They look pretty much the same But give A1 any non-zero value and see what happen. 73 Tony I0JX Rome, Italy It's an INTENTIONAL Microsoft bug. See: "Formula Returns Unexpected Positive Value" http://support.microsoft.com/kb/q132686/ Although this article applies to Excel 97 and earlier versions, the problem is still there in later versions of Excel. The article indicates that: "The order of evaluation of operators dictates that a minus sign (-) used as a negation operator (such as -1) is evaluated before all other operators. " which in my opinion, is wrong. The MS order of operations is: : Range space Intersection , Union - Negation % Percentage ^ Exponentiation * or / Multiplication or Division + or - Addition or Subtraction & Text Operator = = = Comparison Operators Note that the negation (negative sign) operator comes before any arithmetic operators. I haven't checked how Open Office 3 and others do it. My guess(tm) is that they all treat the negation operator the same as subtraction which would be (correctly) evaluated AFTER exponentiation, multiplication, and division. -- Jeff Liebermann 150 Felker St #D http://www.LearnByDestroying.com Santa Cruz CA 95060 http://802.11junk.com Skype: JeffLiebermann AE6KS 831-336-2558 |
#18
|
|||
|
|||
Be careful when using Excel
On Sat, 09 May 2009 16:48:19 +0000, Jim Higgins
wrote: The proper precedence of mathematical operations is "PEMDAS," meaning Parenthesis, Exponentiation, Mult/Div, Add/Sub. Yep. The MS precidence is: http://support.microsoft.com/kb/25189/EN-US/ : Range space Intersection , Union - Negation % Percentage ^ Exponentiation * or / Multiplication or Division + or - Addition or Subtraction & Text Operator = = = Comparison Operators Note that the negation (negative sign) operator comes before any arithmetic operators. The problem comes from Excel inventing some kind of distinction between negation (negative number) and substraction. For arithmetic, there is none. For C programmers, there is a difference (in the way the data is stored). More on the subject: http://mathforum.org/library/drmath/view/69058.html That minus in front of the A1 is a unary negation, which is a multiplication by minus 1, so it should be performed AFTER the exponentiation, i.e; -A1^2 = -(A1^2) Excel performs it before the exponentiation, i.e.; -A1^2 = (-A1)^2 The Excel answer is incorrect. Yep. However, MS is not about to create problems by fixing the problem. It would be a bad thing to have existing spreadsheets, suddently give different results when run on updated and fixed versions of Excel. Compatibility with old bugs is one reason that bugs tend to be perpetuated. Old bugs and sleeping dogs should be left alone. 73 de Jim, KB3PU -- Jeff Liebermann 150 Felker St #D http://www.LearnByDestroying.com Santa Cruz CA 95060 http://802.11junk.com Skype: JeffLiebermann AE6KS 831-336-2558 |
#19
|
|||
|
|||
Be careful when using Excel
"Jeff Liebermann" wrote in message ... On Sat, 09 May 2009 16:48:19 +0000, Jim Higgins wrote: Yep. However, MS is not about to create problems by fixing the problem. It would be a bad thing to have existing spreadsheets, suddently give different results when run on updated and fixed versions of Excel. Compatibility with old bugs is one reason that bugs tend to be perpetuated. Old bugs and sleeping dogs should be left alone. 73 de Jim, KB3PU I sure am glad they finally fixed the simple calculator that Windows came with. It had a major bug in it that if I remember correctly if you substracted 3.1 from 3.11 you got zero. There were other numbers like that also. I think Intel had to recall a bunch of chips because of an error in the math coprocessor part at one time. Microsoft products are so full of 'problems' that if they ever put out an error free product it would seem to be a mistake. |
#20
|
|||
|
|||
Be careful when using Excel
Jeff Liebermann wrote in
: .... It's an INTENTIONAL Microsoft bug. See: It is certainly popular to blame Microsoft with lots of things, whether they were responsible or not matters little. Keep in mind that Microsoft did not 'design' the algebraic operator hierarchy for Excel, Excel was released with a claim of 100% cell formula compatibility with the then leading spreadsheet Lotus 123. (Microsoft's compatibility was so good, it was subject of a famous court case.) It was much later that Microsoft conceived VBA and added it to their apps. IIRC, Visual Basic for Applications inherits its algebraic operator hierarchy from the BASIC language which was conceived around 1964 and enriched progressively. The "intentional Microsoft bug" perspective looks like just prejudice. Owen |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
everyone better be careful while building those shortwave radios | Shortwave | |||
Be careful replying to off topic messages here! (La Site Communique) | Boatanchors | |||
Be Careful What you Say on The Air Girls | General | |||
Be Careful What you Say on The Air Girls | Scanner | |||
Be Careful What you Say on The Air Girls | Shortwave |