Sunday, December 01, 2013

Electronic Calculations (eCalc's)

Over on linkedIn group:  MathCad & Excel Structural Design files share Group there is discussion about MathCAD versus MS Excel.. Some of the MathCAD users seem to have similar knowledge of spreadsheets as the advertisers who work for MathCAD: namely zip.

Advertising they push the idea that in a spreadsheet a formula always look like:


rather than


Ok! may prefer it to look something like the following, but for such a simple expression I'm not going to pay the price of MathCAD for such limited additional benefit.

But consider something more complex say a formula from the cold formed steel structures code AS4600, say equation EQ3.3.3.2(5). So first off, the code standardises the equation and provides a reference number, therefore if want to see the equation using infix notation and text book style presentation, persons who are interested can go look at the code. Secondly we are here on the Internet, so how can we present the equation, in that text book like mathematical notation like manner. One option is MathML, though according to the following article it is not supported by all browsers.

Google subtracts MathML from Chrome, and anger multiplies

Therefore first presentation of the formula is by image, the formula above was copy pasted using the MS Equation object in MS Excel and copy pasted into MS paint. So the more complex formula presented as an image is:

This image was obtained from a screen capture from Amaya, where I created the equation using its equation editor for MathML as can be seen in the following:

Given the hassle of trying to write the equation using the equation editor and get each variable into the right box, I rapidly concluded that if MathCAD is anything like the equation editor in Amaya or MS equation editor then I will stick with the single line Excel or vba like notation:

foz = ((G * J) / (A* r_01 ^ 2)) * (1 + ((PI() ^ 2 * E * Iw) / (G * J * Lez ^2)))

Any case if MathML works in your browser (firefox, Amaya should work) then the following should render similar to the image. If MathML not working then ignore, the equation is not valid.

f oz = G . J A . r 01 2 ( 1 + π 2 E . I w G . J . L ez 2 )

So that is the equation, and arguably the vertical style presentation is more readable than the single line presentation. When reading text books and industry manuals its definitely more familiar and recognisable, that doesn't however make it any easier to understand.

One thing I skipped in my little exercise was to actually time each application used. Any case since I am more familiar with Excel than any of the other applications, timing wouldn't really be fair any case. So the following are use of SMath Studio , its similar to MathCAD, I also used FreeMat which is similar to MatLAB , there are other free software variations of MatLAB: these are Octave and SciLAB. here is an interesting article on MatLAB alternatives.

Speed wise in terms of getting the number I wanted, the order was MS Excel, FreeMat, then SMath.

Here is the screen capture of the MS Excel version, which does highlight one problem, the equation presented above the result doesn't relate to the actual formula in the cell. That is largely because I copied the formula from my vba code to set up the calculation, not from the result cell.

Correcting for this here is a better view, this time with the cursor highlighting the edit bar for the calculated work cell, so that the calculated formula can be seen.

Has can be seen the formula is not based on cell references but on named ranges. Though it could be presented in the following manner. This time the formula is based on cell references, but an equation object has been presented above the result. However it is only an image of the formula, and if change the formula then it doesn't update. A better option would be too make use of XLC.

The following is the screen capture for SMath Studio, unlike Amaya, the equation editor worked by my typing in the single line format of the expression. As you type it reformats the expression. Copying and pasting an expression from elsewhere doesn't seem to work well, and editing had some problems. All up it gave me the wrong answer in the first instance. At first I thought there may have been conflict between Young's modulus E and the base of natural logarithms e, so I replace E with Y. But that didn't help, I also made some of the variables single character (I couldn't figure out how to subscript variable names, I could only find options for matrices, and I figure that would produce a different type of variable), and I removed underscores from the names. None of it helped. So I deleted the expression and started again from scratch, and that worked. I then started editing and changing the variable names back. So first lesson, just because the expression is nicely formatted, doesn't mean that it bears any relationship to the behind the scenes translation into a calculated result. The answer I got in the first instance was 9 point something: way out.

