A Match Function Bug …

Posted on 08/10/2022 by dougaj4

… or at least, a non-intuitive feature of the MATCH function, that may give inconsistent results.

This post is based on a discussion at EngTips.

The problem under discussion was to find the number of the first and last rows containing data in a column, when the data was in a single block, with increasing values. The suggested formulas were:

  • Start: =MATCH(TRUE,C6:C157<>0,0)+5
  • End: MATCH(TRUE,C6:C32<>0,1)+5

A simple example is shown below:

Note that the start and end row of the data in column G is reported correctly, and the start row is correct in both cases, but the formula for the end of the data in column C returns the end of the search range, rather than the end of the data.

Looking at the formula in more detail:
MATCH(TRUE,C6:C32<>0,1)
C6:C32<>0 returns an array of TRUE or FALSE values, as shown in columns D and H.

MATCH(TRUE,{ARRAY},1) should return the row number in ARRAY of the last cell containing a TRUE value (which is how it works in column G), but the last argument is the “match type”, with 1 indicating that the last row number with contents less than or equal to the match value should be returned. Because the data is supposed to be increasing in value the function performs a binary search. Starting at the centre of the search range, if this value is blank (and therefore less than the search value), it will check only cells further down the range, and since all these are also blank, it ends up in the last cell of the range, and returns this as the answer.

Two alternative formulas are given are shown in the spreadsheet, which will always give the correct result, provided that the data is continuous and increasing:

  • =MATCH(MAX(C6:C32),C6:C32,0) + 5 (where 5 is the number of the row immediately above the search range.
  • =COUNTA(C6:C32)+C1-1 (where C1 is the row number of the start of the data)

Note that the first formula will give the correct result if the data has gaps, provided that the last cell with data has the maximum value. For the second formula the data does not need to be increasing in value, but it must be continuous.

Posted in Arrays, Excel | Tagged , , , | 3 Comments

3D plots with the latest Matplotlib

Posted on 30/09/2022 by dougaj4

Installing Python on a new computer, I found I had to downgrade Matplotlib to release 3.5.2 to get my 3D plotting functions to work. Further investigation found that the problem was the Axes3D function.

Changing:

    ax = axes3d.Axes3D(fig)
to:
ax = fig.add_subplot(1,1,1, projection='3d')

the 3D plotting functions worked without further changes to the code. Some additional functionality was also added:

  • The new “roll” angle and focal length attributes were addded.
  • The calculation of the viewpoint coordinates offset and the zoom factor were modified.
  • Scroll bars were added to the spreadsheet to control the zoom factor and viewpoint location.

The new code for the Plot3D function and the new Plot Frame spreadsheet can be downloaded from:

Matplotlib3D.zip.

New spreadsheet:

Posted in Animation, Coordinate Geometry, Drawing, Excel, Link to Python, Newton, NumPy and SciPy, PyXLL, UDFs | Tagged , , , , , , | 4 Comments

Installing Python and pyxll from scratch

Posted on 26/09/2022 by dougaj4

Updated 28th September 2022. Matplotlib latest version OK.

I recently installed Python and pyxll (plus the required additional libraries) on a new computer, which raised a few problems with incompatible versions, so here is a summary of what worked (as of 26th September 2022):

Office should be installed and working before starting the Python installation. Then download and install Python Rel. 3.10.7 (must be the same bit number as Excel). Make sure that the options to install pip and tcl/tk and IDLE are selected.

When Python is installed the pip library installer can be used to install the rest of the required packages, including pyxll. To install pyxll (see here for more details) enter at a command line:

pip install pyxll
pyxll install

As a minimum, numpy, scipy and pandas should also be installed. For many of the applications published on this blog the numba jit compiler is also required:

pip install numpy
pip install scipy
pip install pandas
pip install numba

For applications requiring the solution of large sparse matrix equations the pypardiso library is recommended as being much faster than the sparse solvers included in scipy. This may now be installed simply with pip:

pip install pypardiso

For working with text based equations and units the following libraries are required:

pip install sympy
pip install pint

Note that sympy requires mpmath for multi-precision arithmetic, but this is now included in the pip installation.

For plotting graphics (including 3D graphs and animations) the following libraries are required. Note that the specific kaleido release listed below is required. My code has now been updated so that the latest release of matplotlib works without problems. (Updated 28Sep22).

pip install matplotlib
pip install plotly
pip install kaleido==0.1.0post

If earlier or later releases of matplotlib or kaleido have been installed, they can be replaced with the required release with:

pip install –upgrade matplotlib
pip install –upgrade kaleido==0.1.0post

The “–upgrade” command will work to either “upgrade” or “downgrade” the installed package.

Finally, the sectionproperties application, including all required additional libraries, may now be simply installed with:

pip install sectionproperties

Posted in Animation, Arrays, Charts, Coordinate Geometry, Differential Equations, Excel, Finite Element Analysis, Link to Python, Maths, Newton, Numerical integration, NumPy and SciPy, Python Pandas, PyXLL, UDFs | Tagged , , , , , , , , , , , , , , | 2 Comments

Australasian Engineering Heritage Conference 2022

Posted on 14/09/2022 by dougaj4
https://www.engineersaustralia.org.au/event/2022/03/australasian-engineering-heritage-conference-2022-41536

The Australasian Engineering Heritage Conference will be held at the Engineers Australia Sydney offices at 44 Market Street, Sydney on 12-13 October 2022.

The theme of the conference is: Transport and Communications: Looking Forward – Looking Back‘.

In addition to a wide range of speakers on engineering heritage and heritage engineering, there will be two very notable keynote speakers: Howard Collins, Chief Operations Officer in Transport for NSW, and Pamela Henderson, Executive Director, Technical Services in Transport for NSW. At the end of the first day there will be an informal networking dinner, when the guest speaker will be Scott MacGregor, the well-known railway presenter and tour guide. There will also be site tours on the second afternoon to Central Station Clocktower and the new Metro Station at Central.

For more information and to register see:

Australasian Engineering Heritage Conference 2022

Posted in Historic Bridges, Newton | Tagged | Leave a comment

RC Design Functions 9.02; DevLength function

Posted on 13/09/2022 by dougaj4

The RC Design Functions spreadsheet has now been updated to Version 9.02, and is available for free download from:

RC Design Functions9.zip

Changes in the new version include:

The new DevLength function returns reinforcement development length to AS 3600, AS 5100, Eurocode 2, or BS 5400 requirements. An example of the function can be found on the “UMom Out” sheet:

The function output returns the development length followed by the required code factors, which are different for each code:

On the same sheet, the MaxAx function has been updated to return all values:

On the “UShear” sheet examples have been added of the ShearCapEC2 and ShearCapBS5400 functions, which can also be called from the UMomPF function. There is also a correction to the shear capacity results to AS 3600 and AS 5100:

Finally, the shear results from the UMom function have been updated so that the full results are returned with array input of applied actions. See the “Array exmples” sheet for an example:

Posted in Beam Bending, Concrete, Excel, Newton, UDFs, VBA | Tagged , , , , , , , , , , | Leave a comment