Dynamic array update and the @ operator

Posted on 11/06/2026 by dougaj4

Many of the user defined functions (UDF’s) presented in this blog return an array rather than a single value. Options for displaying arrays in Excel have changed significantly in recent years and this post looks at the most efficient ways of working with these changes, including some situations when the old way is still the best.

In addition to the built-in Excel options, using Python code via pyxll allows large multi-dimensional arrays to be passed to Excel as a cache object, which has significant advantages in some situations. This will be covered in more detail in the next post.

The screenshot below shows output from the py_Umom UDF entered as a fixed array:

The function shown in cell C6 returns a column array. In old versions of Excel only the first cell is returned, but in recent years “dynamic arrays” have been introduced, which automatically display the whole array. Either way, the array can be entered to display fully or in part with the following steps:

  • Enter the function in the top cell and press enter in the usual way
  • Select the range of cells to be displayed, in this case C6:C9
  • Press F2 for edit mode, then Ctrl-Shift-Enter

The function will then display as above, with values in the selected cells, and the function encased in braces, {}, in the edit line.

It may be desired to return to the default display, either to change the extent of the displayed array in any Excel version, or to display the full dynamic array in recent versions. In that case:

  • Select the top cell and press F2 (edit mode)
  • Press Ctrl-A to select all the formula text:
  • Press Ctrl-X to cut the formula and copy to the clipboard
  • Press Ctrl-Shift-Enter
  • All the cells will now be empty
  • With the cursor still in cell C6, press Ctrl-V to re-paste the formula

In this case the dynamic array returns zeros in multiple cells below the desired results. One problem with this, other than appearance, is that if any text is entered in a cell in the output range, the entire output will change to “#SPILL!” in the top cell, with all the others displaying as blank:

The only way to fix this whilst maintaining the dynamic array is to change the UDF code to remove the empty cells before they are returned to Excel. Alternatively, do it the old way and:

  • Select the desired output range
  • Press F2
  • Press Ctrl-Shift-Enter

An alternative for the py_Umom function is to enter the Offs2 input, which specifies the row number, with that row being returned a single value:

The formula is entered with the Enter key, which displays the single value, without the {}.

With Offs2 entered as a relative address, with no $ before the 6, the formula can then be copied to the other 3 rows:

The py_Estress function currently works differently, when the output row is defined it returns a row array with the chosen result in the first cell, and other unwanted data in the rest. This is not a useful feature, but it’s a work in progress. When the function is entered as a dynamic array recent versions of Excel will return the whole row, but just display #SPILL! if any of the cells in the output range are not empty:

The first cell can be displayed with Ctrl-Shift-Enter, but an alternative that may be preferred, and is often inserted automatically by Excel, is to insert @ at the start of the formula, known as the “implicit intersection operator”. This has different behaviour when used on Excel tables of ranges, but for a range returned by a UDF, either VBA, Python, or any other code, it returns the first value of the array, or the top-left for a 2D array:

More details of the @ operator can be found at the Microsoft Site.

As far as I know, Ctrl-Shift-Enter is the only built-in way to display part of an array returned by a UDF, and the displayed results have to start at the top-left. Alternative options using the pyxll cache object will be discussed in the next post.

Posted in Arrays, Excel, Link to Python, PyXLL, UDFs, VBA | Tagged , , , , , , , , | Leave a comment

Monte Carlo simulation with Excel and Python

Posted on 31/05/2026 by dougaj4

The pyxll blog has a post from last year on Monte Carlo Simulations in Excel with Python, with examples and full code going from the simplest implementation to more refined code allowing all the calculations to be set up on the spreadsheet, with the simulation just requiring a single call of the Python code.

The calculation modelled in the pyxll blog example is simply the addition of 3 variables. In this post I have used the same code to do a more complex engineering-based analysis, the evaluation of the ultimate bending capacity of a reinforced concrete section subject to combined bending and axial load. This uses the Py_UMom function, last presented at: py_UMom 1-05, and available for download at:

py_UMom.zip

The rest of the required code, and the example spreadsheet, can be downloaded from:

MonteCarlo.zip

Most of the code is copied directly from the pyxll post, with the addition of a link to the py_UMom code and a new function calling the Scipy Norm function to generate the samples, rather than the PERT function. Note that the examples are purely to demonstrate the process. The values I used to define the range of the distributions were based purely on what “seemed about right” and have no significance beyond that.

The first example below has minimal change to the layout used in the pyxll blog:

The py_UMom function input includes an array of 12 cells defining the cross section dimensions and material properties. Of these, 3 are adjusted in the Monte Carlo simulation, with the rest remaining constant. In the example above the parameters defining the range of the sample distribution are listed adjacent to the input value to be adjusted, followed by the PertRandomVariable function.

In the next example the random variables are listed separately, with the output cell defined in the PertRandomVariable functions input:

This generates similar output to the first example, with the small change in the results being due to the Monte Carlo process:

The final example uses the same analysis process, but uses the Scipy Norm function to generate the input random distributions:

class NormRandomVariable(RandomVar):
    """Random variable using the normal distribution."""
 
    def __init__(self,
                 name: str,
                 target: XLCell,
                 mean_value: float,
                 std_dev: float):
        super().__init__(name, target)
        self.__dist = norm(loc=mean_value, scale=std_dev)
 
    def samples(self, n, seed=None):
        return self.__dist.rvs(size=n, random_state=seed)

