Excel as a function

Excel as a function

We have discussed in a previous blog how to create Custom Functions in PTC Mathcad Prime in the form of DLLs: The Down Low on Mathcad DLLs. And even more recently my colleague, Thomas Devaraj, wrote an excellent piece on 6 interesting use cases for the Excel component in PTC Mathcad Prime: best-of-both-worlds-with-the-embedded-excel-component-and-its-top-6-use-cases/. In use case #6 Thomas introduces how to call Excel as a function. This is really powerful and we wanted to show more examples to help users get comfortable with this Excel as a function paradigm.

First I want to state that while we are creating a function, there is no code involved. All development takes place within the PTC Mathcad worksheet and we’ve attached the actual worksheet for you use.

In this slightly modified example from Thomas’s article you will see first that the Excel component provides great support for Excel’s syntax while defining the Input and Output expression of the component.

Excel as a function

 

The embedded Excel worksheet is actually a workbook with two sheets. You can see the Sheet1! and Sheet2! syntax on the input expressions. In the output section of the Excel component (bottom piece) you see getExcelData function defined as a single statement, returning the result of keyword excel cleverly subscripted with the parameter range.

So now the worksheet has a function called getExcelData which can take in any Excel range using Excel syntax to pull back data from the Excel component. We think it’s pretty cool to make the Excel component available as a parameterized function.

OK, let’s look at a different approach to parameterizing the Excel component. In this case we will use an input vector of different sizes and a range variable to compute the Excel component multiple times. There is not a function per se; we are simply animating the Excel component through the use of a range variable.

Excel as a function

 

So in this scenario we create a dummy matrix and feed it into the Excel component using the normal Input expression syntax. We then create a vector called lookup­­val which represents the data values we want to lookup. When we find the desired value we want to return the entire row the matched value resides in. Next we dynamically create a range variable called i using the last function. As lookupval changes so does the range variable i. Another piece of this puzzle is in the embedded Excel sheet, in cell G1 we store the MATCH(H1,A1:A4,0). Finally we assigned to the Excel sheet, cell H1 the elements of lookupval using the range variable i. This is how the repeated call takes place, we take each element of lookupval one at a time, stuff it into H1, run the MATCH expression in G1 and return G1 to rowval[i].

Let’s let that sink in for a minute…. The value 65 from lookupval gets put into H1, H1 feeds MATCH in G1, G1 gets returned to rowval[i] and once again we use the Excel output with Excel syntax created through the concat function. Next the value 56 from lookupval gets put into H1 and we repeat the process. The input array lookupval can grow or shrink and nothing else needs to change. So Excel will be called length of lookupval number of times.

Using the MATCH in Excel is not required for the looping of the component. This was done in Excel because in the real Mathcad worksheet the data shown in Excel was the collection of many PTC Mathcad input arrays.

So it was easier to do it in Excel rather than stack and augment our way to a single Mathcad matrix.

So there you have it, a deeper dive into a new Excel component paradigm. Please let us know if you have questions with regards to this blog or any topics in PTC Mathcad.

Download the worksheet.

Try out PTC Mathcad Express, free today.
Watch a 90 second demo on the Excel component.

 

Quick Links

PTC Mathcad Express

Free for life version

PTC Mathcad Features

90 second videos

Resources

Training, live demos, free worksheets

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

4 thoughts on “Excel as a function”

  1. Harvey says:

    John,

    Prime has made it easier to use the Excel component by allowing a more dynamic creation of the worksheet. However, I still can’t see how an imbedded Excel worksheet offers any advantages. It seems to me that all of the operations can be done with a matrix in Prime without Excel.

    To me, the advantages that Excel offers are better formatting for table headers and titles. Thus, I have used Excel as input and output sheets which can be provided to clients. The READEXCEL and WRITEEXCEL functions are used to access those worksheets. In my many years using Mathcad, I have not found a need to use the Excel component feature.

    The first example you gave can easily be handled with the submatrix function. In the second example, you state that doing the MATCH in Excel instead of in Prime somehow made things easier because it avoided using the augment function in Prime. However, you had to do something similar in Excel or with the Excel component in order to arrive at the single matrix. What am I missing? Also, using Excel functions and calculations within the embedded worksheet defeats the advantage of Mathcad’s better transparency for documentation.

    1. John Sheehan says:

      Hi Harvey
      Everything you’ve stated is correct. Mathcad matrices can handle most of the day to day manipulation of tabular data. And believe me I am not going to start praising Excel for its documentation abilities.

      But sometimes I just want a nice looking grid of numbers with colors and merged cells and borders on the cells. Maybe even a pivot table. Excel remains today the best data table widget ..IMHO. Terrible for engineering calculations but it can spin an array.

      I use READEXCEL more than I use the Excel embedded components. But seeing that 99% of our new customers are coming to us from Excel, I can see how it might helps the transition.

      John

  2. Harvey says:

    John,

    Being from the “Old School”, i.e. before flying PowerPoint dot points, gifs for everything under the sun, colors galore, I am interested primarily in “just the facts, Ma’am”. Sometimes I really wonder what it costs to have engineers spending time doing tasks that used to be performed by secretaries. Maybe this “productivity software” isn’t so productive after all?

    In my previous comment, I neglected to say that your and Thomas’s examples for the Excel component were well done. Who knows, I may actually use it someday.

    Harvey

    1. John Sheehan says:

      Harvey,
      With deadlines shrinking it is tougher and tougher to find spare time to play with PowerPoint et al to make your findings memorable.

      I am a product of my upbringing, I spent many , many years in data visualization and before that a well known animation company. So for me , the presentation is everything. I mean after getting the right answer.

      Somewhere in the middle is the right amount I suppose. I have noticed over the past 30 odd years how many engineers are better than marketing folks with PowerPoint. What does that say?

      Well let us know if you ever do use Thomas’ or my example. It will be a minor victory for us🙂
      Thanks Harvey
      John

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