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: ConBeamU.zip
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
Newton Excel Bach, not (just) an Excel Blog 
Hello,
Here is one bug.
Please see captures below:
LikeLike
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:
http://www.interactiveds.com.au/software/ConBeamU.zip
LikeLike
Pingback: ELLAMENTAL MEASUREMENTS/DEGREEZ/ANGLES 1440° | S.M.I.L.E. & oooOHMmmm AWARENESS
Pingback: Daily Download 4: Continuous Beam Analysis | Newton Excel Bach, not (just) an Excel Blog
Pingback: Unit aware continuous beam spreadsheet update | Newton Excel Bach, not (just) an Excel Blog
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)
Else
If Numspans = 1 Then
‘ConBeam = SSSpan(Segments, OutPoints, DLoads, PLoads, Supports)
ConBeam = SSSpan(Segments, OutPoints, DLoads, PLoads, Supports, , , Out)
Else
If Supports(1, 1) = 0 Then FixedEnd = 0 Else FixedEnd = 1
ConBeam = Cantilever(Segments, FixedEnd, DLoads, PLoads, OutPoints)
End If
Exit Function
End If
LikeLike
Thanks Afan, I’ll fix that up.
LikeLike
Pingback: ConBeamU Update | Newton Excel Bach, not (just) an Excel Blog
Hi dougaj4
In your opinion, whats’s wrong in my simply example?
Thanks
https://app.box.com/s/uqixwxmzopjn7hxbczm0
https://app.box.com/s/ybjujfr5wxk9ghjcn5ac
LikeLike
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.
LikeLike
That’s right!
I had forgotten this particular
Thank you dougaj4
and congratulations for the excellent work
LikeLike
Hi,
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.
Thanks,
Marcel
LikeLike
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.
LikeLike
Doug,
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.
LikeLike
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.
LikeLike
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.
LikeLike
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.
LikeLike
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.
LikeLike
Doug,
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.
Thanks.
LikeLike
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.
LikeLike
Pingback: Continuous Beams with Specified Deflections | Newton Excel Bach, not (just) an Excel Blog