OpenSource Solutions for Computer Aided Engineering Blog

OpenSource Solutions for Computer Aided Engineering

The OpenSource Solutions for Computer Aided Engineering Blog is the place for conversation and discussion about OpenSource Computer Aided Engineering (CAE) resources available for modern personal computers. There are a myriad of solutions available in the marketplace for a number of different engineering and scientific applications, but it is not always easy to find the most appropriate solution for a particular circumstance, because many of the packages emanate from University research departments or Government development projects that do not have access to sufficient resources to publicize their products adequately. While the primary focus will be on numerical analysis solutions (FEA, CFD, Signal Processing, SPICE electronic simulation, etc.), we will occasional touch on other aspects of CAE such as Computer-Aided Drafting (CAD), 3D modeling, data acquisition (Test and Measurement, etc.), and other such technologies that can add value to the engineering process.

Previous in Blog: Crunching Numbers   Next in Blog: Electrical Transient Analysis

Putting Your Spreadsheet Program to Work

Posted December 06, 2010 12:00 AM by cwarner7_11

The ubiquitous Spreadsheet is a fundamental tool of the Engineer- a basic tool more like an extensible environment for manipulating and analyzing numbers. We have in previous discussions visited some of the less common functions of the spreadsheet, such as:

  • The Fast Fourier Transform- a secret inclusion in older Excel packages (through Excel 2000) hidden in the "Analysis" package add-on that is included in every distribution of Microsoft Office (but you had to be aware that it was there in order to use it). Apparently, newer versions of Microsoft Excel load the Analysis Toolpack automatically, although I have not personally confirmed this.
  • Direct data entry into a spreadsheet, utilizing either something like the Windmill software (available in a free version, although not Open Source), with pretty good in-depth documentation for coupling this software with Excel or OpenOffice Calc; self-produced macros (if anyone is interested, send me a PM and I will share a simple one I wrote a few years ago).

I have recently come across an example of utilizing a spreadsheet for running a simple Finite Elements Analysis that further demonstrates the flexibility and extensibility of the basic spreadsheet. This is an example found in the book "Electromagnetism for Electronic Engineers – Examples" by Richard G. Carter, available from This example intrigues me because it demonstrates how to use a recursive iterative solution that allows a cell to use its current value for calculations (usually blocked with the "circular reference" error). One must set the iteration parameters in the Options menu (In OpenOffice Calc, this is found in "Tools/Options/").

Spreadsheet programs come in an overwhelming variety of flavors, of course, and not all are OpenSource solutions. I personally have a strong preference for the older Microsoft Excel 2000 version, for two reasons. First, the user interface is very simple and clear (and many years of using this version have made it very intuitive for me- newer versions with more "sophisticated" user interfaces tend to confuse me, and I would rather not bother with learning old skills all over again). The second reason I prefer this particular spreadsheet version is the speed with which it renders graphs, especially when one has a very large dataset. When rescaling to get a close-up view of a particular segment of the data, for example, Excel 2000 is blindingly fast compared to newer versions of Excel, or compared to any other solution I have tried (even when running it in a Virtual Machine or Wine in Ubuntu). For more mundane applications, I like the OpenOffice Calc solution.

Some spreadsheet packages are specialized for specific industries, while others tend to be more general purpose. In addition to the OpenOffice Calc which comes with the OpenOffice suite, one can find a 13-page list of various spreadsheet projects for a variety of different specialties on SourceForge. A short sampling:

Where the utility of the spreadsheet really shines is in the extensibility through macros. Macros are programs (in Excel, written in VBA, a limited version of the Microsoft Visual Basic language; in OpenOffice, written in an OpenSource version of Basic) that run inside the Spreadsheet environment to provide additional capabilities for analyzing or manipulating data, formatting forms, and other such functions. This is also where spreadsheet applications move into the realm of Free and Open Source Software, in that there are a plethora of "Spreadsheet Solutions" available on the web, designed for any number of applications, normally with the code of the underlying model accessible and modifiable by the user to meet specialized requirements. Many manufacturers provide "calculators", either for use online or offline, to promote their products.

These free (and some that must be purchased) spreadsheet tools for engineers can be hard to find. Some places to start looking:

OpenOffice provides some really detailed documentation for developing your own macros.

What sort of spreadsheet solutions have you found most useful? Do you have a macro you have written that you would like to share? Let us know!

Disclaimer: Not all of the listed software has been tested by the author, and the user should be aware that the Bad Guys sometimes use macros to pass on malicious software. Beware with this stuff! If the site looks suspicious, don't take a chance.


Interested in this topic? By joining CR4 you can "subscribe" to
this discussion and receive notification when new comments are added.
Engineering Fields - Aerospace Engineering - Member United States - Member - Army Vet in the aviation industry

Join Date: Mar 2008
Location: Bridgewater, Va.
Posts: 2065
Good Answers: 113

Re: Putting Your Spreadsheet Program to Work

12/07/2010 2:36 PM

Great resources.

Thank you.



Join Date: Jan 2008
Location: Allen, TX
Posts: 107
Good Answers: 2

Re: Putting Your Spreadsheet Program to Work

12/08/2010 8:40 AM

Didn't know there was a Gantt Chart generator for Excel. That'll be my new toy for today. Thanks!

Panama - Member - New Member Hobbies - CNC - New Member Engineering Fields - Marine Engineering - New Member Engineering Fields - Retired Engineers / Mentors - New Member

Join Date: Dec 2006
Location: Panama
Posts: 4273
Good Answers: 213
In reply to #2

Re: Putting Your Spreadsheet Program to Work

12/08/2010 10:52 AM


Give us a review of your opinion of this Gantt Chart generator, please! I, personally, use Planner which is a Linux solution, but I would think the spreasdsheet solution would be quicker to set up for preliminary planning. On the other hand, Planner allows me to include notes and comments, etc. How easy is it to determine the critical path for a project with the spreadsheet solution? How about features for assigning resources, and tracking resource allocation (especially for multiple project environments)? One issue I always face is that I wind up allocating critical resources at 250%!

Thanks for your comments.

Reply to Blog Entry 3 comments
Interested in this topic? By joining CR4 you can "subscribe" to
this discussion and receive notification when new comments are added.
Copy to Clipboard

Users who posted comments:

cwarner7_11 (1); Hooker (1); logan (1)

Previous in Blog: Crunching Numbers   Next in Blog: Electrical Transient Analysis