Really simple or really impossible?

Hey Guys,

Since this is the second time I thought: "Wouldn't it be handy if.." I am now asking the pro's.

For stress analysis of a tube frame, Excel is handy because after the initial sin/cos/tan type formulas, you can see the forces and peaks change with different dimensions of the frame. However, if this frame is a 'mechanic' that changes position, for example the tipping mechanism of a truck, there is an added variable: the angle of the tipping.

If I want to make this graphic in Excel, and want to accurately see how the forces react from 0degs to 90degs tipping, I have to copy/paste the (very complex and big) calculation cells 90 times, each time changing the angle variable +1. It works, but is very tedious.

I just want one calculation that calculates an initial position, with Excel being able to make a graph of the outcome with the initial position +1 on the horizontal scale.

Would this be possible?

Thanks!
Niels Heusinkveld

Thanks for the quick reply.

The case I was referring to was part of my engineering dissertation, which sadly got lost with a HD, luckily after the project was finished! How it worked was basically calculating the position of all joints and endpoints of the mechanism at a given input angle. Once it knew all the x and y locations of each point, and the container weight was given, the force on each of the points was calculated. So for each joint/point I had the force in x and y direction at that given input angle.

The result was 20 forces in either horizontal or vertical direction, and of course 10 vector combined 'total' forces. Next to that the hydraulic cylinder length was also an output.

It was pretty cool to see the effect of changing the geometry of the mechanism on the worst case positions of the frame! Actually, tipping was the easy bit, as the other mode of the container pickup system was the ability to pick up a container from the ground, with a 'hook arm' lifting it on the truck. Shame I lost the file. Of course 90% was just sin/cos/tan, but once there is a LOT of that, it can still be daunting

Swats, if I'm not mistaken, your method still requires the sheet to contain 90 sets of all these calculations?

Regards
Niels

PS: I ran out of sheet width really quickly and ended up with 5 rows of full width copy/pastes, after which I had to copy the outcomes for each joint/point to a table, as Excel could use a big selection for its graphs but not 90 individually selected cells..

niels007 wrote:
> Hey Guys,
>
> Since this is the second time I thought: "Wouldn't it be handy if.." I
> am now asking the pro's.
>
> For stress analysis of a tube frame, Excel is handy because after the
> initial sin/cos/tan type formulas, you can see the forces and peaks
> change with different dimensions of the frame. However, if this frame
> is a 'mechanic' that changes position, for example the tipping
> mechanism of a truck, there is an added variable: the angle of the
> tipping.
>
> If I want to make this graphic in Excel, and want to accurately see how
> the forces react from 0degs to 90degs tipping, I have to copy/paste the
> (very complex and big) calculation cells 90 times, each time changing
> the angle variable +1. It works, but is very tedious.
>
> I just want one calculation that calculates an initial position, with
> Excel being able to make a graph of the outcome with the initial
> position +1 on the horizontal scale.
>
> Would this be possible?
>
> Thanks!
> Niels Heusinkveld

-------------------------

Well, I have a simple brute force approach that I use occasionally.

What I sometimes do with big problems like that is to set it up to take the X
variable from one cell -- A1 say. Then I build a table of X values I'd like to
use and blank cells for the Y values (and/or multiple X or Y values).

At that point it's fairly simple to write a macro that will fetch one of the
desired X values, plug it into A1, take the resulting Y value(s) and plug them
into the table I want to build. Then keep looping through that process until
all the desired X values have been used. From that constructed table then Excel
can plot charts of whatever I was interested in to begin with.

There are possibly more elegant solutions, but this is an easy way to repeatedly
run a calculation. And it's kind of neat to kick off the calculation and watch
the graph be built before your eyes one point at a time.

If you've never programmed a macro before this is a good problem to start with.
Just tell Excel to record a macro as you step through the loop you want once
or twice. At that point you can go into the recorded macro and see what it's
doing and modify it a bit to put a For/Next or While/Wend or whatever around it,
etc.

Bill