This MS-Excel bug is well known to me. I detected it about ten years ago, reported it to Microsoft several times. But until today there were no reactions. Presumably noone at Microsoft did ever take some notice of my emails.
As a consultant in financial industry I have to deal with yields, returns and calculations of these figures. In order to calculate the total return of some security or index you have to multiply all the individual returns of each period. For long existing funds this may give a long series of numbers. In order to shorten the multiplying procedure the power-function has been invented. This is where I did apply the ^, power-symbol, in MS-Excel to check some calculations. After a long journey through exclude and resolve procedures I got a very strange result, directly funded by MS-Excel, my ever used power tool. What I could not believe:
MS-Excel applies a wrong precedence order to the arithmetic operators.
The operator “unary minus” is given priority over the function call which is the power function a kind of. Note that by using the function()-syntax all the arguments are included by brackets or separators therefore no ambiguities may arise.
The programming languages are using the priority rules above because it is a must. If we would give the unary symbol precedence over the function call or the power-symbol we would face some trouble:
There are two fully equivalent formulas leading to different results:
| +a–f²=0 |
| –f²+a=8 |
In order to give this Excel feature some particular emphasis as a Level 0-bug I will append the equivalent implementation in VBA, the Visual BASIC of MS-Excel.
Public Function af(Optional nSign As Integer = 1) As Double Dim a As Double, f As Double, rc As Double a = 4 f = 2 If (nSign = 1) Then rc = + a - f ^ 2 ElseIf (nSign = -1) Then rc = -f ^ 2 + a Else rc = Null End If af = rc End Function
For both cases the VBA-function gives the result zero which is arithmetically correct. WolframAlpha gives a clear indication about who is wrong in this particular case.
gg:sfit 