Continuous Beam Spreadsheet – with Units

The spreadsheet previously known as Macaulay has been updated to include “unit aware” versions of all the included functions, and has been renamed “ConBeamU”, to make it more obvious what it does.  The new spreadsheet (including full opens source code) can be downloaded here:

The screenshots below show an example of the new ConBeamU function in action:

Enter input data in columns headed by unit abbreviations:

Input and Output in SI Units

Change input units for beam segment lengths to feet, and use ConvertA function to find the exact equivalent lengths to the original input.  Output values in SI units are unchanged:

Input length values in feet, all other input and output in SI

Some unconventional units (such as the Smoot) are also recognised, and other units can easily be added to the table of non-SI units:

Input lengths in Smoot units, and output deflections in mm.

Also the EvalU function has been added, providing unit aware evaluation of formulae entered as text:

Evalu input in kipf and ft, output in kN and m

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

21 Responses to Continuous Beam Spreadsheet – with Units

  1. metroxx says:

    Here is one bug.
    Please see captures below:


  2. dougaj4 says:

    Hi metroxx – thanks for that. The problem was that for all other supports the support reaction forces and moments are only added in when the section is to the right of the support. For the left hand support I had added in the support reaction force, but had forgotten about the reaction moment, so a section exactly at the end support was always showing zero moment, regardless of restraint conditions. I have added a single span example to the ConBeamU1 sheet, starting at Row 110. The updated file can be downloaded from the original link or here:



  4. Pingback: Daily Download 4: Continuous Beam Analysis | Newton Excel Bach, not (just) an Excel Blog

  5. Pingback: Unit aware continuous beam spreadsheet update | Newton Excel Bach, not (just) an Excel Blog

  6. Afan says:

    When there are only 2 supports and you want the reactions conbeam ignores the output you entered in the function call. I think Out needs to be passed to SSSpan as shown below:
    If Numspans > 1 Then
    ReDim ISupportA(1 To Numspans – 1, 1 To 1)
    If Numspans = 1 Then
    ‘ConBeam = SSSpan(Segments, OutPoints, DLoads, PLoads, Supports)
    ConBeam = SSSpan(Segments, OutPoints, DLoads, PLoads, Supports, , , Out)
    If Supports(1, 1) = 0 Then FixedEnd = 0 Else FixedEnd = 1
    ConBeam = Cantilever(Segments, FixedEnd, DLoads, PLoads, OutPoints)
    End If
    Exit Function
    End If


  7. Pingback: ConBeamU Update | Newton Excel Bach, not (just) an Excel Blog

  8. 4step says:

    Hi dougaj4
    In your opinion, whats’s wrong in my simply example?


    • dougaj4 says:

      In your point load table you had numbers down to Row 168, then spaces down to about Row 600. If you delete all the spaces the spreadsheet will work. To delete numbers from a range you need to select the range and press delete (or use the Clear Contents menu). If you enter a space the spreadsheet treats it as a cell with a value, but because it isn’t a number it gives an error.


  9. 4step says:

    That’s right!
    I had forgotten this particular
    Thank you dougaj4

    and congratulations for the excellent work


  10. Marcel says:

    Thank you for posting all this great stuff!
    I have no knowledge of the methods yet, but would the Macaulay method allow for displacements to be superimposed on supports? I’m thinking of settlement of supports or even optimizing of load distribution by movement of supports.
    Something like this is included in splinebeam2, but that doesn’t allow varying EI in a span between two supports which is why I use Conbeam.



    • dougaj4 says:

      Marcel – Thanks for the interest and the comments.

      There are a few options for modelling a specified displacement so I think I’ll write a post on that topic. In the mean time you might like to experiment with using Excel Goal Seek (for one support) or Solver (for more than one support) to adjust the support stiffness value to get the required deflection.


      • Marcel says:


        Thank you for the suggestions. A new world of excel is opening, but unfortunately both goal seek and solver can only take one target to be optimized. When more than one support needs to be modified this will be a problem (as it is in my case).
        Using influence numbers to unit displacements to set up a matrix should work, but that requires a bit of effort of course.

        Thanks for now and please let me know when you get round to the post on this topic.


    • hadwao says:

      Use a spring support with high stiffness for example 1E20 and put point load over this supports like 1e20 to get 1.0m support settlement.


      • Marcel says:

        Good thought. That’s a workaround I used as well. There is only one problem with that and that’s when I have a known low support stiffness (steel or aluminium structures) that needs to be included together with the deflections.


      • hadwao says:

        Marcel, maybe you can superimpose results? One loadcase is a support settlement and second one is some additional loads with know low support stiffness. The results is sum of results of both loadcases.


  11. dougaj4 says:

    Hi Marcel, you are right that solver will only take one target, but it will take several variables to be adjusted. For each support you want to set a displacement, set up a cell calculating the square of the difference of the calculated displacement from the target displacement (use the square so it is always positive). Then in another cell find the sum of all those values, and that will be the target to minimise. You can then select the column of stiffness values as the variables to be adjusted, or if they are not all adjacent select them as separate cells. I just tried it with four supports and it woks quite quickly.

    I just had a hard disk failure yesterday, so now I’m busy re-installing things on my new machine, but I will do a post about this fairly soon.


    • Marcel says:


      I had fair success using the solver option. It doesn’t quite like some of the bigger calculations, but with some extra effort it got me the result I wanted. With more possible solutions it takes a bit of work to get the most feasible solution, but putting in boundary conditions takes care of that.



  12. dougaj4 says:

    Marcel & Hadwao – thanks for the comments. The artificial high stiffness + support load is a good way to do it. It’s actually built into the Frame4 spreadsheet (which allows fixed supports, or a defined deflection, but doesn’t have spring supports). One point to note is that if the stiffness is too high round off errors result in wrong results. I found a stiffness of 1e10 to 1e12 kN/m worked better than 1e20.
    One other point to watch is that if the settlement is very large the artificial stiffness method will generate an additional downward force at the support, whereas adjusting the stiffness only allows a positive stiffness. Which is more correct would depend on the structure.


  13. Pingback: Continuous Beams with Specified Deflections | Newton Excel Bach, not (just) an Excel Blog

Leave a Reply

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

You are commenting using your 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 )

Google+ photo

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

Connecting to %s