The TrimRange Function

Posted on 05/11/2025 by dougaj4

I have just discovered the TRIMRANGE function, and its very useful shortcut version, which were introduced to Excel 365 about a year ago. The function excludes all empty rows and/or columns from the outer edges of a range or array, and returns the remainder of the array​​​​​​​​​​​.

The function arguments are:

  • The range to be trimmed, in this case entered as the complete column B:
  • Optional “Row_trim_mode” and “Col_trim_mode” (see below for details).

The shortcut is to simply enter the range as =B.:.B. Some examples are shown below:

  • Either the left or right period may be omitted from the shortcut symbol, so that either only the rows below and columns to the right will be trimmed using B:.B, or only the rows above and columns to the left will be trimmed using B.:B.
  • The range may also be a 2D range, as shown in Column K.

Options to trim only to the left and above, or only to the right and below, are shown below:

Using the optional arguments with the full function name allows more options on the ranges to trim:

  • 0 – None
  • 1 – Trims leading blank rows or columns
  • 2 – Trims trailing blank rows or columns
  • 3 – Trims both leading and trailing blank rows or columns (default) 

In the examples above:

  • =TRIMRANGE(A1:E100,2,2) trims trailing rows and columns
  • =TRIMRANGE(A1:E100,1,2) trims leading rows and trailing columns
  • =TRIMRANGE(A1:E100,3,1) trims both leading and trailing rows, but only leading columns.

Using the shortcut notation:

  • =A1.:E100 is equivalent to: =TRIMRANGE(A1:E100,1,1)
  • =A1:.E100 is equivalent to: =TRIMRANGE(A1:E100,2,2)
  • =A1.:.E100 is equivalent to =TRIMRANGE(A1:E100,3,3) or just =TRIMRANGE(A1:E100)
Posted in Arrays, Excel | Tagged , , , | 2 Comments

py_UMom 1-05

Posted on 26/10/2025 by dougaj4

Following the previous post the OptShearCap3600 function has been modified to give correct results with negative bending moments. The revised spreadsheet and Python code can be downloaded from:

py_UMom.zip

Typical output with negative moment input:

Posted in Beam Bending, Concrete, Excel, Link to Python, Newton, PyXLL, UDFs | Tagged , , , , , , , | Leave a comment

py_UMom 1.04

Posted on 19/10/2025 by dougaj4

The py_UMom spreadsheet has been updated with further revisions to the py_OptShearCap3600 function. The new version can be downloaded from:

py_UMom.zip

For more information on the other functions in the spreadsheet see: py_UMom spreadsheet and OptShearCap3600 function.

The changes to the py_OptShearCap 3600 function are:

  • There is a new option to calculate the longitudinal force due to shear using the AS 5100.5 equation, with all other calculations to AS 3600.
  • The input has been modified so that axial force, moment, shear and torsion are specified for the critical load case, and a range of M/V (moment/shear ratio) values are entered to generate the interaction diagram.
  • The option to adjust axial load has been removed. The input axial load will be applied to all load cases.

The revised input and typical output is shown in the screenshots below:

Examples comparing the different longitudinal force options with different levels of shear reinforcement are shown below. The 4 options in each graph are:

  • Longitudinal shear force to AS 3600 with no adjustment of the compression strut angle, Theta.
  • As above with the compression strut adjusted to minimise the longitudinal shear force.
  • Longitudinal shear force to AS 5100.5 with the vertical force in the shear steel, Vus, limited to V*.
  • As above with no limit to Vus.

The first case has shear reinforcement just adequate for the input V*:

Increasing the shear reinforcement greatly increases the shear range with unconservative unreduced bending capacity when the AS 5100 calculation is used with no limit on Vus:

When the shear reinforcement is reduced below the level required for the input shear force the critical forces are all in the range where shear capacity governs the failure mode, and all four options give the same shear capacity:

Posted in Beam Bending, Concrete, Excel, Link to Python, Newton, PyXLL, UDFs | Tagged , , , , , , , | 1 Comment

Mandelbrot, Mojo, Numpy and Numba

Posted on 06/10/2025 by dougaj4

I recently read an article about connecting Python to the new Mojo language:

Python Can Now Call Mojo

The article had some code examples, including code to plot the Mandelbrot set, comparing plain Python, Python with Numpy, and Mojo, and what particularly interested me was that the code with Numpy was about 4 times faster than plain Python, whereas I had found Numpy for this task was slower than Python, as reported here: Computing and plotting the Mandelbrot set in Excel …

I have now updated the matplot-mandelbrot spreadsheet to include revised Numpy code, based upon on the code at the link above, and found that the Numpy code went from up to about 2 times slower to 3-4 times faster.

The updated spreadsheet and Python code can be downloaded from: FastMandelbrot.zip

