A Problem Solved in Excel and Mathcad

Quote of the Day

All of the animals except man know that the principal business of life is to enjoy it."

- Samuel Butler

Introduction

FIgure 1: HMS Dreadnought, the ship that changed naval gunnery.

I use both Excel and Mathcad in my daily work. Most people would consider me very proficient in both. Though it has taken me a long time to become a master of Excel as there weren't as many resources around when I started learning.

In any case, I frequently get asked, "Which tool is better?" Like all other interesting questions in Engineering, the answer is "it depends". Both have their strengths and Microsoft has other uses that can connect with Excel, like Azure Virtual Desktop for cloud usage which can make it easier when moving about, but it is all a case of preference.

As an example, I decided to work a simple problem in both Excel and Mathcad. A number of the advantages and disadvantages of both tools can be seen in this example. The key problem with Excel is its cell-oriented approach. While the cell-oriented approach works for small problems, it has major issue with large problems. As you know, MS Excel is one of the best alternatives to Google products but, there are also other software manufactures that offer an Excel alternative. You could take a look at Web Safety Advice for more information.

Background

My Example

I am reading the book Dreadnought Gunnery and the Battle of Jutland and it presents an interesting fire control example from the Battle of Jutland in the form of Table 1.

Table 1: Original Table of Fire Control Information from the"Run to the South" Engagement.

I want to verify that I understand what I have read by duplicating the results shown in Table 1. This problem is most easily approached as a vector analysis problem. There is also some unit conversion involved. My interest in this problem is driven by my desire to code a naval warfare simulation and I want to make sure that I understand the fire control issues involved.

Engagement Geometry

Figure 2 is an illustration of the critical variables in this problem. This is a very common type of fire control situation from World War 1. Here are the details:

• There are two ships: SMS Lützow and HMS Lion.

  HMS Lion was the flagship of the Grand Fleet's battlecruisers. SMS Lützow was a battlecruiser with the German Imperial Navy.

• Both ships are on headings given in terms of the points of the compass.

  Historically, compass readings were given in terms of 32 compass points. Each of the points was evenly spread over a 360 ° circle -- each point represents an 11.25 ° increment.

• The fire control example is from the standpoint of the HMS Lion.

  This means that the target bearing reading is given from the standpoint of HMS Lion. Note that target bearings are given with respect to the ship's heading and not the compass.

• Two fire control examples are listed in Table 1. Figure 2 only illustrates one example. The second is similar.

Figure 2: Engagement Geometry.

Analysis

I go through some of the basic fire control equations in this blog post, so I will not review them here.

Excel Version of My Analysis

Here is my approach to duplicating Table 1 in Excel:

• Table 1 is row-oriented. I decided that for a column-orientation would be a bit easier to work with in Excel.
• Inputs to the problem are tan-colored. Over time, I intend to add additional cases to the table and I want to highlight which cells need to filled with information.
• I show the Excel formulas I used in the comments column. One of the issues with Excel is that the formulas get complex and difficult to read. There are things you can do to minimize that, but you will often see formulas in Excel that are difficult to figure out.
• You need to explicitly handle unit conversion in Excel. This is one of my biggest gripes with Excel.

I wrote up the Excel solution and I did not get the results of Table 1. Unfortunately, I made a unit error. However, I eventually did get it right.

Table 1: Screenshot of My Excel Version of the Jutland "Run to the South" Rate Table.

Here is how I see the advantages and disadvantages of Excel.

• (Advantage) Repeating simple formulas over and over is very simple in Excel.

  This is why Excel is so popular with accountants. They do not tend to have complex formulas, just lots of them.

• (Disadvantage) Complex formulas are a pain in Excel.

  I cannot tell you how many hours I have spent trying to figure out some complex array formula in Excel. That same formula in Mathcad would be simple.

• (Disadvantage) You must handle unit conversions yourself.

  This is painful -- especially in the US where I need to convert between unit systems all the time.

• (Advantage) Power tabular data display capabilities.

  Excel is really good at displaying and analyzing tabular data.

• (Advantage) Everyone has access to Excel.

  Most folks can get access to Excel one way or another (e.g. use it online with Microsoft Live). I frequently solve problems in Excel that really would be more appropriately done in Mathcad simply because my customers do not all have Mathcad. In these cases, I use Mathcad to help me verify my Excel solution.

Mathcad Version of My Analysis

Figure 3 shows my version of this analysis using Mathcad, which was correct the first time I went through it. The key to this success was that Mathcad handles the units automatically. To be completely honest, when I had the unit problem in Excel, I decided to write up the problem in Mathcad. Seeing the correct unit conversions in Mathcad allowed me to easily see the error in my Excel. Note that I only solved one engagement scenario in this example. I could easily take this work and put it into a Mathcad program that would allow me run as many scenarios as I wish. Here are the advantages and disadvantages of Mathcad:

• (Advantage) Math-like notation.

  If you are familiar with mathematical notation, you can pick up the Mathcad syntax pretty quickly.

• (Advantage) Automatic unit handling.

  I use this capability all the time. It does take a bit of getting used to -- especially for temperatures and decibels. However, it is a powerful feature.

• (Advantage/Disadvantage) Requires using a Mathcad program to repeat the analysis steps with different parameters.

  I actually like putting things into Mathcad programs. I usually solve one case and get it right, then I put the equations into a program. That is what I would do here. However, it is an extra step. Excel makes it easy to repeat your calculations in adjacent rows/columns.

• (Disadvantage) Does not display tabular data as cleanly as Excel.

  Getting a nice tabular display really requires inserting an Excel component into the Mathcad worksheet. This is not difficult, but native Mathcad does not do it well.

Figure 3: Mathcad Version of My Analysis.

Conclusion

I have decided not to choose between Mathcad and Excel -- I use them both and frequently on the same problem. Each has their strengths and I want to use these strengths to solve my problems. In this case, I thought I would blog about a common situation for me.

• I wanted to use Excel to make a clean looking table and to allow others to work with the data.
• I had some trouble getting my Excel formulas correct.
• I solve one case in Mathcad and use that solution as a guide in getting my Excel to work.