Editor's note: This blog article was originally written as a PTC Mathcad Prime 9 worksheet. For the best reading and interactive experience, please download the Mathcad Prime worksheet here. When you download the sheet, please unzip the file and have the accompanying Excel spreadsheet in the same directory that you save the Mathcad Prime worksheet. If you need a Mathcad Prime worksheet viewer, download Mathcad Prime for free here.
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.
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.
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.
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.
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.
On the other hand, when the READEXCEL function is used, all the data from the Excel file is brought into Mathcad and becomes available for use. If you need access to all of the data, this is the easiest way to bring the data into Mathcad. Use this function if your Excel file is used to only store data. This function is not appropriate if your Excel file is performing calculations. If the Excel file has a cell with a function, then the result of the calculation is brought into Mathcad, not the formula. The READEXCEL function is linked to the original Excel file and must retain access to that Excel file.
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.
Excel alone isn't the best tool for engineering calculations. Explore the benefits of combining your existing spreadsheets with PTC Mathcad.
