Really simple or really impossible?

[niels007]

Hey Guys,

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

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

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

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

Would this be possible?

Thanks!
Niels Heusinkvel

 

[swatsp0p]

well, you didn't give us your very complex and big formula to help you
write it to add 1 as you copy the formula down your range... so I will
suggest this:

In a distant range (say Z1:Z90) place the values 1-90 (use the auto
fill feature for this).

In your very complex and big formula, instead of entering
"=..........+1....." enter "........+$Z1....." and copy this down your
range of 90 cells (use the drag handle on the cell). The second cell
will be "=........+$Z2....." (which, of course, is a value of 2) and so
on up to Z90 (a value of 90).

could a very simple and small solution such as this work for you?

good luck

Bruce

 

[niels007]

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..

 

[swatsp0p]

I would assume if you are charting the results, Excel will need one cell
for each value to chart...so yes, you would need 90 cells worth of data
for your chart.

Good Luck

 

[Guest]

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