Mathcad Brush Up: Setting up an Excel Integration

There are many ways to use the Excel integration in Mathcad to your advantage. Here’s one of my favorite use cases with the math software– and tips for setting it up.

The Challenge

Say I have a worksheet that performs some basic engineering calculations like the reactions, shear, and moment as well as the deflection of a simply supported beam subjected to a force. The cross section of the beam can be seen in the image below:

Cross section of a beam shown in Mathcad software.  

If I were to update this worksheet manually with the dimensions of another cross section, I’d have to type in a few numbers, which takes time. But, I already have a database of numbers in Excel format. Why not just leverage the work that’s already done, and save as much time as possible?

Using Values Already in Excel

This is where the Mathcad-Excel integration comes in. Once set up, I will be able to type a single ID value into the worksheet. Because it is connected to the related values in Excel, it will automatically update the rest.

There are a few ways you can get the data from the spreadsheet to PTC Mathcad, but in this example we’ll go with the Excel Component approach because I want my data displayed in a nice table.

Start by inserting an Excel component. Just follow these steps:

1. In a PTC Mathcad worksheet, click where you want to insert an Excel component.
2. On the Input/Output tab, in the Data Import/Export group, from the Excel Component list, click Insert Excel Component.
3. Add or remove spaces in the Inputs and Outputs areas as you do in a PTC Mathcad worksheet.

My table looks like this:

Worksheet showing Excel component in the math software

Connecting the Values

Now comes the interesting part. We need to connect the values from the table to the variables in the worksheet so it will automatically update the variables with the values that correspond to that ID.

To connect values, follow these steps:

1. At the bottom of the Excel Component, expand the outputs area.
2. Insert relations by right clicking the area and selecting Insert Output Expression. You will have to insert one output expression for each column of the table.
3. Make sure to specify the range of values that you want to pull out of the table and assign it to a variable. It should look something like this:

Assigning ranges of values to a variable

After you are done with defining the relations, hide them by collapsing the outputs area.

At this point if you evaluate any of the variables defined in there, you will get a vector of values. The thing is that we want the individual values corresponding to the ID, not the values for all of them. This brings us to our next and last step.

Using the match function

Using the “match” function, make sure the ID variable (marked in yellow) that you will enter manually in the worksheet is found in the ID_e vector you pulled out of the Excel Component. This way you store the position of your ID in the variable idx. We then use the same idx variable to get the values on the same position as the ID from the h_e, b_e, s_e, t_e, d_e and r_e vectors. The returned values are assigned to the variables that are used in the Mathcad worksheet.

Mathcad Does the Rest

At this point, all you have to do is to enter the ID of the beam that you want to perform the calculations for and watch Mathcad update. Go on, give it a try.

Want to learn even more about using Mathcad? Check out this prerecorded webinar to learn more about documenting design intent, creating analysis-driven designs, and more.

Featured image Structure by Les Chatfield via Flickr