Category Archives: Arrays

Excel functions that return references and new functions

The MyOnlineTrainingHub blog recently posted an article on: Excel Functions that Return References As well as comprehensive advice on the use of OFFSET, INDEX, CHOOSE, IF and INDIRECT, it also covers the new functions XLOOKUP (Excel for Microsoft 365 only), and … Continue reading

Posted in Arrays, Excel | Tagged , , , , , , , , , | Leave a comment

XY Chart problems – 2

The previous chart post (XY Chart problems – 1) looked at problems plotting charts with very short data ranges.  An associated problem is plotting XY charts with data with gaps (see When is an XY chart not an XY chart …? and … Continue reading

Posted in Arrays, Charts, Charts, Excel, UDFs, VBA | Tagged , , , , , , | Leave a comment

More Python Traps

I recently posted on some Python Traps, focussing on code where changes in the values of sub-routine arguments were not reflected in the calling routines.  The reverse can also be a problem: if an array, a, is passed to a … Continue reading

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

LinEstGap with non-linear functions

The LinEstGap user defined function (UDF) allows the built-in Linest function to be used on data with empty cells or hidden rows, returning results that agree with the chart trend-line function, which automatically ignores blank cells and hidden data.  It … Continue reading

Posted in Arrays, Curve fitting, Excel, Maths, Newton, UDFs, VBA | Tagged , , , , , , , , , | 1 Comment

Python Traps

When converting existing VBA code to Python there are a number of obvious changes to the syntax that need to be made for the code to run at all, but there are also a number of not so obvious traps … Continue reading

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