Using Matplotlib from Excel with xlwings – update

In February 2016 I posted a spreadsheet with examples of linking to the Python Matplotlib library with xlwings.  Since then updates to xlwings required some changes to the python code, specifically has been replaced with pictures.add.

The spreadsheet has now been updated, and can be downloaded from:

Similar code has also been updated in the xlwScipy spreadsheet:

A typical graph example is shown in the scree shot below:

See linking to the Python Matplotlib library for more details. The code in the post has now also been updated.

Posted in Charts, Charts, Excel, Link to Python, Newton, NumPy and SciPy, VBA | Tagged , , , , , , , | Leave a comment

More on trend line equations on line charts

In the previous post we saw that if a trend line equation is added to a line chart it will return the wrong coefficients if the x values are not a continuous sequence of integers, starting at 1 (click on any image for full size view):

Using an XY chart in place of the line chart is one way to get the correct trend line (which will work for any sequence of x values), but there is another way:

Right click on the X axis, and select Format Axis … then set Axis Type to Date axis:

The line chart and the XY chart now display the same (correct) trend line.

The trend line will also still work correctly if there are one or more negative x values (although the values do not display in the axis labels):

But be careful, the line chart treats the x values as being date numbers, and truncates all decimal values to the integer part:

For more details see Jon Peltier’s blog.

Posted in Charts, Excel, Maths, Newton | Tagged , , , , | 1 Comment

Displaying trend line equations on line charts

This post is prompted by a recent comment at Using LINEST for non-linear curve fitting which found that the trend line formula displayed on a chart was totally different from that found using the Linest function.

The problem was caused by using a line chart, rather than an XY (scatter) chart.  A line chart treats the x-axis values as text labels, even when the data range is formatted as numbers.  When calculating a trend line Excel treats the x range as a consecutive sequence of integers starting at 1, regardless of the value displayed.  As a result, if the x values are any other sequence the trend line equation displayed will be totally different to the correct one.

The solution is simple, convert the chart to an XY chart.

As an example, the screenshot below shows the function:
y = 2x^4 + 3x^3 – 4x^2 + 5x + 1
plotted on a line chart:

The trend line is a good fit to the plotted points, but the calculated trend line formula is totally different to the correct one.

The chart type can be changed to an XY (Scatter) type, using the  Chart-Tools, Design ribbon:

The function formula then displays correctly:

Posted in Charts, Charts, Excel, Maths | Tagged , , , | Leave a comment

Three tributes to John Clarke

From Michael Leunig at The Age:

From the ABC:
Remembering John Clarke


From North Palmerston City Council –
After an off-night at N Palmerston, John Cleese named the city “the suicide capital of New Zealand”.  John Clarke, who was born there, suggested that the city council should respond by naming the local rubbish dump after Cleese, which they duly did, complete with official signage.  Much to the amusement of Eric Idle:

Posted in Bach | Tagged | Leave a comment

RCInteract and RC Design Functions 7.03

Since the previous post, I have updated the notes in the RC Design Functions spreadsheet to reflect the fact that with the recent update to the Bridge Design Code (AS 5100) the reinforced concrete strength design requirements for beam bending and combined bending and axial load are now the same as in AS 3600.  The updated spreadsheet can be downloaded from:

RC Design

The RCInteract function will generate a moment-axial load interaction diagram to a number of design codes.  For AS 3600, and now AS 5100 as well, it will also adjust the results according to the specified confinement steel.

The screen shot below shows input and plotted results for 65 MPa concrete to AS 3600 including the effect of compression steel (blue line), and with compression steel ignored:

If the confinement option is set to 1 the compression steel is included for axial loads <= 0.5Phi.Nu:

Confinement option 2 applies the AS 3600 limitations for standard confinement to Cl. 10.7.3, for concrete grades >= 65 MPa:

Confinement option 3 gives results where special confinement to Cl. 10.7.3 is provided, i.e. compression steel is included for all axial loads.

The function input is (arguments in italics are optional):
RCInteract(UMomin, PRange, NSteps, Muin, Code, CompFace, AxLoadA, Confinement):

  • UMomin: Section details (Range D4:D16 in the example data)
  • PRange: Axial load range; default = 0 to Phi.Pu
  • NSteps: Number of output increments; default = 20
  • Muin: Array of moments for each axial load, defining the compression face; default = use CompFace
  • Code: Design code; default = 1 = AS 3600 and AS 5100-2017
  • CompFace: Compression Face, 1 = top face, anything else = bottom face; default = 1
  • AxLoadA: Array of output axial loads; default = use Prange and NSteps
  • Confinement: Confinement option (Code = 1 only); default = -1 (include compression steel for all loads)

Output is an array with 3 or 1 columns, depending on whether AxLoadA was specified; see below:

To display all results the function must be entered as an array function, by selecting the required output range and pressing Ctrl-Shift-Enter.  See Using Array Functions and UDFs for details.

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

RC Design Functions 7.03

The latest update to my RC Design Functions spreadsheet has just been uploaded, and is available for free download (including full open-source code) from:

RC Design

The main new feature in the latest version is a new RCInteract function, which generates an axial load-moment capacity interaction diagram, including corrections for confinement steel type, to AS 3600.

The screen shot below shows sample input and output.  Further details will be given in the next post.

Posted in Concrete, Excel, Newton, UDFs, VBA | Tagged , , , , | 1 Comment

Little Unsaid

From Highgate Cemetery:


Posted in Bach | Tagged | Leave a comment