5 Tips to Transition from Excel to PTC Mathcad

five

The vast majority of PTC Mathcad users, new and old, used Microsoft Excel prior to using PTC Mathcad for their engineering calculations. Users and companies that have gone through, are going through or are thinking of going through the transition could have tens or even hundreds of calculations built in Excel. In a perfect world those Excel spreadsheet would magically be turned into a professional looking PTC Mathcad worksheet with natural math notation, unit awareness, documentation, etc. While it may seem a daunting task, the light at the end of the tunnel is bright given all the benefits of having a PTC Mathcad worksheet instead of an Excel spreadsheet. To make the transition smoother here are some tips that will allow you to transition your prehistoric Excel spreadsheets into modern PTC Mathcad documents.

 

  1. Utilize Range Variables

PTC Mathcad uses range variables to do a multitude of things. One of which is to define a range of values in which to evaluate a function (or functions) over. How many of you have done something like this before in excel? Type in cell A3 ‘0’, type in cell A4 ‘0.1’, highlight the two cells, grab the corner and drag down.

blog1

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

In a spreadsheet all values need to be evaluated on the screen in order to be referenced in another cell. PTC Mathcad works differently, the values do not need to be evaluated on the screen in order to be referenced in a calculation. This allows PTC Mathcad users to simply define a range and create functions that use the range as an argument. You can call the force or torque function at a given time like shown below or evaluate it across the entire range on a plot without needing to have all the values shown on the screen.

blog2

 

 

 

 

 

 

 

 

 

 

The value of using a range variable instead of dragging your values down column ‘A’ becomes even more valuable when you want to change the range you are evaluating over, or change the step of the range. In PTC Mathcad you simply change a number to update the range. Good luck changing the step size from 0.01 to 0.001 on a range that goes from 0 to 100 in Excel. So for scenarios like this, be aware you do not need to manually create and evaluate all the numbers in PTC Mathcad.

 

  1. Transitioning to a units aware engine

One of the biggest differences between Excel and PTC Mathcad is the units intelligence that PTC Mathcad’s engine has. When you are in PTC Mathcad, you’ll notice that (by default) all units are labelled as units and their formatting changes to blue, bold & italicized. PTC Mathcad’s engine will automatically account for any units, something that is very handy, but may cause you to see some unexpected results in your PTC Mathcad calculations if you do not accommodate for them in your equations.

 

There are two cases I see often where what’s in the formula bar of Excel doesn’t directly translate into what your PTC Mathcad equation should look like. The first case is for empirical formulas, that’s to say formulas that have unit conversion baked into them. Below is an example I recently came across doing a conversion of my own. I’ve stripped out the context, but the formula itself takes ‘psi’ and multiplies it by ‘in’ and returns ‘lbf’. With units intelligence in PTC Mathcad simply typing the same equation will return ‘lbf/in’. This is as expected since the equation multiplies a pressure and a length, but the empirical formula returns a force.

blog3blog4

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

To get PTC Mathcad to do this you need to divide the variables with units by their unit and then multiply the entire equation by the desired output (I2).

 

Another common scenario where what’s in your Excel formula doesn’t translate 1:1 with a PTC Mathcad equation is for unit conversion. Many Excel formulas will have “conversion factors” built into the equations to manually force Excel to return the correct numeric value for the desired unit string. Those should not be included in a PTC Mathcad equation since the unit labeling will ensure you that your units are converted correctly.

 

You may see something that looks like this in the formula bar…

=A1*5450.4

 

And after 15 minutes of deciphering you may be able to determine that 60 (minutes in an hour) * 24 (hours in a day) * 3.785 (liters in a gallon) = 5450.4

=A1*60*24*3.785

 

Which is the conversion factor for converting ‘gal/min’ to ‘L/day’. Which isn’t a tragic thing to have if your formula is that simple; however, when formulas become more complex those numbers become harder to tease out.  In PTC Mathcad, no additional calculation or formula needs to exist to convert your volume flow rate from one unit to another. Just assign the variable a unit and dictate which unit to display a result in when the time is appropriate.

 

  1. Lookup Tables

 

You may have lookup tables integrated into your spreadsheets. Lookup tables enable you to lookup specific values within a data set; both vlookup and hlookup are commonly used in spreadsheets and often times you have vlookups inside of vlookups. To put it shortly, PTC Mathcad has those same lookup functions so you can easily pull the Modulus of Elasticity (E) for alloy steels.

 

blog5

 

 

 

 

 

 

 

 

 

 

 

 

 

  1. PTC Mathcad loves matrices, simplifiying your array formulas

 

Excel for the average user does not handle arrays or matrices, dragging formulas down the spreadsheet to try to work around that is a timely process. PTC Mathcad on the other hand loves when you feed it arrays and matrices. If you have array forumulas in Excel, those will translate to array (or matrix) math; when combined with more robust range variables to replace your array constants the matrix math you are doing in Excel will move cleanly into PTC Mathcad.

blog6

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

  1. Locking single cells, rows and columns

 

Many excel formulas contain absolute references ($A$1) and or mixed references ($A1 or A$1). These are used to lock a specific cell, a specific column or a specific row when you drag formulas across the spreadsheet. In PTC Mathcad you are not going to press ‘F4’ to add a dollar sign to the formula, you will simply use variables and matrix indices.

 

 

blog7

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Although the task of moving all your excel calculations from Excel to PTC Mathcad may seem like a tall task, after you do the first couple you will quickly learn the nuances between the two software packages and become comfortable with PTC Mathcad’s range variables, units awareness, lookup functions, and matrix indexing.

Learn more today and watch this short demo on the Excel component

 

Don’t have PTC Mathcad?  Try it for free with PTC Mathcad Express.

This entry was posted in Mathcad, Tips & Tricks, Uncategorized and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s