Evaluating text with units and finding the neutral axis

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 function, which evaluates functions  entered as text, including evaluation of units.  This function 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 function can be found on the Units4Excel spreadsheet, which can be downloaded from:

Units4Excel.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:

 

This entry was posted in Beam Bending, Concrete, Excel, Maths, Newton, UDFs, VBA and tagged , , , , , , , , , , . Bookmark the permalink.

5 Responses to Evaluating text with units and finding the neutral axis

  1. GregB says:

    Hello Doug,

    unfortunately, it seems that the EvalU spreadsheet is included in none of the 2 xlsb files.

    But I like the way you use the “Evaluate” instruction, allowing user to give a “litteral” function, and returning either the result for a particular x, or the integral over a given interval, etc…

    Don’t you “hit” the hard limit of 255 characters too often?I have in mind that when using “evaluate” , excel remplaces numbers by a 15 character string so it is quite easy to make the evaluation fail…

    actually I tried to use your spreadsheet to integrate the volume of a torispherical vessel (I work in chemical engineering). The calculation is not very complex but formulas are long

    there is a excel.uservoice item about this limitation
    https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/10208379-remove-the-255-character-limit-in-excel-object-mod

    thanks for your Blog!

    Greg

    Like

    • dougaj4 says:

      Thanks for pointing that out Greg, I’ll fix the wording when I have time. EvalU is the function name, which can be found in the Eval2 spreadsheet.

      Regarding the 255 character limit, you might like to look for Python versions of the Eval function.
      I’m in Cambodia at the moment, but I’ll write more on this when I get back.

      Like

      • dougaj4 says:

        It looks like I didn’t get back to this, back in January.
        The EvalU function is actually found in the Units4Excel spreadsheet. I have now corrected the link.
        Regarding the 255 character limit, I will today post a link to a spreadsheet with a Python based EvalU function, which does not have this limit.

        Like

  2. Pingback: Working with units in Excel – using VBA and Python | Newton Excel Bach, not (just) an Excel Blog

  3. Pingback: Units and solvers with Pint and Sympy | Newton Excel Bach, not (just) an Excel Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.