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 , , , , | Leave a 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

Indexing NumPy arrays with floats …

… now you can’t.

I recently updated to the latest version of NumPy (1.12.1), and today I discovered that some of my spreadsheets using NumPy arrays (via xlwings) were returning errors like:

IndexError: only integers, slices (`:`), ellipsis (`…`), numpy.newaxis (`None`) and integer or boolean arrays are valid indices

The problem was that using floats to index NumPy arrays previously generated a warning, but now generates an error:

DeprecationWarning to error
•Indexing with floats raises IndexError, e.g., a[0, 0.0].
•Indexing with non-integer array_like raises IndexError, e.g., a[‘1’, ‘2’]
•Indexing with multiple ellipsis raises IndexError, e.g., a[…, …].
•Non-integers used as index values raise TypeError, e.g., in reshape, take, and specifying reduce axis

You might ask, why use floats to index an array in the first place?  The reason is that if a range is copied from Excel to Python then by default all the values are passed as float64.  The array could be converted to all integers, but if the values are mixed integers and floats (such as an array of node numbers and coordinates) that won’t work.  In previous versions the values to be used as array indices could just be left as floats, but now it is necessary to convert them to integers before using them as index values.

I will be working through my Python code posted here and update as required, but if you come across an IndexError anywhere, please let me know.

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