Excel has become an indispensable tool for many designers and engineers for a number of reasons. For starters, it can handle many complex design and engineering calculations. Also, it is very good for creating and managing tables of data. And finally, Excel can be linked to almost any CAD system. Note that this can help you automate your design process.

So if you are a designer or an engineer chances are you will end up using Excel. We at CADmech have been using Excel for many applications and would like to share some tips with you.

Data Validation and Drop Down Lists

Data Validation is a tool that helps you ensure that the data that is being entered is correct. For example if you don’t want to allow negative numbers in a cell, you can set up a limit range so that only positive numbers are allowed. This is very useful for CAD, since many CAD tools don’t allow negative values for lengths.

Another way to ensure the data you enter is valid is to add a Drop Down List so that your choices are limited to the items in the list. For example, let’s say you had to enter a voltage then your choices may be limited to 120V or 347V as per the picture below.

Look Up Tables

In mechanical design when you change one parameter usually you need to change several other parameters that are affected. For example if you make a structure higher you may need to have additional supports. One way to handle these changes is by using IF statements. However, IF statements specially long ones can become cumbersome to manage. A better way to manage several design parameters is via Look Up Tables.

Look Up Tables allow you to store information in table format and then pull the information when needed.  For example, let’s look at the design of a combustion burner. Note that there are two types of fuel you could select: Natural Gas or Oil. The one you choose will affect several other parameters such as: the ignition system, the type of refractory, and the weight of the combustion chamber etc. If you select Oil then you will need an Oil Igniter (which is more powerful than a Gas Igniter), also you can’t use Ceramic refractory since oil can soak up in the Ceramic, so you will need a Brick refractory. Note that Brick refractory requires the installation of anchors (Ceramic refractory does not), so you will need to install anchors. Also Brick refractory is heavier and therefore heavy-duty lifting lugs are required. All this would make for a very long and complicated IF statement. Or you could use a simple Look Up Table like the one below to get the job done:

Functions

Part of an engineer’s job is to size equipment. Many times calculations are used to select equipment. But what happens when your calculations tell you that you need one and a half pumps? Well you would round up and use 2 pumps. Note that you can round a number with Excel, since Excel has functions that force you to select whole numbers, such as: TRUNC, ROUNDUP, and ROUNDDOWN.

Conclusion

Data Validation, Drop Down Lists, Look Up Tables and Functions are very helpful to designers and engineers. These Excel tips combined with CAD can become very powerful design automation tools in your collection.

- by Joseph Vera