Skip to main content
D
DJF16-Pearl
16-Pearl
September 20, 2018
Question

Looking for Excel limitations

  • September 20, 2018
  • 3 replies
  • 3362 views

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,

This topic has been closed for replies.

3 replies

M
mvenich14-Alexandrite
14-Alexandrite
September 20, 2018

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

 

 

W
Werner_E25-Diamond I
25-Diamond I
September 20, 2018

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).

 

L
LucMeekes23-Emerald IV
23-Emerald IV
September 20, 2018

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

D
DJF16-PearlAuthor
16-Pearl
September 24, 2018

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,

 

L
LucMeekes23-Emerald IV
23-Emerald IV
September 24, 2018

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

Terms & ConditionsCookie settingsAccessibility statement