Written by Brent Maxfield
• 10/6/2023
• Read Time : 6 min.

Over the years, I have had numerous conversations with engineers about which program is better for engineering calculations: PTC Mathcad or Excel. My answer is usually, “They are both great programs, and I regularly use both of them.” They both have unique features that make each one better suited for specific needs. As you might expect, I heavily lean toward Mathcad.

The wonderful thing is that with Mathcad, you can take advantage of both worlds. Excel spreadsheets, with full functionality, can be embedded into your Mathcad worksheets. In this case, you can have your cake, and eat it too. When an Excel file is embedded within Mathcad, Mathcad will feed values to specific cells within Excel. Excel will do its processing, and then Mathcad can extract results from specific Excel cells.

A full discussion of Mathcad and Excel would be much longer than I can write in this blog. In fact, each of my books devote an entire chapter to discussing this topic. This blog will focus on the power of Mathcad/Excel integration, but it will not provide specifics on how it is done. There are many resources available to help you learn to do what I will show in this blog. I will discuss the Excel Component and the READEXCEL, and WRITEEXCEL functions.

Note Excel must be installed on the computer you are using to use the Excel Component. You do not need Excel to be installed to use the READEXCEL and WRITEEXCEL functions.

Excel Component

The Excel Component is an Excel worksheet embedded within your Mathcad worksheet. When you double click on the component, Excel opens and you can create your spreadsheet as you normally would in Excel. When you close the worksheet, it is now embedded in Mathcad.

To bring in an existing Excel spreadsheet, you must open a blank Excel Component in Mathcad, and then copy all the cells from your existing Excel spreadsheet and paste them into the Excel component.

The below example is a rather simplistic calculation of the stress in a simply supported rectangular beam. It is easy to do in Mathcad, and more difficult to do in Excel, but it illustrates a few concepts, such as inputting data into Excel, the importance of understanding how units affect the input values, and how to get results out of Excel.

In the following example, Mathcad is using metric units, but Excel is expecting values representing feet and inches.

Note how the Excel inputs are divided by feet and inches as in the previous example, even though metric units were input into Mathcad.

The output is still multiplied by psi, because that is what Excel produced. This value can then be displayed in metric units.

It is CRITICAL to understand the values Excel is needing, in the units it is expecting. Do this by dividing the Mathcad value by these units when creating the Excel input.

The above two examples are simple. You may create complex Excel components, but the concepts are the same.

2. Know what units Excel needs, and divide the Mathcad value by the desired unit.
3. Determine the cell addresses of the Excel output you want to bring back into Mathcad and what units the data should have.
4. Attach the same units to the Excel output.

If you have existing Excel spreadsheets, copy the Excel cells and paste them into a new Excel component. It is a good idea to protect all cells of the Excel spreadsheet except the cells needing input, so that you do not accidentally overwrite a value or formula.

The READEXCEL function is very useful if you have an Excel table of data that you want to bring into Mathcad. For Example, AISC has an Excel file with the steel properties of all the AISC beam sections. It has more than 70 columns and has more than 270 rows.

The following examples shows a small portion the data from this Excel file that was brought into Mathcad. It is brought in as an array and all data from the Shapes database now resides in Mathcad.

You can also extract a column vector from the Shapes matrix. This will allow for easier data manipulation and searching.

WRITEEXCEL Function

The WRITEEXCEL function can be used to export a matrix or a series of matrices to one or more Excel files.
Another use is if you were performing a series of runs in Mathcad and wanted to capture the inputs and results of each different run in an Excel spreadsheet.

The below example takes the vectors Area and Label from the above example and creates a new Excel file called AISCData. It writes the Label in column A and writes the Area in column B.

Note that the [1] in the function definition refers to the first sheet in Excel. If you are writing to the first sheet, this can be eliminated. Use a [2] if you want to write to the second sheet, etc.

Here is a screenshot of the created Excel file.

When to Use Each Excel Integration Option

Let's compare the Excel Component and the READEXCEL function, and discuss when each would be appropriate.

The Excel Component is needed if you want to use Excel to perform calculations and then have Mathcad extract the results. It allows you to take advantage of Excel functions and features. The data and numbers inside Excel are not available to Mathcad until you extract the data using the Outputs area of the Excel Component. The Excel Component does not maintain a link to any outside Excel file; it embeds and stores the Excel data inside Mathcad.

If your Excel file has a combination of calculations and data, then the Excel Component would be best to use. The data portion of the Excel spreadsheet can be brought into Mathcad from the Outputs area of the Excel Component. You will need to provide cell addresses of the upper left data cell and the lower right data cell. An example of this would be a loan amortization schedule that uses loan rate, loan terms etc. to create the schedule. Using the Excel Component, you could provide Excel with the loan data, and then Excel would create the schedule. All the data from the schedule could then be brought into Mathcad in the Outputs area as described above.

Using Excel within Mathcad is a great way to expand the capabilities of Mathcad. If you have existing Excel engineering calculation files, these can be used within Mathcad along with all the other benefits of Mathcad.

Use Excel where it makes sense, but do it within your Mathcad worksheet.

Using Excel for engineering calculations? Think again

Excel alone isn't the best tool for engineering calculations. Explore the benefits of combining your existing spreadsheets with PTC Mathcad.

Tags: