Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2003 SP2 computes the power of a negative number wrong.
A1 contains -4.
B1 contains the expression -A1^4 The result is supposed to be -256, but instead it is 256. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...et.f unctions |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2003 SP2 computes the power of a negative number wrong.
=A1^4 (should give you 256)
"Giovanni Ciriani" wrote: A1 contains -4. B1 contains the expression -A1^4 The result is supposed to be -256, but instead it is 256. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...et.f unctions |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2003 SP2 computes the power of a negative number wrong.
Giovanni Ciriani -
See Excel Help "About calculation operators." Negation is performed before exponentiation. - Mike http://www.mikemiddleton.com "Giovanni Ciriani" <Giovanni wrote in message ... A1 contains -4. B1 contains the expression -A1^4 The result is supposed to be -256, but instead it is 256. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...et.f unctions |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2003 SP2 computes the power of a negative number wrong.
Hence if you want -256, try =-(A1^4)
-- David Biddulph "Mike Middleton" wrote in message ... Giovanni Ciriani - See Excel Help "About calculation operators." Negation is performed before exponentiation. "Giovanni Ciriani" <Giovanni wrote in message ... A1 contains -4. B1 contains the expression -A1^4 The result is supposed to be -256, but instead it is 256. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2003 SP2 computes the power of a negative number wrong.
The negations belongs to the -4. The - in front of A1 should be trated as a
subtraction sign. Exponentiation takes precedence over subtraction. To prove this point, please enter another formula: =1-A1^4 and for a value of A1=-4 you obtain -255. Why should the minus in front of A1 be treated with different precedences depending whether or not there is a 1 in front of it? Every program that manipulates symbolic math gives precedence to the exponentiation and then put the minus in front of it. My HP pocket calculator does that too, and math students are taught the same way in school (elementary through post-graduate). Previous versions of Excel behaved the way I was expecting. This versionnis behaving incorrectly and I seriously believe it to be a misinterpretation of symbolic math. "Mike Middleton" wrote: Giovanni Ciriani - See Excel Help "About calculation operators." Negation is performed before exponentiation. - Mike http://www.mikemiddleton.com "Giovanni Ciriani" <Giovanni wrote in message ... A1 contains -4. B1 contains the expression -A1^4 The result is supposed to be -256, but instead it is 256. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...et.f unctions |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2003 SP2 computes the power of a negative number wrong.
Hi
Rewrite the equation as =((-1)*(-4))*((-1)*(-4))*((-1)*(-4))*((-1)*(-4)) What do you get as result? Maybe you wanted =-(A1^4) Arvi Laanemets "Giovanni Ciriani" <Giovanni wrote in message ... A1 contains -4. B1 contains the expression -A1^4 The result is supposed to be -256, but instead it is 256. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...id=e69600e5-fa db-4745-91bb-eea8435e9c38&dg=microsoft.public.excel.worksheet.f unctions |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2003 SP2 computes the power of a negative number wrong.
The UNARY minus operator always takes highest precedence. What would
you say that: (-4) * (-4) * (-4) * (-4) should evaluate to? Hope this helps. Pete Giovanni Ciriani wrote: The negations belongs to the -4. The - in front of A1 should be trated as a subtraction sign. Exponentiation takes precedence over subtraction. To prove this point, please enter another formula: =1-A1^4 and for a value of A1=-4 you obtain -255. Why should the minus in front of A1 be treated with different precedences depending whether or not there is a 1 in front of it? Every program that manipulates symbolic math gives precedence to the exponentiation and then put the minus in front of it. My HP pocket calculator does that too, and math students are taught the same way in school (elementary through post-graduate). Previous versions of Excel behaved the way I was expecting. This versionnis behaving incorrectly and I seriously believe it to be a misinterpretation of symbolic math. "Mike Middleton" wrote: Giovanni Ciriani - See Excel Help "About calculation operators." Negation is performed before exponentiation. - Mike http://www.mikemiddleton.com "Giovanni Ciriani" <Giovanni wrote in message ... A1 contains -4. B1 contains the expression -A1^4 The result is supposed to be -256, but instead it is 256. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...et.f unctions |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2003 SP2 computes the power of a negative number wrong.
Despite what you believe, Excel has defined the order of precedence for evaluation of its formulas. This has not changed. (also note that negation and subtraction fall in to different areas).
Here is the help from xl2000 for example: The order in which Microsoft Excel performs operations in formulas If you combine several operators in a single formula, Microsoft Excel performs the operations in the order shown in the following table. If a formula contains operators with the same precedence €” for example, if a formula contains both a multiplication and division operator €” Excel evaluates the operators from left to right. To change the order of evaluation, enclose the part of the formula to be calculated first in parentheses. For more information about calculation operators, Operator Description : (colon) (single space) , (comma) Reference operators €“ Negation (as in €“1) % Percent ^ Exponentiation * and / Multiplication and division + and €“ Addition and subtraction & Connects two strings of text (concatenation) = < <= = < Comparison -- Regards, Tom Ogilvy "Giovanni Ciriani" wrote in message ... The negations belongs to the -4. The - in front of A1 should be trated as a subtraction sign. Exponentiation takes precedence over subtraction. To prove this point, please enter another formula: =1-A1^4 and for a value of A1=-4 you obtain -255. Why should the minus in front of A1 be treated with different precedences depending whether or not there is a 1 in front of it? Every program that manipulates symbolic math gives precedence to the exponentiation and then put the minus in front of it. My HP pocket calculator does that too, and math students are taught the same way in school (elementary through post-graduate). Previous versions of Excel behaved the way I was expecting. This versionnis behaving incorrectly and I seriously believe it to be a misinterpretation of symbolic math. "Mike Middleton" wrote: Giovanni Ciriani - See Excel Help "About calculation operators." Negation is performed before exponentiation. - Mike http://www.mikemiddleton.com "Giovanni Ciriani" <Giovanni wrote in message ... A1 contains -4. B1 contains the expression -A1^4 The result is supposed to be -256, but instead it is 256. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...et.f unctions |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2003 SP2 computes the power of a negative number wrong.
Then let me ask you a question. Why should -A1^4-1 evaluate to a different
number than 1-A4^4 ? (-255 vs 257 for A1 equal to -4). This striking difference goes counter the rule of commutativity additions established several centuries ago (if not millenia): the order of the number being added should not change the result of an addition. "Pete_UK" wrote: The UNARY minus operator always takes highest precedence. What would you say that: (-4) * (-4) * (-4) * (-4) should evaluate to? Hope this helps. Pete Giovanni Ciriani wrote: The negations belongs to the -4. The - in front of A1 should be trated as a subtraction sign. Exponentiation takes precedence over subtraction. To prove this point, please enter another formula: =1-A1^4 and for a value of A1=-4 you obtain -255. Why should the minus in front of A1 be treated with different precedences depending whether or not there is a 1 in front of it? Every program that manipulates symbolic math gives precedence to the exponentiation and then put the minus in front of it. My HP pocket calculator does that too, and math students are taught the same way in school (elementary through post-graduate). Previous versions of Excel behaved the way I was expecting. This versionnis behaving incorrectly and I seriously believe it to be a misinterpretation of symbolic math. "Mike Middleton" wrote: Giovanni Ciriani - See Excel Help "About calculation operators." Negation is performed before exponentiation. - Mike http://www.mikemiddleton.com "Giovanni Ciriani" <Giovanni wrote in message ... A1 contains -4. B1 contains the expression -A1^4 The result is supposed to be -256, but instead it is 256. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...et.f unctions |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2003 SP2 computes the power of a negative number wrong.
Then let me ask you a question. Why should -A1^4-1 evaluate to a different
number than 1-A4^4 ? (-255 vs 257 for A1 equal to -4). This striking difference goes counter the rule of commutativity additions established several centuries ago (if not millenia): the order of the number being added should not change the result of an addition. "Tom Ogilvy" wrote: Despite what you believe, Excel has defined the order of precedence for evaluation of its formulas. This has not changed. (also note that negation and subtraction fall in to different areas). Here is the help from xl2000 for example: The order in which Microsoft Excel performs operations in formulas If you combine several operators in a single formula, Microsoft Excel performs the operations in the order shown in the following table. If a formula contains operators with the same precedence €” for example, if a formula contains both a multiplication and division operator €” Excel evaluates the operators from left to right. To change the order of evaluation, enclose the part of the formula to be calculated first in parentheses. For more information about calculation operators, Operator Description : (colon) (single space) , (comma) Reference operators €“ Negation (as in €“1) % Percent ^ Exponentiation * and / Multiplication and division + and €“ Addition and subtraction & Connects two strings of text (concatenation) = < <= = < Comparison -- Regards, Tom Ogilvy "Giovanni Ciriani" wrote in message ... The negations belongs to the -4. The - in front of A1 should be trated as a subtraction sign. Exponentiation takes precedence over subtraction. To prove this point, please enter another formula: =1-A1^4 and for a value of A1=-4 you obtain -255. Why should the minus in front of A1 be treated with different precedences depending whether or not there is a 1 in front of it? Every program that manipulates symbolic math gives precedence to the exponentiation and then put the minus in front of it. My HP pocket calculator does that too, and math students are taught the same way in school (elementary through post-graduate). Previous versions of Excel behaved the way I was expecting. This versionnis behaving incorrectly and I seriously believe it to be a misinterpretation of symbolic math. "Mike Middleton" wrote: Giovanni Ciriani - See Excel Help "About calculation operators." Negation is performed before exponentiation. - Mike http://www.mikemiddleton.com "Giovanni Ciriani" <Giovanni wrote in message ... A1 contains -4. B1 contains the expression -A1^4 The result is supposed to be -256, but instead it is 256. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...et.f unctions |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2003 SP2 computes the power of a negative number wrong.
Then let me ask you a question. Why should -A1^4-1 evaluate to a different
number than 1-A4^4 ? (-255 vs 257 for A1 equal to -4). This striking difference goes counter the rule of commutativity additions established several centuries ago (if not millenia): the order of the number being added should not change the result of an addition. "Arvi Laanemets" wrote: Hi Rewrite the equation as =((-1)*(-4))*((-1)*(-4))*((-1)*(-4))*((-1)*(-4)) What do you get as result? Maybe you wanted =-(A1^4) Arvi Laanemets "Giovanni Ciriani" <Giovanni wrote in message ... A1 contains -4. B1 contains the expression -A1^4 The result is supposed to be -256, but instead it is 256. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...id=e69600e5-fa db-4745-91bb-eea8435e9c38&dg=microsoft.public.excel.worksheet.f unctions |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2003 SP2 computes the power of a negative number wrong.
Giovanni Ciriani wrote:
Then let me ask you a question. Why should -A1^4-1 evaluate to a different number than 1-A4^4 ? (-255 vs 257 for A1 equal to -4). This striking difference goes counter the rule of commutativity additions established several centuries ago (if not millenia): the order of the number being added should not change the result of an addition. I think you meant -A1^4+1. And the answer, which you are refusing to acknowledge even though it's been pointed out to you a couple of times, is that the minus sign in -A1^4-1 is a negation operator, not a subtraction operator, so commutativity of subtraction is not relevant. To see commutativity in action compare =1-(-A1^4)-2 with =1-2-(-A1^4),where the minus signs preceding the parenthetical and 2 are, in fact, subtraction operators. Alan Beban "Arvi Laanemets" wrote: Hi Rewrite the equation as =((-1)*(-4))*((-1)*(-4))*((-1)*(-4))*((-1)*(-4)) What do you get as result? Maybe you wanted =-(A1^4) Arvi Laanemets "Giovanni Ciriani" <Giovanni wrote in message ... A1 contains -4. B1 contains the expression -A1^4 The result is supposed to be -256, but instead it is 256. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...id=e69600e5-fa db-4745-91bb-eea8435e9c38&dg=microsoft.public.excel.worksheet.f unctions |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2003 SP2 computes the power of a negative number wrong.
Hi
Even when you refuse acknowledge sign and power as separate operators, you can deduce them only to multiplying operator. So when you have an equation =-A1^4, then you have a sequence of multiplying class operators, which are all same weigth and are processed in order from left to right. In formula =1-A1^4 you have a substraction operator ad an operator of multiplying class, the later is processed first. It looks like you missed some lessons in elemantary school math :-)))) -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) "Giovanni Ciriani" wrote in message ... Then let me ask you a question. Why should -A1^4-1 evaluate to a different number than 1-A4^4 ? (-255 vs 257 for A1 equal to -4). This striking difference goes counter the rule of commutativity additions established several centuries ago (if not millenia): the order of the number being added should not change the result of an addition. "Arvi Laanemets" wrote: Hi Rewrite the equation as =((-1)*(-4))*((-1)*(-4))*((-1)*(-4))*((-1)*(-4)) What do you get as result? Maybe you wanted =-(A1^4) Arvi Laanemets "Giovanni Ciriani" <Giovanni wrote in message ... A1 contains -4. B1 contains the expression -A1^4 The result is supposed to be -256, but instead it is 256. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...id=e69600e5-fa db-4745-91bb-eea8435e9c38&dg=microsoft.public.excel.worksheet.f unctions |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2003 SP2 computes the power of a negative number wrong.
actually that is right. any negative number to raised to an 'even' power is
going to be positive. "Giovanni Ciriani" <Giovanni wrote in message ... A1 contains -4. B1 contains the expression -A1^4 The result is supposed to be -256, but instead it is 256. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...et.f unctions |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2003 SP2 computes the power of a negative number wrong.
Arvi, I do acknowledge that they are separate operators. The expression are
evaluated correctly according to the precedence established by the programmers. What I'm saying is that the programmer interpreted the specs regarding negation incorrectly. The negation should be given priority only if it is in parenthesis, and not when it is out of parenthesis. That's the only way to keep the rule of commutativity working. "Arvi Laanemets" wrote: Hi Even when you refuse acknowledge sign and power as separate operators, you can deduce them only to multiplying operator. So when you have an equation =-A1^4, then you have a sequence of multiplying class operators, which are all same weigth and are processed in order from left to right. In formula =1-A1^4 you have a substraction operator ad an operator of multiplying class, the later is processed first. It looks like you missed some lessons in elemantary school math :-)))) -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) "Giovanni Ciriani" wrote in message ... Then let me ask you a question. Why should -A1^4-1 evaluate to a different number than 1-A4^4 ? (-255 vs 257 for A1 equal to -4). This striking difference goes counter the rule of commutativity additions established several centuries ago (if not millenia): the order of the number being added should not change the result of an addition. "Arvi Laanemets" wrote: Hi Rewrite the equation as =((-1)*(-4))*((-1)*(-4))*((-1)*(-4))*((-1)*(-4)) What do you get as result? Maybe you wanted =-(A1^4) Arvi Laanemets "Giovanni Ciriani" <Giovanni wrote in message ... A1 contains -4. B1 contains the expression -A1^4 The result is supposed to be -256, but instead it is 256. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...id=e69600e5-fa db-4745-91bb-eea8435e9c38&dg=microsoft.public.excel.worksheet.f unctions |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2003 SP2 computes the power of a negative number wrong.
Jim, the trouble is if 1-A1^2 evaluates differently from -A1^2+1 it means
that the rule of commutativity does not hold. To fix that centuries ago it was established that the - in front of A1 should be applied to the evaluation of the expression only after the exponetiation. Further to that it was established that if one wanted to raise to the power a negative number, the negation should have been enclosed in parenthesis. This way everything works. Now, somebody wrote specs for the programmers of the spreadsheet incorrectly, or the programmers interpreted the specs incorrectly. One way or the other, centuries of a perfectly good algebraic convention should not take a back seat to a misunderstanding betrween programmer and spec writer. For this reason many prefer to abund in parenthesis to make sure that the calculations precedences are not misinterpreted. As several folks, who replied to my initial post, pointed out if I want a certain result why don't I put parenthesis around the power. That's OK but it doesn't make the wrong convention correct. "jim sturtz" wrote: actually that is right. any negative number to raised to an 'even' power is going to be positive. "Giovanni Ciriani" <Giovanni wrote in message ... A1 contains -4. B1 contains the expression -A1^4 The result is supposed to be -256, but instead it is 256. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...et.f unctions |
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2003 SP2 computes the power of a negative number wrong.
Thanks Mr. G.C.....In our BUILDING Engineering Field, considering the
built-in MATH & trigonometric functions alone, we endlessly use this Expenential function, and encodes formulas the way mathematics was taught in the school as based on the CENTURY OLD RULE....thats why, most building engneers tend to be apart with Excel due to this....Remaining mostly are those who use excel like a database<search, find, replace and count 1+1 or basic mathematical equations. I can feel the students and previous students to be with you !!! Anyway...Lets just hope that this thread can alarm Microsoft of their selling value in the succeeding versions.... -- "Bright minds are blessed to those who share them.."-rsb. "Giovanni Ciriani" wrote: Jim, the trouble is if 1-A1^2 evaluates differently from -A1^2+1 it means that the rule of commutativity does not hold. To fix that centuries ago it was established that the - in front of A1 should be applied to the evaluation of the expression only after the exponetiation. Further to that it was established that if one wanted to raise to the power a negative number, the negation should have been enclosed in parenthesis. This way everything works. Now, somebody wrote specs for the programmers of the spreadsheet incorrectly, or the programmers interpreted the specs incorrectly. One way or the other, centuries of a perfectly good algebraic convention should not take a back seat to a misunderstanding betrween programmer and spec writer. For this reason many prefer to abund in parenthesis to make sure that the calculations precedences are not misinterpreted. As several folks, who replied to my initial post, pointed out if I want a certain result why don't I put parenthesis around the power. That's OK but it doesn't make the wrong convention correct. "jim sturtz" wrote: actually that is right. any negative number to raised to an 'even' power is going to be positive. "Giovanni Ciriani" <Giovanni wrote in message ... A1 contains -4. B1 contains the expression -A1^4 The result is supposed to be -256, but instead it is 256. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...et.f unctions |
#19
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2003 SP2 computes the power of a negative number wrong.
Has anybody tried a symbolic calculator like the HP-19B or the TI-83 and the
rest of the TI family? Let's try a much simpler calculation and then see how many calculators agree with one result and how many agree with one result or the other. Let's try -A1^2+A1^2 please post your results: Excel=8 HP-19B=0 TI-83=0 "Giovanni Ciriani" wrote: A1 contains -4. B1 contains the expression -A1^4 The result is supposed to be -256, but instead it is 256. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...et.f unctions |
#20
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2003 SP2 computes the power of a negative number wrong.
Google Spreadsheets=8
Open Office Calc=8 "Giovanni Ciriani" wrote: Has anybody tried a symbolic calculator like the HP-19B or the TI-83 and the rest of the TI family? Let's try a much simpler calculation and then see how many calculators agree with one result and how many agree with one result or the other. Let's try -A1^2+A1^2 please post your results: Excel=8 HP-19B=0 TI-83=0 "Giovanni Ciriani" wrote: A1 contains -4. B1 contains the expression -A1^4 The result is supposed to be -256, but instead it is 256. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...et.f unctions |
#21
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2003 SP2 computes the power of a negative number wrong.
Giovanni Ciriani wrote...
Arvi, I do acknowledge that they are separate operators. The expression are evaluated correctly according to the precedence established by the programmers. What I'm saying is that the programmer interpreted the specs regarding negation incorrectly. The negation should be given priority only if it is in parenthesis, and not when it is out of parenthesis. That's the only way to keep the rule of commutativity working. .... First, commutivity has NOTHING to do with this. Nothing! In ALL programming languages and all expression-driven numerical computation software I've seen, unary minus ALWAYS has higher precedence than the dyadic subtraction operator. -a - b = -(b - -a) (dyadic - is anticommutative) and -a + b = b + -a (dyadic + is commutative). If you add other operators, you need to parse the expressions correctly, and you don't seem to be doing that. Second, Excel is NOT unique among programming languages or expression-driven numerical computation software in giving unary minus higher operator precedence than exponentiation. That *IS* contrary to the standard bodmas (the English acronym) math/science textbook operator precedence, but Excel is highly idiosyncratic about which standards it follows. FWLIW, Excel's operator precedence is the same as COBOL and some SQL dialects. It has also been in place for decades now, so changing it merely to suit purists' whims could break many existing spreadsheet models. Getting back to the similarity to COBOL, if Excel's original programmers believed that most Excel users back in the mid 1980s would have been more familiar with COBOL than math texts, their choice of operator precedence would NOT have been a mistake, just an unfortunate design decision. However, changing that operator precedence now would be a MONUMENTAL MISTAKE for the reason already given: it'd screw up millions if not billions of existing spreadsheets. Besides, this has been discussed many times in the past. There's a much longer thread in the comp.sci.math ng archives that goes into this in much greater depth. Yes, Excel's operator precedence is unfortunate. No, it's not unique. No, it probably wasn't a mistake. No, it's not going to change in our lifetimes. So . . . get used to it. |
#22
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2003 SP2 computes the power of a negative number wrong.
Giovanni Ciriani wrote...
Then let me ask you a question. Why should -A1^4-1 evaluate to a different number than 1-A4^4 ? (-255 vs 257 for A1 equal to -4). .... Your error here comes from confusing the unary minus operator with the dyadic subtraction operator. In bodmas texts, unary minus is treated implicitly as multiplication by -1, so -x^4 could be expanded as (-1)*x^4, and bodmas would evaluate this as (-1)*(x^4). This is just a convention. Unfortunately, Excel adopts a different but internally self-consistent convention of treating -x implicitly as (0-x), and the parentheses are *INTENDED*. Also, your first expression has 3 operators while your second has only 2. It's not immediately obvious why changing the number of operators shouldn't change the values. Perhaps you should have written the second as 1--x^4, in which case -x^4-1 = 255 and 1--x^4 = -255, which is EXPECTED since subtraction is anticommutative: a - b = -(b - a). Or you could have written the first as -x^4+-1 and the second as -1+-x^4, and in that case both would equal 255. Commutivity and anticommutivity are preserved as long as you use APPROPRIATE arithmetic rephrasing, and that REQUIRES distinguishing between - as unary minus and as subtraction. IOW, Excel's convention, PROPERLY UNDERSTOOD, is self-consistent. Your mistake is assuming a--b^c = a+b^c as it would in bodmas. In some programming languages there's a different token for the arithmetic sign of negative numeric literals. APL and its offspring are prime examples. In those languages the sign token is part of the numeric literal, so if I were to use underscore, _, as such a token, 0-3 = _3, and _3^4 would always be interpretted as (0-3)^4. This probably wasn't Excel's original developers' explicit rationale, but I suspect they believed that users would expect -3^2 to be evaluated as (-3)^2 when the base was a negative numeric literal, so they chose to have unary minus behave the same way: -x^2 = (-x)^2. Their convention is equivalent to translating all instances of -x (unary -) as (0-x). Mathematicians back in the 17th century were lazy in this regard, and used the same symbol to represent numeric sign, sign change and subtraction. In order to resolve the ambiguity that this produces, mathematicians and other writers who use mathematics adopted the bodmas convention. Excel adopted a different convention. Unfortunate, but as likely to be resolved as the UK, Ireland, Japan, Australia and New Zeeland are to switching to driving on the right side of the road. |
#23
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2003 SP2 computes the power of a negative number wrong.
Giovanni Ciriani wrote...
Google Spreadsheets=8 Open Office Calc=8 .... Because both adopted Excel's operator precedence. This has been done before, but WTH. Gnumeric 1.6.x 8 though it changes the formula to =(-A1)^2+A1^2 Lotus 123 R97 0 Lotus always understood math better than Microsoft VBA 0 using ? -2^2+2^2 in the Immediate window In short, any software that puts compatibility with Excel worksheet formula syntax as a high priority is likely to produce the same results as Excel, and other software (aside from COBOL apps and some SQL dialects) won't. |
#24
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2003 SP2 computes the power of a negative number wrong.
Thats enough Geovanni....Lets find solution (at least temporary) so that the
faithful user like me can still continue with all the workbooks established by engineers like me....I will start a new ? thread to find solution by find/replace functions, hopefully to be replied by other excel users....thanks anyway....see THREAD : EXCEL FUNCTION CROSS SOLUTION ? -- "Bright minds are blessed to those who share them.."-rsb. "Harlan Grove" wrote: Giovanni Ciriani wrote... Google Spreadsheets=8 Open Office Calc=8 .... Because both adopted Excel's operator precedence. This has been done before, but WTH. Gnumeric 1.6.x 8 though it changes the formula to =(-A1)^2+A1^2 Lotus 123 R97 0 Lotus always understood math better than Microsoft VBA 0 using ? -2^2+2^2 in the Immediate window In short, any software that puts compatibility with Excel worksheet formula syntax as a high priority is likely to produce the same results as Excel, and other software (aside from COBOL apps and some SQL dialects) won't. |
#25
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2003 SP2 computes the power of a negative number wrong.
"Harlan Grove" wrote: Unfortunate, but as likely to be resolved as the UK, Ireland, Japan, Australia and New Zeeland are to switching to driving on the right side of the road. Any citizen of those countries would argue that they DO drive on the right side of the road... |
#26
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2003 SP2 computes the power of a negative number wrong.
Ryan Poth wrote...
Any citizen of those countries would argue that they DO drive on the right side of the road... Right meaning opposite of left. However, I'll accept your implicit argument that they can't tell right from left. |
#27
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2003 SP2 computes the power of a negative number wrong.
Ouch!!!!
Right as in Correct<vbg -- Regards Roger Govier "Harlan Grove" wrote in message ps.com... Ryan Poth wrote... Any citizen of those countries would argue that they DO drive on the right side of the road... Right meaning opposite of left. However, I'll accept your implicit argument that they can't tell right from left. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I set number values for letters (eg. B, A-) in Excel 2003 | Excel Discussion (Misc queries) | |||
axis title text box is wrong size in chart for excel 2003 | Charts and Charting in Excel | |||
Text, Number and Date formats excel 2003 driving experienced user | Excel Discussion (Misc queries) | |||
How do i number a list of data in excel 2003? | Excel Discussion (Misc queries) | |||
sharing/using/saving Excel 2002 files in Excel 2003 | Excel Discussion (Misc queries) |