ConbeamU Update; defined support deflections

As promised in a recent post, I have updated the ConBeamU spreadsheet to allow defined deflections to be specified at any support.

The new spreadsheet (including full open-source code), can be downloaded from

In addition to the support deflections the new spreadsheet has the following changes:

  • In addition to providing a list of output point positions it is now possible to specify the number of points required for each span.
  • At support positions output is now provided for the shear and moment on either side of the support.
  • VBA routines are now provided to simplify the entry or re-sizing of array functions.
  • Behind the scenes, the VBA code has been re-arranged to allow many of the functions to be used in the frame analysis spreadsheets, reducing duplication of code.

Defined support displacements and the new input of output points is shown below:

The output from this data is similar to the previous version, except that output X values are included in the output:


The output points may also be defined from a list as in the previous version:


Note that where the same output point is defined twice, at a support or point load position, the output now returns the shear force and moments immediately to the left of the point, followed by the values immediately to the right.

The array entry routines are illustrated below.  Any of the functions may be entered in a single cell:


Then press Ctrl-Shift-S, and the full array will be returned automatically:


The array range may also automatically be reduced in size.  Select the required output range and press Ctrl-Shift-R:


More details of the array entry routines are provided on the spreadsheet:


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

12 Responses to ConbeamU Update; defined support deflections

  1. 4step says:

    Fine job Dougaj4.


  2. 4step says:

    But, sorry, ave you got an Excel 2003 version?
    thanks advance


  3. 4step says:

    Ther’s a problem with DataRangeUA. What’s wrong


    • dougaj4 says:

      If you let me know:
      Exactly what the problem is (including any error messages)
      What you were doing when it occurred
      What version of Excel you are using.

      I will look into it.
      If it’s an Excel 2003 related problem I may not be able to help because I no longer have 2003 installed, but please let me know the details of the problem anyway.

      Liked by 1 person

  4. 4step says:

    There was a missing reference to Smart Indenter v3.4
    Both excel 2003 and excel 2010 show this missing reference.
    So I removed this reference. Now it seems run successfully..
    I was forgetting: Usually, I use excel 2003.


    • dougaj4 says:

      Thanks again for letting me know about this. It seems that there have been changes in the way missing references are handled in 2010 and 2013. I got the same error message when running on my old computer with Excel 2010, but it runs without problems on my new machine with 2013, even after uninstalling Smart-Indenter.
      The download file now has the reference removed, so I hope that will solve the problem for everybody.


  5. 4step says:

    using your files, I have simplified the one
    and I added some comments to help use conbeam function.
    if it can help someone…


    • dougaj4 says:

      Thanks 4Step, I will have a look.

      I will also make sure to remove the smart-indenter reference for future releases.


  6. ASc says:

    I don’t know if this is the right location to post my question, but I’m desperate for an answer, so I hope somebody could help me.
    It regards the calculation of an R² for a linear regression with 10 variables and I have 9 or 10 data points per variable (data from 2004 or 2005 until 2013).
    So I calculate my coefficients with the array function {=LINEST($AD$8:$AD$17;$S$8:$AB$17;FALSE)} where AD8:AD17 are my known Y’s and S8:AB17 my known x’s. I calculated my R² for this equation with =INDEX(LINEST($AD$8:$AD$17;$S$8:$AB$17;TRUE;TRUE);3;1).

    My problem however is that it always returns an R² of 100% which is completely impossible. Even more so because if I take out a variable the R² stays the same.

    Could somebody tell me what the reason for this “bug” could be?

    Thank you so much for your help!


  7. 4step says:

    Hello dougaj4
    Could you take a look on this file.
    I’m using conbeam to analyze a single span with translational restraint and rotational restraint at start (approximately).

    Something doesn’t seem right: why there are reaction at the end of frame,
    and why slope and deflection are zero at the end?
    They must be zero nearby start position. Don’t think you?
    Thanks in advance for your reply


    • dougaj4 says:

      It seems Conbeam isn’t working properly with 1 support. I will have a look and post an update when it is fixed. In the meantime you can use the Cantilever function. The support has to be at one end, but your support is very close to the end, so it shouldn’t make a significant difference. You could also try having two supports very close together.


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 )

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.