We are going to go through a simple engineering calculation case study and we are going to
solve it in excel.
A 100 kg sign which is hung from the end of a horizontal support beam.
It is hinged to a wall.
The beam and sign are supported by a cable attached to the beam and above to the wall.
The beam is 2.5 m long, the cable is 3 m and the beam and cable weigh a small amount when
compared to the sign.
Find the attachment position from the cable to the beam that minimizes the tension in
the cable.
It always helps to draw a diagram.
We have a wall over here on the left.
We have a beam that is hinged.
We have a cable here.
The cable has a length of "c" which for this particular example is 3 m.
The length of the beam here "L" is 2.5 m.
"T" is the tension that is in that cable.
If we have too big of a mass as the mass goes up the tension will go up and might even snap.
Attachment position of the cable is "x".
Then we have the mass of the sign which is "M".
Let's try to solve this in excel.
Excel is a nice tool to do multiple engineering calculations because you can do case studies
and it is a nice organized way.
Over here on the left I have put in labels here for our basic data.
I have copied the formula for tension here.
I am going to vary the attachment position from 1 m in increments of 0.1 all the way
up to 2.5 m.
If I wanted to I could type in each number but there is a much easier way to do this
in excel.
You can just highlight two or more of those cells that are in constant increments.
Then go to the very bottom right corner where there is that plus and then I can drag this
down by holding down the mouse button as I am gong down.
I am going to go all the way down to 2.5 and you can see there is that cursor there that
says 2.5.
That filled that in.
Now I am going to put in a formula for the tension as a function of the attachment position.
All excel formulas start with the equal sign.
Our tension is going to equal mass times gravity (and I click on the cells to add it to the
formula) times length times "c".
I am going to put parenthesis here so that we maintain the order of operation and then
have "x" (which is what we are varying and is our independent variable) times
the square root (which is already built into excel) then parenthesis and "c" squared (which
is "^2" in excel) minus "x" squared.
Be sure to close the parenthesis for the square root and close the parenthesis for the denominator
and then I press enter.
That is the tension if we attach that cable 1 m out from the wall.
A couple of formatting things here are that I can click on that and I can go to decrease
decimal here just to make things look a little better.
Now what looks what happens if I try to drag this formula down.
You can drag down formulas by dragging in the lower right corner.
You will see something strange that happens.
If you click in this cell and then go back up to the formula and I click in there you
can see what each term is referencing because everything is lit up in colors.
Then we press enter to go to the next line.
If I go into that formula and click in there you will see that because that cell is one
cell lower it takes all of those references from the previous formula and moves them all
down one.
If I had moved this formula over a column it would have moved all of those references
over a column.
This is not what we want it to do because I want it to maintain for example when "x"
is 1.1 I wanted "M" to be the same that it was when "x" was 1.
To address this problem we use something known as absolute references.
I am going to go back up into my original formula.
To make a reference absolute I can click in that formula then click in the middle of the
reference and I will press the "F4" key.
The "F4 key puts dollar signs before the "B" and before the "5".
That makes it an absolute reference.
I am going to do this also for "g".
I can click in and press "F4".
Then do the same for the other variables.
You can also manually type in dollar signs if you wanted to.
I am going to press enter and it did not change anything with that.
But now if I copy this formula down you will see that if I click in this second line it
made those absolute references.
That "M" is always going to be "M".
I am just going to take that and I can drag it down or since I have a bunch of "x" values
on the left I can double click there and it auto filled that in.
We can look at the tension values as a function of attachment position and the minimum is
somewhere around 2.1.
Attaching the cable about 2.1 m away from the wall will minimize the tension in the
cable for this particular example.