cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Looking for Excel limitations

Highlighted
Moonstone

Looking for Excel limitations

Many years ago as a young(er) engineer I received a memo (from someone who may happen to be a regular contributor here) about the dangers of spreadsheets.  One of the key points was the mathematical limitations of excel - in the memo demonstrated by a Runge-Kutta solution not being accurate.  I have a few questions may be you can help me with:  

a.) Have there been any upgrades to Excel in the last decade that corrected such issues? 

b.) Does anyone happen to have an example that shows bad excel results vs mathcad/matlab/maple etc. (Limited time for me to attempt to reproduce the R-K at the moment)

c.) Any other examples or reasons on why excel is probably not the best choice for engineering calculations? (besides clarity and operation order)

 

Thanks,

6 REPLIES 6
Highlighted

Re: Looking for Excel limitations

Both Mathcad and Excel make it easy to generate numerical results with floating point error.  For example, try calculating 1/(0.3-0.1-0.1-0.1) in Mathcad and Excel (you'll get a surprising result).

 

You need a full computer algebra system (like Maple) if you want truly numerically accurate results

 

 

Highlighted

Re: Looking for Excel limitations


c.) Any other examples or reasons on why excel is probably not the best choice for engineering calculations? (besides clarity and operation order)

When it come to Mathcad its ability to be able to deal with units sure is a huge plus!

 

When it comes to numerical accuracy I wouldn't expect any big differences as both use the IEEE format for that.

B.png

But Mathcad may have better and more stable numerical algorithms implemented (not sure about this as I never would use Excel for any scientific or engineering calculations) and in Mathcad you have the option to use its symbolic calculations as well (even thought they can't compete with Maple or Mathematica).

 

Highlighted

Re: Looking for Excel limitations

Without having access to the actual implementations of both the Excel and the Mathcad RK routines it will be hard to pinpoint what went wrong, and whether updates (may) have resolved it.

As far as I can tell, given the same sequence of calculations, both Mathcad (11) and Excel (2007) give the same results.

LM_20180920_accuracy.png

Well, there's no symbolic in Excel. So numerics only:

LM_20180920_accuracy1.png

All this to demonstrate that to implement a numerical (approximation) algorithm requires careful attention when big and small numbers are both into play...

 

Luc

Highlighted

Re: Looking for Excel limitations

Did some work on this and seemed to confirm the issue that was I was told existed in Excel. 

 

Here is some background to the problem (minus any mention of excel):

https://community.ptc.com/t5/PTC-Mathcad/Pin-and-Pendulum/td-p/332104

In the attached I used an RK4 routine in mathcad (4.0) and then duplicated that in Excel.  (avoiding built-in functions to be apples-to-apples)  Sure enough, in Excel I only see that the pendulum strikes the pin 3 times.  I need to bump the division count up to ~12000 to observe the 4th strike in excel.  

 

So, that's interesting.  I'm curious if anyone seems a flaw in the analysis or has a good explanation for why different results are obtained between two programs.

Thanks,

 

Highlighted

Re: Looking for Excel limitations

One observation already:

The Prime implementation uses a definition of f(x,u) which internally does not refer to x.

So it could be better/as well a function of one parameter. f(u).

Changed it, and it does not make a noticable difference..

 

Luc

Highlighted

Re: Looking for Excel limitations

Found it!

That happens when you implement the functions differently.

The problem essentially lies with the function to calculate the length of the rope to be used.

In Prime the function l(phi) is called from within the function f, using the angle calculated for each k (k1...k4) as it is input to f. In contrast in Excel, the angle used to determine the length is the same for every k.

So once corrected:

LM_20180924_Pendulum.png

Conclusion: (As Werner noted) there's no (major) difference in accuracy between Mathcad and Excel.

(I hope I didn't ruin your case for Mathcad and against Excel.)

 

Success!
Luc

 

Announcements