Since I got an error, I decided to track down what part of the calculation was potentially at fault. So I extended the Excel spreadsheet and the SMath calculation page, rewriting the expression as Eq1+(1+Eq2) as follows:

Excel Calculation
SMath Calculation
From this it appears that second part of the expression was not a valid calculation in my first attempt to type the equation in. It seems very sensitive to the operators used: just because it displays a given symbol doesn't mean that is the symbol to be typed in. For example need to type * for multiply not a simple dot (.) . The few times I have tried SMath it hasn't given me much confidence in the validity of the calculated result.

Whether we use Excel or MathCAD or even Mathematica , it important to understand that a great deal of translation is going on behind the scenes. To start with, we may prefer the use of infix notation for our mathematical expressions, computers on the other hand prefer post fix (or reverse Polish notation RPN), so arithmetic expressions we type in have to be parsed and translated into a stack of numbers and operators to be fed to the computers arithmetic processing unit. How the expression is displayed and how it is represented behind the scenes for calculation purposes are two different things. Similarly how the expression is input can be yet another data structure and processing task.

Our regulations require independent technical check. I would contend that independence is not achieved if designer and certifier are using the same software. Therefore both should not use MathCAD. Further just because have expression displayed, and results supposedly calculated from that expression doesn't mean that it is all valid. I contend that an independent technical check does not take place if the certifier has the designers calculations, and is reading those calculations. The certifier should have a blank sheet, for a valid independent check.

So having identified that one part of my expression was messed up by attempting to copy/paste equation segments. I decided to have a try with FreeMAT to see how cumbersome that would be, the following is the screen capture from.

Calculation in FreeMat
Other than it took a months of Sunday's for FreeMat to open in the first instance. It otherwise did the calculations quickly and with minimum problem. The only issue is that straight out, its a poor presentation.

But from another viewpoint, I spend as much time in the vba editor behind Excel as I do using Excel worksheets. So the FreeMat type environment does have its attraction, the issue for me is I have a lot of vba code, and translating to yet another application language is wasteful of my time.

There are also a multitude of simpler computer based calculators with tracing of the calculations, or specialised versions of interpreted Basic programming environment for engineering. For example to name a few:
  1. ATCalc, version 4.0 (This now taken over from my use of Calc98 using RPN via my computer keyboard or the mouse was/is a pain: it also doesn't trace (But it does have a lot of other useful features). Just typing expression into ATCalc is easier and get a trace if have results panel switched on.)
  2. Microsoft Mathematics
  3. GraphCalc
  4. Math Mechanixs
Some of these are more educational than anything else, but some times the only requirement is to get some insight into a formula by either tabulating input/output or otherwise graphing various parameters, and the educational tools are probably good enough for that purpose.

ATCalc with results panel switched off, simple and unobtrusive

Now personally I believe cell formula are error prone and cumbersome, especially given that the codes of practice have a multitude of conditional tests to carry out to select the most appropriate formula. Additionally compared to a database management system (DBMS) like MS Access, using MS Excel is actually inefficient for generating tables. It is better to define the formula once and apply to consistently to every record rather than replicate the formula. An alternative to this is to use something like VCmaster which has a better approach for tables and avoids the bloat of using a DBMS for simple tables.

I don't use cell formula I use vba functions and central library TechLIB, as mentioned in earlier posts. Using such the spreadsheet would look something like:

Yes I know, no formula present and cannot check. But I reiterate, reading it, is not a valid way of checking the calculation. Need to check that the calculation is relevant to the technology being assessed and that the arithmetic results are correct. The certifier or reviewer should redo the calculations from the input parameters. The designer has to carry out multiple iterations to find a potential solution, the certifier only needs to check that the proposed solution actually is a valid solution: one iteration. Software allows rapid checking, no need to start writing calculations out with pencil and paper and start bashing numbers through a calculator. We need to build solid foundations, reliable heritage to build up on for the calculation process.

Now in using a function library, can use the following method. First insert function, by pressing fx on the edit bar, to get the following dialogue box.

Choose user defined functions and select the function. Which if they are you own functions are easier to find, than if someone else's. If I knew how to add the help to the functions, I'd might consider including it. Any case from here can choose the function, and get the following assistance.

Press the right vertical scroll bar to see additional parameters.

From here can then go and select the appropriate cells, and get to see the values selected as well as the final result of the function, before accepting and committing it to the worksheet cell.

Now as far as I know at the end of the day when want to get a real job done, then mathematical notation is thrown in the bin, and the constructs of high level programming languages like Fortran, Pascal, Basic are adopted. The difference comes down to the available function libraries available to the application, and what want to do with the resultant application.

For me, the important criteria is being able to write a stand alone application which can be handed over to persons who are not engineers. Further, the custom application shouldn't have a massive runtime application to work: Java and .net have runtime application's but these are more readily available than say the runtime for MatLAB.

Currently I have the hassle that I thought I could make use of my Excel/vba, by adopting as stand alone programming environment, but too many differences between the languages. So become an issue a of whether to go back to Delphi, or move over to Java or C#. I don't like the idea of C++, pointers in Turbo C always gave me a problem and the syntax for record type data structures seemed untidy and cumbersome.

Put simply I disagree with MathCAD's white paper that I read some years back, and the benefits of using MathCAD. The benefits have very little to do with the text book type presentation of mathematical expressions and such presentation is not all that clear in any case. Personally for practical purposes, I prefer integrals presented as summations, and further I prefer summations presented as "for loops" rather than using a sigma symbol. And that's another issue. It does not help very much to use the lower case sigma symbol for stress and standard deviation. Now some who write programs would tend to create a variable called sigma, rather than name it stress. Similarly they would call it sigma rather than standard deviation, and additionally call the summation function sigma. Thus got a real mess when statistically assessing the results of experiments involving mechanical stress: as everything is called sigma.

Dreaming up new variable name, or creating arrays and matrices is largely avoided in MS Excel and other spreadsheets, as working in an array of cells in the first place. So simply point to and use a cell value on an as needs basis. MS Excel fits in better with what the individual was going to do with their calculator, especially their RPN calculator, as the cells represent the stack or registers of such calculator, but have the advantage of labelling each register. The calculations can be saved as a template, and can be modified on an as needs basis to be made more and more presentable. However even with Excel, still need to understand that their are different presentation requirements for different purposes and different audiences. Those who push MathCAD only seem to recognise one audience and one purpose for calculations.

And that narrow view represents a major obstacle in moving forward with calculations, as we should be enabling and empowering others to get the calculations done, with out being concerned about the details of how got from input to desired result. Most people just need results with which to make decisions.

This IStructE forum thread for example includes a lot of annoying people. It does not take 4 years to learn how to calculate the base moment to a simple cantilever post: its high school physics stuff. Further more now that we have limit states design and use member resistances rather than check stresses, once the design moment is calculated, can often look up member resistance in manufacturers tables. My question would be why the drafter asked the question if they already had calculations from an engineer? It would seem either his employer never receives the printed calculations: or maybe for some reason they are not easily read. Chances are they just get a certificate. My view is that they should have someone on staff to do the calculations, and it doesn't require an engineer to do the calculations: Australia's 2 year qualified engineering associates are more than capable. But then again engineering associates are not drafters, nor are they WFEO technicians.

Any case the attitude shown was largely obstructive not helpful. But it does highlight need to consider audience and purpose of calculations. In-house the calculations need to present the theory, and detail, as informative training materials for each new generation of designers taken on by the business. But for the regulatory system such repetition of calculations represents a waste of paper or hard disk space if printed to pdf, and a waste of  time to read and to check. If have appropriate software, then both designer and certifier can get their answers and reach their respective decisions quickly.

Checking using  firefox, the MathML in the blog post displays correctly, though possibly too small.