Loading...

Follow Newton Excel Bach - not (just) an Excel Blog on Feedspot

Continue with Google
Continue with Facebook
Or

Valid


Following a comment at update-to-glob_to_loc3-and-loc_to_glob3-functions, I have modified the Python code at the linked site, so it can be run from Excel, via xlwings.  The spreadsheet described below, and the associated Python code, can be download from:

3DCirc.zip

The original code and background information can be found at: Fitting a Circle to Cluster of 3D Points.  The code performs the following functions:

  • Generates points along a circular arc, then applies a random 3D offset to these points, to generate a cloud of points close to the original curve.
  • Finds the best fit circle passing through these points.
  • Generates graphs of the points, and the best fit circles, using Matplotlib.

To simplify the process as far as possible, I have converted the code to two user-defined functions (UDFs) that can be called from Excel, using xlwings, to generate the cloud of points, and to return the coordinates of a series of points along the best fit circle, or along an arc extending over the range of the data.  This data is then plotted in Excel, using xy charts.

Typical spreadsheet input and output are shown in the screen shots below:

The Points_by_ang function generates points along an arc of the specified circle, with random 3D offsets of magnitude determined by the k factor.  The input data in the example is the same as is hard coded in the original Python code.  The Fit_circ3D functions returns 3D coordinates along the best fit arc or circle (or alternatively other results, as defined by the “out” value).  Note that if the number of generated points is changed from 100, the range must be adjusted in the fit_Circ3D function, so that all of the input data range contains real numbers, not #N/A# or blank cells.

The best fit circles, projected to the XY, XZ and YZ planes, are shown below:

The Matplotlib results from the original code are very similar:

Changing the input data, the graphs automatically re-draw to show the new results:

To use different input points (either generated from another source, or real survey data) simply paste the data anywhere in the spreadsheet, and adjust the “3DPoints” range in the Fit_circ3D function, and the chart data ranges for the input data.

To use the spreadsheet:

  • If necessary, install Python, Numpy, and xlwings.  All the necessary packages are included in the default Anaconda Python installation.
  • Copy 3DCirc.xlsm and 3DCirc.py to any convenient directory.
  • Run 3DCirc.xlsm, and if necessary enable macros.
Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

A cubic spline provides a good approximation to a smooth curve, and alternative versions are available for free download (see Daily Download 22: Splines and Curves, Update to AL-Spline-Matrix, and xlwSciPy 1.09 – update for xlwings 0.10 and Scipy 0.18.1), but if a curve has a sharp change of direction a single cubic spline will deviate significantly from the required values near the change.  An example of curves where this is a problem is a reinforced concrete moment-curvature diagram, which has sharp changes of slope at the cracking moment, and also at the reinforcement yield point.

To deal with curves of this sort, I have added an MSplineA user defined function (UDF) to the CSpline2 spreadsheet, which may be downloaded (including open-source code) from:

CSpline2.zip

This function allows a curve to be divided into any number of segments, each of which may be either linear or cubic splines.  Input and output details, and a typical example are shown in the screen shots below:

Required input ranges are the X and Y values of the input data, a list of spline segments, listing the last node number of each segment, the spline type to be applied (1 = linear or 3 = cubic), and end curve or slope details for cubic splines, and the X values where interpolation is required:

Output for this example is shown below:

Looking more closely at the region near the cracking moments it can be seen that the MSplineA function has given a good approximation to the input values, whereas a single cubic spline deviates significantly:

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

In June last year I posted a link to a Jeremy Corbyn speech on the topic of building bridges, not walls.

Here are a couple of (more poetic) links on the same topic.

The first is from Anaïs Mitchell.  This song was written over 10 years ago, as part of the folk opera Hadestown, and the word are those of Hades, Lord of the Underworld:

Anaïs Mitchell - Why We Build The Wall (live in May 2007) - YouTube

An interesting interview with Anaïs Mitchell from 2016:

The singer-songwriter Anaïs Mitchell grew up on a sheep farm in semirural Vermont to a soundtrack of folk ballads and protest music. As a child, she believed that “if you could just write a song good enough, you could change the world.”

