This post was prompted by a thread at Eng-Tips:

https://www.eng-tips.com/viewthread.cfm?qid=501339

The beam analysis functions in the ConbeamU spreadsheet allow for the use of any SI units, or any non-SI units found in the long list included with the spreadsheet. I usually use all SI units, but in this case I was wanting to use kip and foot units with the SSSpanU function, and found that the function did not work with the units as entered, and did not provide any indication of the problem. To improve that, I have added to the list of non-SI units (as described below), and modified the code so that it gives a more helpful message when input units are not recognised. The updated spreadsheet can be downloaded from:

ConbeamU.zip

The Eng-Tips discussion linked above concerned ways of finding the maximum deflection of a beam under non-symmetrical loading. This can be easily done using the SSpanU function together with the Excel Solver. A similar procedure can also be used with the ConBeam function. Input for an example beam is shown below (click image for full size view):

• Input and output units have been set to kip and feet units, with output deflections in inches.
• Unused inputs (support stiffness and point moment loads) have been left as SI units. These would normally be set to the same units as the other inputs, although mixed units will be converted and calculate correctly without a problem.
• Output was originally set to display at 2 foot intervals, as listed in column I

The screenshot blow shows the Solver input to find the point with maximum deflection. In general, with non-symmetric loading, this will not be at mid-span or at the point of maximum bending moment.

By inspection, the maximum deflection was seen to be between 13 and 14 ft from the left support. To use the Solver the deflection output for 14 ft has been multiplied by 1000 in cell O28. This is used as the Solver “objective value”. The Solver is set to minimise this value by changing the output location in cell I28. The Solver adjusts the value to 13.9234, with a deflection of -1.028 inches.

There are two reasons for using the factored value in O28, rather than the function output in N28:

• The SSSpanU function is entered as a dynamic array in cell J21. Excel displays the whole output array, but the Solver function does not recognise the cells other than J21 as containing a formula. You therefore need to enter another formula outside the array range, referring to the cell you want to maximise or minimise.
• For results that have a small numerical value such as deflections the Solver default tolerance may result in an inaccurate result.

The location and value of the maximum bending moment is found in a similar way, except the formula in cell O27 does not factor the moment value, and the Solver is set to maximise this value.

The latest version of the units functions now returns a more helpful message if it does not recognise one of the supplied units, as shown below:

The non-SI units list has now been modified to recognise the following abbreviations:

• kip or kipf is recognised as 1000 lbf
• Bending moments may be entered with a space (kip ft) or a point (kip.ft) and using kip or kipf
• Flexural stiffness (EI) may be entered as kipf.ft2, kip.ft2 or kip ft2

Non-SI units are listed on the Ext Unit List sheet. Adding new abbreviations to this list is easy. The example below shows kip.ft added to the list:

• Find the unit type you want to add to (e.g. “moment”).
• Insert a blank row below one of the existing units in that row.
• Copy the row above down into the new row.
• Enter the new unit abbreviation under Symbol (Column C).
• Check that columns E to H are copied correctly, and that the SI Factor is correct.
• Save the spreadsheet, close and re-start.