The range of the variable distributions is now defined with two values, the mean and the standard deviation. The results are very similar to the PERT distribution:

Posted in Beam Bending, Concrete, Excel, Link to Python, Maths, Newton, NumPy and SciPy, UDFs | Tagged , , , , , , , | Leave a comment

FeedSpot

Posted on 24/05/2026 by dougaj4

Feedspot is a blog database with a huge list of different categories, each with listings of the best blogs, including:

60 Best Excel Blogs to Follow in 2026

with a wide range of blogs, including:

For a complete list of contents see:

FeedSpot helps you find the best blogs on the web.

https://bloggers.feedspot.com

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

Scipy-statistics with all distributions

Posted on 17/05/2026 by dougaj4

Following the previous post the Scipy statistics functions have been updated to include all the distributions, including the discrete distributions. The input to the distribution functions has also been updated to allow arrays to be transferred either as Excel ranges or pyxll cache objects. The updated code and spreadsheets can be downloaded from:

py_SciPy.zip

The screenshot below shows the py_multivariate_norm function with the x array input as a cache object:

Changing x to the range G32:G36 returns exactly the same results:

There have also been changes to the py_linspace function, in the pyNumpy.py file, to allow the results array to be returned either as a range or a pyxll cache.

If the optional Rtn_Cache input is set to False (default) the results are returned as a range:

If Rtn_Cache is set to True the array is returned as a pyxll cache, as for py_linspace_o, which is still available:

The updated pyNumpy.py file comes in two versions, pyNumpy-jit.py and pyNumpy-noJIT.py, with the jit version including calls to the Numba just-in-time compiler. The chosen version should be copied to pyNumpy.py.

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

Transferring different data types to/from Python with pyxll

Posted on 12/05/2026 by dougaj4

When a user defined function (UDF) is created in Excel with Python and pyxll the data type of each argument can be specified, either in the @xl_func decorator, or with a separate @xl_arg decorator for each argument. I prefer the latter, which I find more readable and easier to check.

A problem arises if the argument may have different data types; for instance, in my recent additions to the Scipy Stats functions, one of the inputs is often an array of x values, which can easily be created on the spreadsheet for small arrays, but for very large arrays it is may be better to generate a pyxll cache object, which displays as a single cell on the spreadsheet. I had been creating separate functions for these two cases, but I recently discovered, with the help of Tony Roberts from pyxll, that it is now possible to specify alternative data types for any argument, using Union Types

The code below illustrates this approach, with the ‘x’ argument being specified either as a ‘numpy_array’ or an ‘object’.

@xl_func(category = "Scipy-Stats", help_topic="https://docs.scipy.org/doc/scipy/reference/generated/scipy.stats.multivariate_normal.html!0")
@xl_arg('method', 'str')
@xl_arg('x', 'union<numpy_array, object>')
@xl_arg('kwargs', 'dict')
@xl_return('numpy_array')
def py_multivariate_norm(method, x = [], kwargs = None):
    """
A multivariate normal random variable.
The mean keyword specifies the mean. The cov keyword specifies the covariance matrix.
See on-line help for full list of methods and arguments
    """
    newmethod = getattr(stats.multivariate_normal, method)
    if type(x) == list:
        res = newmethod(**kwargs)
    else:
        res = newmethod(x, **kwargs)
    if isinstance(res, np.ndarray) == False: res = np.array(res)
    return res

In this case the x argument is optional, and defaults to an empty list if omitted, but if included it may be specified either as a cache object:

or a selected range which will be converted to a NumPy array.

The results are the same in both cases.

The same approach may be used to return the function results either as a numpy array, displaying all the results, or a pyxll cache object, which displays as a single cell, but contains all the data, which can either be passed back to Python, or the data can be extracted, all or in part, for display in the spreadsheet.

In this case the Python data must be prepared before being transferred, as described in this pyxll FAQ:

https://support.pyxll.com/hc/en-gb/articles/360036961153-How-can-my-function-return-different-types

This approach has been used in the py_linspace function:

@xl_func(category="py-Maths", help_topic="https://numpy.org/doc/stable/reference/generated/numpy.linspace")
@xl_arg('startnum', 'float')
@xl_arg('stopnum', 'float')
@xl_arg('num', 'int')
@xl_arg('endpoint', 'bool')
@xl_arg('retstep', 'bool')
@xl_arg('rtn_cache', 'bool')
@xl_arg('dtype', 'str')
@xl_arg('axis', 'int')
@xl_return('union<var, object>')
def py_linspace(startnum, stopnum, num = None, endpoint = None, retstep = None, rtn_cache = False, dtype = None, axis = 0):
 
    try:
        axis = int(axis)
    except:
        pass
    kwargs = {'num': num, 'endpoint': endpoint, 'retstep': retstep, 'dtype': dtype, 'axis': axis}
    kwargs = {k:v for k,v in kwargs.items() if v is not None}
    
    res = np.linspace(startnum, stopnum, **kwargs)
    if rtn_cache:
        return res
    else:
        to_var = get_type_converter('numpy_array', 'var')
        res = to_var(res)
        return res

The Python code generates a numpy array, which will be displayed in Excel as a cache object. To display as an Excel range of values the pyxll get_type_converter is used to generate a to_var function that converts the array to an Excel variant array.

The updated Scipy and Numpy code will be included for download in the next post.

Posted in Arrays, Excel, Link to Python, Newton, NumPy and SciPy, PyXLL, UDFs | Tagged , , , , , , , , , | 1 Comment