The Numba code using Guvectorize remains by far the fastest, but the revised Python + Numpy code is now much faster than the plain Python. The original code loops through the results array cell by cell, converting the coordinates to a complex number then calling a plain Python routine to do the calculations:

def mandelbrot_set1(xmin,xmax,ymin,ymax,width,height,maxiter):
    r1 = np.linspace(xmin, xmax, width)
    r2 = np.linspace(ymin, ymax, height)
    n3 = np.empty((width,height))
    for i in range(width):
        for j in range(height):
            n3[i,j] = mandelbrot(r1[i] + 1j*r2[j],maxiter)
    res = (r1,r2,n3)
    return res

The revised code generates an array of complex numbers in a single operation, then generates the Mandelbrot values with a Numpy vectorized computation:

def mandelbrot8(x, y, maxiter, out = 0):
    """Generates a Mandelbrot set using NumPy for vectorized computation."""
    c = x[:, np.newaxis] + 1j * y[np.newaxis, :]
    z = np.zeros_like(c, dtype=np.complex128)
    image = np.zeros(c.shape, dtype=int)
    
    for n in range(maxiter):
        not_diverged = np.abs(z) <= 2
        image[not_diverged] = n
        z[not_diverged] = z[not_diverged]**2 + c[not_diverged]
        
    image[np.abs(z) <= 2] = maxiter
    return image

The spreadsheet plots the calculated shape with chosen colour properties to full scale:

or scaled up by 100,000:

or 1,000,000:

or by 10,000,000:

or by 100,000,000:

and more, but after 1 billion, the image starts to lose resolution using 64 bit floats.

For those who want to go further, visit YouTube:

“The deepest Mandelbrot zoom ever! This zoom is over 24-hours long, uploaded to YouTube in 11 parts. It will finish on a mini-Mandelbrot. As such, I am claiming a world record for the deepest Mandelbrot video! I have kept to the traditional colouring style for this one. The first video may seem a little repetitive at first, but this builds more interesting shapes later into the zoom. You’re welcome to skip ahead a little. Consider playing your own music while you let this one roll. Sit back, relax, and soak it in. (The last minute also generates a cool optical illusion with the pattern it finishes on).”

Posted in Arrays, Drawing, Excel, Link to Python, Maths, Newton, NumPy and SciPy, PyXLL, UDFs | Tagged , , , , , , , | Leave a comment

py_Numpy update 2: Polynomial functions

Posted on 04/10/2025 by dougaj4

Continuing from the previous post, this post looks at the polynomial functions in the revised py_Numpy spreadsheet. The updated code and spreadsheet can be downloaded from:

 py_SciPy.zip

The available functions call the Numpy functions for solution and evaluation of polynomials, plus some additional functions with closed-form solutions for polynomials of up to quartic order. The available functions for finding the roots of polynomial equations are shown below (click on any image for full-size view):

Numpy iterative functions:

  • py_PolyRoots: Find the real and complex roots of a polynomial equation, calling the Numpy polyroots function. Coefficients are listed in increasing order of x.
  • py_PolyRoots: Provides an option for the order of coefficients. By default list in decrasing powers of x
  • py_PolyRootsC: Accepts complex values for the input coefficients. Also has an option to list coefficients in descending powers of x, default = ascending powers.
  • py_Polyroots2: Find the real and complex roots of a polynomial equation, calling the Numpy Polynomial function and .roots method.

Closed-form functions:

  • py_Quadratic: Find the real and complex roots of a quadratic equation: a x^2 + b x + c = 0
  • py_Cubic: Find the real roots of a cubic equation: a x^3 + b x^2 + c x + d = 0
  • py_CubicC: Find the real and complex roots of a cubic equation: a x^3 + b x^2 + c x + d = 0
  • py_CubicT: Find the real roots of a cubic equation: a x^3 + b x^2 + c x + d = 0, alternative solution method.
  • py_Quartic:  Find the real and complex roots of a quartic equation: a x^4 + b x^3 + c x62 + d X + e = 0

Calculation times for 100,000 iterations are shown in the screenshot below, with plain Python code, and with the Numba JIT compiler.

More functions:

  • py_PolyFromRoots: Return the polynomial with the given roots
  • py_PolyVal Evaluate a polynomial for a given x value
  • py_PolyFit Fit a polynomial function to XY data
  • py_PolyCompanion Returns the companion matrix to an array of polynomial coefficients in ascending order
  • py_PolyDer Differentiate a polynomial
  • py_PolyInt Integrate a polynomial
  • py_PolyAdd Add one polynomial to another
  • py_PolySub Subtract one polynomial from another
  • py_PolyMul Multiply one polynomial by another
  • py_PolyDiv Divide one polynomial by another
  • py_PolyPow Raise a polynomial to a power
Posted in Curve fitting, Excel, Link to Python, Maths, Newton, NumPy and SciPy, PyXLL, UDFs | Tagged , , , , , , | Leave a comment