Never use the power-symbol in MS-Excel!

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.

ExcelPower

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:

ExcelPwr2

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.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s