That belief has never quite left her. She is testing it in her first musical, the theatrically frisky and musically daring “Hadestown,” a version of the Orpheus myth retold in the American vernacular, which just opened at New York Theater Workshop.

One of Mr. Page’s songs will send a shiver for anyone following the presidential election: “Why We Build the Wall.” Though Ms. Mitchell wrote it a decade a ago, the song has taken on the uncanny echo of Donald J. Trump’s remarks on the campaign trail. Ms. Mitchell is unsurprised. “Political leaders will always invoke that image when it serves them,” she said, “because it appeals at a visceral level to people who feel scared.”

Ms. Mitchell is not scared, and she plans to keep writing — for the concert stage and the theatrical one. The Orpheus-like part of her insists on it. “Whether or not you can change the world with a song, you’ve still got to write the song,” she said. “You still have to try.”

Full interview at The New York TimesNew York Times.

The second is from Melanie Safka, singing “Close To it All” at a live performance in 1971:

Close To It All — '71 - YouTube

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Driving through the Phnom Penh traffic …

and crossing the Mekong River …

Brings us to the Raw Impact construction site  ..

Where we have 4 days to complete 2 houses:

On day 4, with one house complete and one nearly there …

We hand over to two Cambodian families to start a new life …

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Re-visiting the Steeleye Span version of Tam Lin lead me to two very different musical groups that I had not heard of before:

Anaïs Mitchell is an American singer-songwriter and musician, here performing another version of Tam Lin with Jefferson Hamer:

Folk Alley Sessions: Anaïs Mitchell & Jefferson Hamer - "Tam Lin (Child 39)" - YouTube

More on Anaïs Mitchell at Orpheus for the age of Trump: Anaïs Mitchell on her folk opera Hadestown.

The Melbourne Scottish Fiddle Club is an amateur group that explores the lively sounds of Scottish music, with repertoire that both acknowledges and reinvents traditional roots:

Melbourne Scottish Fiddle Club - YouTube

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

A few weeks ago a comment asked for guidance on finding the moment capacity of a reinforced concrete section.  This post will look at finding the depth of the neutral axis (NA) in a rectangular section, with a single layer of reinforcement.  A later post will look at how this can be extended to concrete cross sections with multiple trapezoidal layers, and any number of reinforcement layers.

I have made use of the EvalU spreadsheet, which evaluates functions  entered as text, including evaluation of units.  This spreadsheet is useful for anyone who wants to evaluate functions in Excel, so is worth a look even if you don’t work with reinforced  concrete.

The EvalU spreadsheet can be downloaded from:

Eval2.zip

The input for the examples presented in this post is shown below:

All user-input cells are shaded, grey for numerical data and light blue for units, but note that no cells are protected.

The highlighted cell,  J12, illustrates the use of the EvalU function to evaluate the simple function entered in  H12:

  • Input is the function to be evaluated (H12), the input data range (D11:F12), and the output units (K12).
  • The input data range is a 3 column range listing all symbols used in the function, their values, and their units.
  • The input data must be a continuous range, with text in every row of the first column, but any symbols not used in the function will be ignored.
  • Unlike Excel range names, the symbols are case sensitive.
  • The value of pi could be entered as input data, but as pi() is a built in Excel function it may be included in the function in Excel format, i.e. including the ().

The first example finds the depth of the NA under ultimate loads, for the axial load specified in cell E20, assuming a rectangular concrete stress block with constant stress alpha * f’c and depth gamma * x, where:

  • alpha and gamma are code specified constants
  • f’c is the specified concrete compressive strenth
  • x is the depth of the NA (to be determined)

The steel reinforcement is assumed to be yielded, with a stress equal to the specified yield stress.

The depth of the NA, x, is then given by the expression in D34, with the EvalU function in D36:

The calculated value of x (108.8 mm) is checked to ensure that the nett reaction force is equal to the applied load (1000 kN), and the steel strain is checked to ensure that it is greater than the steel yield strain.

If the applied axial load is increased, the depth of the NA increases, and the strain in the reinforcement reduces so that at some point the steel strain will move into the elastic region, and the steel stress will depend on the depth of the NA, x:

