Trending

Best of Both worlds – with the Embedded Excel component and its top 6 use cases.

The Collins English dictionary defines “The Best of Both worlds” as “the benefits from two different or opposed ways of life, philosophies, etc.”

You could argue that PTC Mathcad & Microsoft Excel are tools facilitating calculations from two different and opposed philosophies. The phrase also elucidates the human condition, where we just we can’t settle for one, we want to have our cake and eat it!

So when it comes to solving problems in STEM (Science, Technology, Engineering & Maths) there is a need for an intuitive, powerful and flexible calculation tool and Microsoft’s Excel Spreadsheet is generally at the top of the pile if you purely look at the numbers of various estimates of people using Microsoft Excel today. There are some good reasons for its undoubted popularity and the scale and extent of applications built in Excel are truly vast.

In my professional capacity, speaking with fellow professionals in various industries, I have more often than not come across the view that an uncomfortable marriage with Excel exists. MS Excel is undoubtedly good when it comes to sharing data, sorting and organizing this information in a convenient and visual manner. And beyond this, there is no doubt that Excel has capabilities to create the weird and the wonderful. But when it comes to committing complex scientific and mathematical content into an Excel cell is where the problems arise.

Take the example below.

What does it mean? Is it correct? Should I use it? Can we trust it? The questions keep coming.

PTC Mathcad offers a calculation environment that is fundamentally different. In short PTC Mathcad takes a formula-centric approach, conversely Excel adopts a data centric approach. PTC Mathcad helps to answer those questions and assures that fundamental concepts are not consigned to the scrap heap of wasted effort. The equation from above is written and calculated in its natural mathematical format in PTC Mathcad below and advantages of such an approach are obvious.

Those who swear by Excel appreciate its ability to organize data, plot and do repetitive tasks whilst fans of PTC Mathcad prefer its clarity of presentation and its undoubted mathematical pedigree and its unique ability to manage units. So surely a tool that provides the best of all that both PTC Mathcad & Excel can offer combined is what we all want?

It’s one of the top reasons why PTC Mathcad gives users that ability to embed Excel spreadsheet content within a PTC Mathcad worksheet. Users benefit from the strengths of both tools to have an awesome capability that works seamlessly. This capability allows for a variety of uses ranging from the obvious to the imaginative. Here are the top use cases that I’ve come across and could be of interest.

Note: In all the use cases the equations/calculations are done in PTC Mathcad, the Excel component is used to support a specific capability and as such no calculations are done in the Excel sheet.

Display tabular data from other sources – Hosting data to be used in the calculation. In instances where the input data for calculations is often better captured in a table e.g. Section properties that can be selected to be used in a design.

Display calculated results – the excel component can be used to display calculated results in an organized manner.

Take advantage of conditional formatting – you can enhance your data representation with cell highlighting. This can help to quickly and easily assess the changes in your results in a very visual manner.

Enhance the appearance of the header for documentation purposes. Make use of the ability to embed the excel component in the header to enhance the appearance of the header with a rich table of details.

Push results to the top of worksheet to capture them as a summary for quick review. Helps to get around PTC Mathcad’s top down approach to calculations where order of calculation precedence needs to be strictly observed. When a summary of results needs to be displayed on the first sheet such an approach could help.

Read data from the embedded excel table using the ability to cast the output of the component as a function. One generic function can allow multiple calls to any cell in any sheet allowing you to retrieve the data of interest.

If you want to try it PTC Mathcad yourself, click here for your free permanent download of PTC Mathcad Express.

Remember to activate your 30-trial of PTC Mathcad Prime, where you can test out all the premium features including the Excel Component.

#3. I was not aware that Excel had this capability, since I rarely use Excel. I will be on the lookout for opportunities to use this capability. I don’t think I can do this in Mathcad without the Excel component.

#4. This looks interesting, but only if changes to the Excel worksheet get updated in the Mathcad header. For example, if the Rev. #, date, etc. changes, I need the header to change also. I tried this and the only way I could get the component in the header was to create it in the Prime work area and then copy/paste it into the header field. Changes to the Excel sheet showed up in the Prime work area component but not in the header component. Is there another way of inserting the component into the header that allows updates? If not, then a simple cut and paste from the Excel worksheet would suffice.

#5. Pushing the results to the top: I need to test this with a large worksheet to see how many variables need to be global. Using global variables would be a big change from my normal methods. For reports needing an Excel type of look for the output, I currently just write the results to Excel.

Finally, did you ever supply a file per Juan’s request? I couldn’t find one on the PTC Community.