Calculating and plotting rotations

Posted on 19/06/2023 by dougaj4

This post was prompted by a recent post at Eng-Tips.

The original problem seemed simple enough:

Im trying to wirte a formula where I imput vessel heading and datum bore heading and it tells me FWD, STBD, AFT or PORT

Now FWD and AFT are I can manage. If the difference between two headings is 45 or less then its FWD. if its greater than 135 its AFT

Between 45 or 135 is PORT or STBD depending on which way vessel turns. If vessel turns clockwise on compass then bore heading on PORT and and if anticlockwise its STBD

Anyone know how to write such a thing on excel?

For consistency the terminology used in the rest of this post will be:

  • Angles are measured clockwise from North, so will always be between 0 and 360 degrees.
  • The angle from the boat to the datum bore is referred to as the Reference Bearing.
  • The ship’s direction of travel is referred to as the Heading
  • The angle required is the angle from the Heading to the Reference Bearing, which will be divided into FWD, STBD, AFT, PORT, and back to FWD, with transitions at 45, 135, 225, and 315 degrees.

The problem is that if the angle is calculated as Ref. Bearing – Heading, it may lie between -360 degrees and 360 degrees, which makes any single formula using nested if statements very long. The simplest solution is to use a VLookup formula with a table, as in the screenshot below:

The table can be shortened by using the formula =MOD(Ref-Head, 360), which returns an angle between 0 and 360. The shortened lookup table is in my opinion the simplest solution, and the easiest to check, but two working alternatives were given in the discussion, which used a single cell formula:

  • The formula in cell A16 uses =INDEX() with a list of the four direction names, and a formula using MOD() that returns a value between 1 and 4.
  • The formula in cell A11 uses the sum of the Sin and Cos of Ref-Head, together with nested Ifs and ANDs.

A simpler solution using the ABS function was suggested, but this does not work when the Ref-Head angle is negative.

Finally Eng-Tips regular MintJulep provided some VBA code to plot the Reference Bearing and Heading instantly whenever one of the angles was changed:

Both spreadsheets shown above, including full open-source VBA code can be downloaded from:

Bearings.ZIP

Posted in Coordinate Geometry, Drawing, Excel, Maths, Newton, VBA | Tagged , , , , , , | Leave a comment

Speed of Scipy Linear Algebra Solvers

Posted on 14/06/2023 by dougaj4

The files presented in the recent post on Scipy Linear Algebra Solvers include a wide range of different solver types, with in some cases numerous sub-options, allowing the functions to be called from Excel, and greatly extending and speeding up Excel’s limited solver functions.

In this post the speed of all the available functions is compared for solving sparse matrices for structural frame analysis with matrix sizes of 14742×14742 and 29485×29485.

The python code and spreadsheet for the solvers can be downloaded from:

pyLinAlgfuncs3.zip

and the timer spreadsheets (including complete matrices) from:

TimeLinAlg.zip

Note that the spreadsheets require pyxll to link the Python code to Excel.

The file Time Lin-Alg2.xlsx has the larger matrix and time results for both:

The solver times show the time for complete solution in the first column, or where applicable the time for factorisation in the first column, and extraction of the results from a factorised matrix in the second:

The functions solveit (iterative solver) and spsolve (sparse solver) have alternative solver types, with widely varying performance:

Times shown are for the larger matrix.

Two of the iterative solvers (4 and 10) failed to converge.

For the py_Spsolve functions Option 0 calls the pyPardiso library, which must be installed separately to Scipy. This is very much faster than the built-in Scipy options, and is the default solver called by py_Spsolve.

Posted in Arrays, Excel, Finite Element Analysis, Frame Analysis, Link to dll, Link to Python, Newton, NumPy and SciPy, PyXLL, UDFs | Tagged , , , , , | 2 Comments

EngineeringPaper.xyz

Posted on 12/06/2023 by dougaj4

EngineeringPaper.xyz is a web app for engineering applications with free open-source code:

EngineeringPaper.xyz is a web app for engineering calculations that handles unit conversion/checking automatically and also supports plotting, solving systems of equations, and documenting your calculations (see the official blog for many examples). It’s easy to share your calculations by creating a shareable link that anyone can open and build off of. Additionaly, you can save and open your files locally if you prefer not to save to the cloud. EngineeringPaper.xzy runs on Mac, Windows, Linux, and ChromeOS and works on all of the major browsers. Additionally, EngineeringPaper.xyz is designed to run well on Android and iOS devices. Launch EngineeringPaper.xyz in your browser to try it out.

From the tutorial:

EngineeringPaper.xyz is a free and open source Mathcad® alternative for engineering calculations that automatically handles unit checking and unit conversions, can solve systems of equations, and supports plotting. You can also easily save and share your work by creating a shareable link. EngineeringPaper.xyz is a web app that doesn’t require you to create an account. The tutorial video below will get you started using this powerful tool that runs on any operating system including macOS, Windows, Chrome OS, and Linux. The table at the bottom of this page lists several example sheets that walk you through some of the more advanced capabilities of EngineeringPaper.xyz. If your interested getting involved with the development of EngineeringPaper.xyz, visit the EngineeringPaper.xyz GitHub project site. 

Posted in Computing - general, Newton | Tagged , | Leave a comment

Pete Brown

Posted on 02/06/2023 by dougaj4

Peter Ronald Brown (25 December 1940 – 19 May 2023) was an English performance poet, lyricist, and singer best known for his collaborations with Cream and Jack Bruce.

https://en.m.wikipedia.org/wiki/Pete_Brown

I spent my last year of high school in England as a boarder, and the “upper sixth” boarders were privileged to have their own common room (located in a cardboard box in the middle of the road) where we could huddle around the record player and listen to the latest progressive rock releases, including the works of Cream, featuring the music of Eric Clapton, Ginger Baker, and Jack Bruce, and the words of Pete Brown; such as:

Posted in Bach | Tagged , , , | Leave a comment

Scipy linear algebra functions in Excel

Posted on 30/05/2023 by dougaj4

The file

pyLinAlgfuncs3.zip

has open source Python code allowing the Scipy linear algebra functions to be called from Excel using the pyxll add-in. It also includes all the Python code to perform LU decomposition, as discussed in the previous post., and a spreadsheet with examples of all the available functions.

The spreadsheet has 64 functions, listed on the first sheet:

The solver functions are demonstrated solving a stiffness matrix representing a small structural frame:

The first 10 rows of the solution to the matrix for the example loading are presented for a wide range of different functions:

The problem presented in the previous post is also included, with the associated python code:

Future post will look at the use of the different solver functions in more detail.

Posted in Arrays, Excel, Finite Element Analysis, Frame Analysis, Link to Python, Maths, Newton, NumPy and SciPy, PyXLL, UDFs | Tagged , , , , , , | 2 Comments