steel stress, fst =  epsc * (Dt-x)/x * Est
epsc = maximum concrete compressive strain
Dt = depyh of reinforcement from the compressive face
Est = steel Young’s Modulus

Equating the sum of concrete and steel forces to the applied load yields the quadratic equation for x, shown in bold below:

Solving this equation for the positive x value finds the depth of the neutral axis, which is checked above to ensure that the sum of the reaction forces is equal to the applied load, and the steel stress is less than the yield stress.

Note that the b coefficient of the quadratic equation includes the value of the increased axial load.  The Evalu function has been modified to allow an optional additional range of input data; for example b is given by: =EvalU(C58,$D$6:$F$19,G58,$C$54:$E$54)

A similar approach can be used to determine the depth of the NA under a specified bending moment, with zero axial load and both steel and compressive concrete strain within the elastic region.  In this case the concrete stress is not known, but if both concrete and steel are in the elastic range then the NA depth is constant for any applied moment.   If the maximum concrete stress is assumed to be equal to x (depth of NA), then the concrete force is equal to the first moment of area of the concrete above the NA, and the depth x is given by the quadratic function shown in bold below:

If the axial load is not zero, the NA depth is no longer constant for varying bending moment, but the eccentricity of the reaction force must be equal to the eccentricity of the applied load, or to avoid division by zero when P = 0, equate P/M for the applied loads and reaction.   As shown below, this yields a cubic equation in x, which can be solved with the user defined function (UDF) Cubic:

The calculated x value is then used to find the bending moment and axial force assuming unit maximum concrete stress; and then the bending moment for the actual applied axial force:

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

In June this year Michael Alexander at Bacon Bits had a post on using Hungarian Notation (or not).  It contained the following quote from Stackoverflow, which for me sums up excellently a good reason for not using it:

“vUsing adjHungarian nNotation vMakes nReading nCode adjDifficult”

But what really made the post useful for me was a comment from Freek van Gilst, who posted a couple of very useful keyboard short-cuts available in VBA:

 When you have the cursor on a variable you can press Ctrl-i to get a tooltip with the type (and scope).
Or press Shift-F2 to jump to the declaration (and Ctrl-Shift-F2 to return to your original position).

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

The Countif function counts the number of cells in a range that match some criterion.  If we enter 1,2,3 in cells A1:A3 and enter =COUNTIF($A$1:$A$3,A1) somewhere else, it will return 1:

But if we enter three text strings with 16 or more numerical characters, that are only different in the 16th or later characters, COUNTIF will say they are all the same:

This problem was reported by John Walkenbach at Daily Dose of Excel, back in 2006.  The solution given then used the SUM function as an array function: =SUM((A1:A3=A1)*1).  The alternative shown below gives the same results.  Note that using SUM the function must be entered as an array, using Ctrl-Shift-Enter.

An alternative that does not require array function entry is to use the SUMPRODUCT function:

But after 11 years Lori Miller returned to Daily Dose of Excel with a way to get COUNTIF to work correctly.  Precede the address of the criterion cell with CHAR(173)&:

CHAR(173) is a “soft hyphen” character, which will ensure that the contents of the data range are treated as text strings, rather than numbers, but is otherwise ignored.  Now all numeric text strings of any length may be entered, and they will only be counted as being the same if they really are.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

In June last year I posted a link to a Jeremy Corbyn speech on the topic of building bridges, not walls.

Here are a couple of (more poetic) links on the same topic.

The first is from Anaïs Mitchell.  This song was written over 10 years ago, as part of the folk opera Hadestown, and the word are those of Hades, Lord of the Underworld:

Anaïs Mitchell - Why We Build The Wall (live in May 2007) - YouTube

The second is from Melanie Safka, singing “Close To it All” at a live performance in 1971:

Close To It All — '71 - YouTube

 

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Driving through the Phnom Penh traffic …

and crossing the Mekong River …

Brings us to the Raw Impact construction site  ..

Where we have 4 days to complete 2 houses:

On day 4, with one house complete and one nearly there …

We hand over to two Cambodian families to start a new life …

Read Full Article
Visit website

Read for later

Articles marked as Favorite are saved for later viewing.
close
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Separate tags by commas
To access this feature, please upgrade your account.
Start your free year
Free Preview