If we put Mathcad and Excel in a boxing ring, toe-to-toe, who would come out victorious? What can you do in one, and not in the other? What can you do better in one, and not the other? Why do you like one over the other?
Photo credit: Generationbass.com
Ready? Let's get it on!! [ding ding]
if(0.1+0.1+0.1=0.3,"True","False").
Try it in MathCad and Excel with variables.
Enclosed file does it with both an Excel component and Mathcad using variables in the same MathCad worksheet.
One of them gets it wrong even if you could get it right in your head. It reads right. It looks right. It is auditable. That is a possible danger.
But I repeat - you need to know your products.
EXACTLY. For some reason Mathcad is not calculating 0.1+0.1+0.1 as exactly 0.3, but pretty close. I'm not sure if this is a bug.
I believe the ARIAN space rocket program costing several billion dollars once lasted a few seconds in flight and you may like to research why?
I haven't got the time to look this up, but I'm sure it's been covered before.
Have a look at the attached sheet. Both true.
Mike
Mike Armstrong wrote:
For some reason Mathcad is not calculating 0.1+0.1+0.1 as exactly 0.3, but pretty close. I'm not sure if this is a bug.
No, it's not a bug. Internally, Mathcad represents numbers in binary, with limited precision. The values used here don't have an exact binary representation to the level of precision used by Mathcad, so it's not entirely surprising that it doesn't think there is an exact match (in fact, if you subtract the answer from the sum and increase the display tolerance, you see that the diffference is around 5.55.10^-17 in M15). If you do the comparison in Mathcad 11, which held numbers to a lower precision than M14/15, you (fortuitously) get an exact match (ie the if test comes up True). Replace the numbers by 0.125 and 0.375 (which have an exact binary representation) as appropriate and M14/15 give True.
This highlights the (well-known?) fact that one should never do exact comparison tests when using Reals. Compare absolute value of difference against an acceptable tolerance.
Alan
This highlights the (well-known?) fact that one should never do exact comparison tests when using Reals. Compare absolute value of difference against an acceptable tolerance.
Good point Alan and that's what I was trying to point out. For an example so simple was it worth comparing the two pieces of software.
You can always change the settings as I did.
Mike
if(0.1+0.1+0.1=0.3,"True","False").
Try it in MathCad and Excel with variables.
If I had, I would have got "True" for both cases, because I would never set the worksheet options to "Use exact equality..." except in very special circumstances. That is a very dangerous setting when doing numeric math.
One of them gets it wrong even if you could get it right in your head. It reads right. It looks right. It is auditable.
I'm not sure I would call it "wrong" (in the context of your worksheet settings it is correctly telling you that the binary representations of those numbers are not the same), but I agree that it is very misleading. It would indeed be hard for an auditor to find the problem (and impossible in a pdf printout of the worksheet). It makes me think about the possibility of a component that you could put at the top of a worksheet that would flag if any worksheet setting in non-standard. I think that would be possible by sifting the XML of the file, but it would be a lot of work to write it.
Might be very hard to spot buried in a complex problem.
Perhaps a moral of the story is "don't use exact equality checking in complex problems"
I run a website called www.ExcelCalcs.com where we help people make calculations using Excel. We have a software addin called XLC which produces equations automatically from cell formulas. For most people this is the main advantage of Mathcad. We also use templates to help people manage units and create professional engineering calculation sheets. It has become a thriving community where engineers share calculations in a repository and discuss issues in the forum. I think it is fair to say that I am a little biased but I genuinely believe that Excel with our XLC addin leaves Mathcad for dead.
John, thanks for sharing your view and opinion. Welcome to PlanetPTC.
-Dan
Community Manager
Well, I only spent 10 minutes looking, but:
1) It only shows the equations in math notation. You still have to enter them in much the same way I used to enter equations in Fortran on a PDP11, which is painful very prone to error. In fact, unless I misunderstand how your add-in works, you have to add even more functions into the line of text.
2) It only shows one equation. There is nothing that shows the flow of the calculations. To figure that out you still have to hunt and peck through the cells to make sure what follows from what.
3) You also can't add any figures, graphs, or text to the (non-existent) workflow to clarify what you are doing.
So OK, your add-in makes it slightly easier to debug a worksheet, but it does not produce a readable document with standard math notation, text, figures, graphs, etc, all laid out in a way that shows the logical workflow (let alone allow you to enter everything in such a format). It's certainly a nice improvement for users of Excel, but when all you have is a whale oil lamp a candle is a nice improvement too
I think it is fair to say that I am a little biased
Given the statement "I genuinely believe that Excel with our XLC addin leaves Mathcad for dead" that's not only obvious, it's an understatement.
Thank you Richard for your response. I certainly don’t intent to not comply with your small print - I guess I am as guilty as anyone clicking on the ‘I agree button’ without actually reading the terms and conditions. If you wish to remove my comments I can understand your reasons but if you bear with me I would like to address the points you have raised.
1)
Given what we have achieved at the site using XLC I think your comment about XLC being a candle seems to me to be a little ungenerous but I accept that this is your initial response having only been on the site for 5 minutes. And to show you that Excel is far from being an oil whale lamp - have you seen our 3D beam finite element Excel spreadsheet? - maybe this is our knockout punch? Or maybe it is just the weight behind our punch because everyone has Excel and everyone can read an Excel file and reuse it.
I certainly don’t intent to not comply with your small print - I guess I am as guilty as anyone clicking on the ‘I agree button’ without actually reading the terms and conditions.
It's not my small print, it's PTCs. I subsequently deleted it (I thought I did so before you would even see it, but evidently that was not the case) because Dan, who is the forum admin, started this thread. So I figured that even though your post appeared rather overtly commercial, in a way he asked for it.
Cell formula don’t change – I agree but generating formulas in Excel is so easy simply by clicking on cells, much faster than typing in Mathcad.
I disagree 100%. For a trivial formula it's trivial to type it into Mathcad or Excel without making a mistake, and if you are used to the Mathcad editor it's just as fast as typing text. But if you have a very complex formula typing it into Mathcad with no errors is enormously easier and faster. Type this into Excel without errors:
It's trivial to do so in Mathcad (it is a real formula, by the way, not something I just made up). Notice that it's also defined as a function of two of the variables. I admit I don't even know how to define a function in Excel (other than by resorting to VB, anyway), but I assume it must be possible. Can you create and display (and of course subsequently use) such a function using XLC?
Calculation Flow – It does not just show one equation it shows as many equations as you like.
Yes, sorry, I didn't phrase that very well. Yes, it's clear from your site it shows multiple equations, but what I meant is that it shows them individually. There is no obvious connection between them. A formula in one cell can refer to any other cells, above, below, to the left, to the right. There is no way to know without clicking on the cell and checking which other cells it refers to. Therefore there is no obvious workflow.
You can’t add any figures, graphs or text – well the sample calculation for spring design shows it certainly can. In fact you use it just like Excel with text, pictures and charts but you just get the opportunity to add mathematical equations. You might like to take a look at a YouTube video I made which show just how easy it is to make visually exciting engineering documents:
OK, fair point. Of course you can paste picture into Excel and create graphs, but again, there is nothing intrinsic to Excel that means you can tell what the workflow is. Where did the graph come from? Data above the graph, or below it? And although what you show is a great improvement over the native abilities of Excel, the formatting still looks forced (such as the big gaps between the variable name on the left and the number being assigned to it)..
And all of the above only really scratches the surface. For example, can you define integral functions such as this?
Also, how do you handle matrix math? Can you do something like this:
Or this (it's the pseudoinverse of K, used in least squares solutions):
Of cousre, in Mathcad that equation works. I can look at the contents of the calculated matrix quite easily:
And I can do so would having to display the entire thing in one go. In this case that's good, because iin this particular example it's 8 x 3112. As far as I know (and I could certainly be wrong about this), you can't hide a block of cells in Excel. You have to either hide all the columns or all the rows. And I know of no way to display a matrix as above,. so that I can see it without it sucking up huge amounts of screen space (which means I can see it in the context of my workflow). Also, that's quite small compared with some of the data sets I have. Do current versions of Excel have more than 65536 rows and 256 columns? I can't even fit a lot of my data sets into that space, making it impossible for me to use Excel to analyze or manipulate them.
And of course, Excel has no symbolic math, no unit handling (I realize you have something for that, but does it really do what Mathcad's unit handling does?) limited 3D plotting (although in some ways the 2D plotting is better), limited high level functions...
everyone has Excel and everyone can read an Excel file and reuse it.
Yes, as pointed out higher in the thread, that is an advantage of Excel.
Hi Richard I guess I realised jumping into a Mathcad forum I would be up against it from the start. I just thought Excel was getting a hard time particularly at the top of the thread. My honest opinion is that as engineers we choose the tools we work with and provided they come up with the answers we need.
Turkeys don’t vote for Christmas and Mathcad users won’t change a tool they love but I just want to say I have yet to find a mathematical problem I can’t get Excel to solve – it’s no accountant’s tool! I prefer Excel because I can share my mathematical ideas with anyone so, for me, this is the trump card.
John Doyle wrote:
Hi Richard I guess I realised jumping into a Mathcad forum I would be up against it from the start. I just thought Excel was getting a hard time particularly at the top of the thread. My honest opinion is that as engineers we choose the tools we work with and provided they come up with the answers we need.
Yes, I agree. Provided we come up with the answers. But not all of us do the same thing, and Excel cannot do some of the things I need.
Excel does not have integral functions but if I can’t find a solution using SAGE then I can do a numeric integration either in Excel or in VBA.
The fact that you have to resort to SAGE points up a big hole in Excel. Writing a decent numerical integrator in VBA would be a big chunk of work, and because VBA is only an interpreted language it would be slow. And a primary reason I use Mathcad is so that I don't have to write in a language like VBA. And the more complicated the calculations / analysis become, the more of these holes appear. The Fourier transform functions in Excel are weak, there are no ODE solvers (I think there is actually an add-in for this, but even if there is, could I do something like a least squares fit of a differential equation to data?), no cubic spline functions (that's a really huge limitation), etc. How do you handle multidimensional arrays in Excel (Mathcad does not do a great job of this either, but it can handle them)?
- Excel 2007 now supports sheets bigger than 65536 rows and 256 columns. I could not tell you how large it is but I have never reached it.
Yes, I found it on the web. It now has 1,048,576 rows. That is still not enough. Just recently there was a thread where the data arrays had 1,638,400 points: http://communities.ptc.com/message/165738#35243. Sorry, but for some types of calculation, Excel just can't do it.
- Regarding units I use a template to allow unit switching but there is also the ChangeUnit addin that will handle every conversion imaginable and I think units present a relatively trivial problem to most calculations.
But does it do unit checking, so that it traps math errors because of unit mismatches? Can it do dimensional analysis? Simplify units in the result?
Turkeys don’t vote for Christmas and Mathcad users won’t change a tool they love but I just want to say I have yet to find a mathematical problem I can’t get Excel to solve
Well, anything involving very large numbers of data points, for a start. For what you do, I accept that may be a non-issue, but for some of us it is a very real issue. And it's not just a question of whether, ultimately, Excel could solve it. It's a question of how easily. Mathcad is a tool I use to solve problems, sometimes very complex problems, and I have no interest in writing VBA programs to do numerical integration, cubic spline interpolation, etc. Any tool I use has to have that built in. I am sure that some Mathcad users could switch to Excel without running into those sorts of problems, but I am equally sure that many other Mathcad users could not. People use Mathcad for a lot of things, not just mechanical engineering calculations (I'm a physicist).
it’s no accountant’s tool!
No accountant would use Excel nowadays, even if that was the best thing available when Lotus 123 was created, and MS (and Borland) subsequently copied it. They use real accounting software, one example of which, by coincidence, is call Sage: http://www.peachtree.com/
But Lotus was designed for office use; it was designed for tasks that involve looking at columns and tables of numbers, and graphing them. And if your need is basic bookkeeping, or to look at things such as change in market share by region, it's a much better tool than Mathcad (or SAGE, or Sage). But it was not designed for involved science and engineering calculations, and it's fundamental design has not changed. There have been improvements, and tools have been added to improve it's capabilities in that regard (such as your own very excellent tools) but in my opinion it is still a case of using a big hammer to put a square peg in a round hole..
I prefer Excel because I can share my mathematical ideas with anyone so, for me, this is the trump card.
We all agree that the ability to send someone a document where they can change the numbers is a big plus. But for me the trump card for Mathcad is that Excel can't do some of my calculations, so there would be nothing to send.
I admit I don't even know how to define a function in Excel (other than by resorting to VB, anyway), but I assume it must be possible.
I was sufficiently curious about this that I looked in Excel's help and searched the web. As far as I can work out VBA really is the only way to create a user defined function. I still have a hard time believing this though. Can someone that knows a lot more about Excel either confirm or deny this?
VBA is one way to produce a user defined function. The other way is to use C++ to write a DLL or an XLL which Excel can access as an add-in.
The 64-bit systems can use more virtual and physical memory than 32-bit systems. Microsoft Office 2010 introduces native 64-bit versions of Microsoft Office products to take advantage of this larger capacity. For example, this additional capacity is needed only by those Microsoft Excel users who require Excel spreadsheets that are larger than 2 gigabytes (GB). I think this should be adequate for even very large computational problems. My money is still on Excel.
VBA is one way to produce a user defined function. The other way is to use C++ to write a DLL or an XLL which Excel can access as an add-in.
Ouch. Since you wrote a very nice add-in you are obviously a competent programmer, and therefore obviously understand the importance of reusable code in large projects. Based on the posts on these forums, I think the majority of engineers (and also a large number of scientists) either don't know how to program in VBA at all, or are barely proficient in it. And almost none know how to program in C++ or C#. And VBA is a poor language for handling large data sets, because it's interpreted. If you were to sift through the Mathcad worksheets posted to these forums you would find that almost all of them are written as a series of dependent functions. It's by far the best way to break down a complex problem, and avoids large amounts of repeated code. Not having this capability in Excel, at the worksheet level, is a major limitation. It would make it really painful to use Excel for some types of problems.
The 64-bit systems can use more virtual and physical memory than 32-bit systems. Microsoft Office 2010 introduces native 64-bit versions of Microsoft Office products to take advantage of this larger capacity. For example, this additional capacity is needed only by those Microsoft Excel users who require Excel spreadsheets that are larger than 2 gigabytes (GB). I think this should be adequate for even very large computational problems.
You are still limited to just over 1 million rows though: http://msdn.microsoft.com/en-us/library/ff700514.aspx. So if you have a data vector with 1.5 million elements it doesn't matter how much memory is available. Excel can't handle it. Granted, there are a lot of fields where this is largely irrelevant, but there are also a lot of fields where data sets of this size, or larger, are common.
I think what is emerging in our discussion it that you are clearly pushing computational boundaries of pc based software. My brother (another physicist) is involved in developing grid computing for the LHC at CERN. You Physicists certainly like your computing power.
As an engineering analyst I get involved in complex computational problems compared to most other engineers and for me Excel takes all my problems in its stride. I would say the might be in the top quartile of people requiring computational power – you are clearly way beyond me – but for most people and certainly for most engineering problems Excel copes well.
but for most people and certainly for most engineering problems Excel copes well.
I couldn't disagree more. Excel copes well, but IMO Mathcad is a much better tool for engineering calculations. I work for a Subsea Pipeline Installation company and Mathcad is the tool of choice for most engineers.
Mike
It is clearly a matter of choice, both Mathcad and Excel do a good job for engineers but to use Mathcad you need to have access to it and know how to use. Excel on the other hand is on everyone's machine and they already know how to use it.
I took a survey of engineers using the ExcelCalcs site back in 2009 and I asked a simple question “what is your calculation tool?”
70% use Excel, 13% use pen and paper, 11% use Mathcad, 2.6% use Mathematica and the remainder used custom programming. So in terms of winning the prize-fight I think the number of people voting with their feet says it all. I am sure that the marketing guys at PTC already know this.
I'm dismayed that MatLab didn't show in your survey. MatLab and Mathcad are competitors in my workplace for large numerical efforts, with EXCEL used to publish and share results.
Excel on the other hand is on everyone's machine
It's on most people's machine (although, as I pointed out in another post, XLC is not)
and they already know how to use it.
For very basic stuff, that's probably true. But I don't think many people "already know" enough about Excel to be able to solve complex engineering problems in it. Especially not if that requires that the user resort to programming in VBA.
It seems to me that at this point the one and only advantage for Excel that anyone has come up with is that more peple have it. Everything else is, at best, an attempt to demonstrate that "Excel can do that to". I cannot think of anything that Excel can do that Mathcad can't, and very few things where it's better. It's better for looking at columns of numbers. What else?
So Mathcad is a better tool for scientific and engineering calculations. In my opinion, a much better tool. More people have Excel, but while that is an advantage it does not make Excel a better tool than Mathcad. It just makes it a more common tool.
It seems to me that at this point the one and only advantage for Excel that anyone has come up with is that more peple have it. Everything else is, at best, an attempt to demonstrate that "Excel can do that to". I cannot think of anything that Excel can do that Mathcad can't, and very few things where it's better. It's better for looking at columns of numbers. What else?
Good point Richard.
Mike
John Doyle wrote:
It is clearly a matter of choice, both Mathcad and Excel do a good job for engineers but to use Mathcad you need to have access to it and know how to use. Excel on the other hand is on everyone's machine and they already know how to use it.
I took a survey of engineers using the ExcelCalcs site back in 2009 and I asked a simple question “what is your calculation tool?”
70% use Excel, 13% use pen and paper, 11% use Mathcad, 2.6% use Mathematica and the remainder used custom programming. So in terms of winning the prize-fight I think the number of people voting with their feet says it all. I am sure that the marketing guys at PTC already know this.
This is interesting! Your poll reminds me of the polls for the Truman/Dewey election in 1948; the respondents were selected from telephone directories, which resulted in a shift in the polled population with respect to the voting population. The resulting polls predicted a Dewey victory--highly embarassing for the pollsters!
You polled a web site for prople using EXCEL. Only 70% of your respondents admitted to using EXCEL as a calculation tool; why were they on the web site to take the survey?
What percentage of PC's arrive on an engineer's desk without EXCEL installed? The fact that 30% of the engineers responding to your survey (of engineers using an EXCEL web site) used another "tool" for calculation is significant.
This is a fair comment Fred. I imagine the PTC marketing department have more reliable figures. I don’t know if anyone could get them to comment? My own site demographics show that visitors arrive via Google when searching for solutions to engineering problems in our Repository. In my own experience, and this might come as a shock to a forum full of Mathcad users, is that not many engineers have it.
It is clearly a matter of choice, both Mathcad and Excel do a good job for engineers but to use Mathcad you need to have access to it and know how to use.
It is a matter of choice and over the past four years I have converted several engineers from using Excel and they have never looked back.
Excel on the other hand is on everyone's machine and they already know how to use it.
It's on most machines, but not everyone's and I very much doubt most people could use Excel to an advanced level.
Mike
Some of the contributors in this forum actually are rocket scientists, but most of us are not.
I do complete structural designs for for buildings entirely in Mathcad, with the exception of when I need an FEM package.
I string together many standard functions that I have written for design of elements (beams, columns, connections, etc.) and tie them seamlessly together with calculations specifically done for a given project. For example, I accumulate reactions and column loads in vectors, and apply them as needed to supporting beams. If, for example, I change the live load at the begriming of the project, it will update thought the job, I do not have open any other programs and re-run, or transfer numbers: just review results.
When I am done, I have, in essence, a program for the building.
Whatever you use, the calculation book for even modest buildings can easily be a couple of hundred pages long.
If I need to integrate over a non-umiform grade beam with overturning, I can easily do so, and I don't need to know anything about numerical integration or VB, I just need to know how to set up the problem. I can easily check soil bearing, and calculate the reinforcement requirements along the length as a function of x. (and again, if I revise the design loads or make some other change, this all updates as well)
Most Structural Engineers who use Excel, do not do it this way. Most of the Excel templates are run as individual routines where input and results are transferred to and from the template by the user. I see some of examples on your site are set up the same way as well. Sometimes the templates are stacked in one file, but mostly not.
In Mathcad, tracking reactions, etc., as variables throughout the project is natural and easily understood. I am sure the same can be done in Excell, but I don't think its as natural as Mathcad and much more error prone.
Saying that you can do all of the above with addons or VB programming, is, for me anyway, an admission that Excel is not the best tool for what I do.
Structural Engineering is complicated enough with design in wood, steel, reinforced concrete, reinforced masonry, prestressed concrete, glass, fiber reinforcement, foundation design (footings, mats, slabs, piers, casions, piles, retaining walls, reinforced earth, liqufaction, creep, expansive material, water table, flood loads) , earthquake loads, wind loads, crane loads, vibration, connections, inelastic response, temperature loads, pier reviews, building authority reviews, mosquitos (Lawyers with wings), in adequate fees and time. I don't need to add VB programming, add-ons and trying to track or name cells, I need to clearily see the calculations, unit checking and results as variables.
The primary reason Excel is used in Structural Engineering is primarily to its use in the past. It's hard to change when you have a large investment, but change you should.
Wayne I am very much like you. Your chosen calculation tools are an FE package and Mathcad mine is an FE package and Excel (with the XLC add-in). I agree that native Excel is more error prone but using Excel with the XLC add-in equations are generated from cell formula so I get an immediate verification of the cell formula is correct. Thus Excel becomes far less error prone putting it back on a par with Mathcad in this respect. I also have the certainty of knowing when I send my calculation to a colleague or a client he can open an Excel file but this is not so certain with a Mathcad file.
I also have the certainty of knowing when I send my calculation to a colleague or a client he can open an Excel file
Once you have XLC functions in it is that really the case? I would have thought that unles your colleague has XLC your worksheet will not work.
The main focus behind our work is to spread mathematical knowledge, that’s why we use Excel and that’s why XLC allows you to save files as native Excel files just in case your colleague does not have XLC installed. Excel formulas still work and the equations become simple Excel graphics.
XLC allows you to save files as native Excel files just in case your colleague does not have XLC installed. Excel formulas still work and the equations become simple Excel graphics.
I see. Nice touch
I think what is emerging in our discussion it that you are clearly pushing computational boundaries of pc based software.
For some of what I do perhaps, but not for most of it. I wouldn't consider one vector of 1.5M points as exactly pushing the limits.
As an engineering analyst I get involved in complex computational problems compared to most other engineers and for me Excel takes all my problems in its stride. I would say the might be in the top quartile of people requiring computational power – you are clearly way beyond me – but for most people and certainly for most engineering problems Excel copes well.
Lot's of engineering problems involve integrals that have no analytical solution. So they must be solved numerically, and you already said Excel can't do that. And you can't define functions except by writing them in VBA. Presumably your add-in does not also convert VBA into standard math notation, so that brings us right back where we started. It's difficult and error prone to enter complicated math expressions, and equally difficult to audit them or understand what someone else has done. Both of the above limitations apply to a very wide range of engineering problems, and they are not the only limitations of Excel.
The maths behind most engineering problems is quite simple. We don’t spend much time solving integrals or finding solutions to second order differential equations. Rarely do we resort to VBA and user defined functions. For more complex problems we would probably turn to specialist software like Finite Element. Most engineering calculations require us to follow standard design methodologies which are published as codes of practise and national standards. For this Excel with XLC works a treat showing a logical calculation process and that we have followed the appropriate standards. You'll see a lot of examples in the site repository.