Are you and excel WIZARD??

[tombaron]

If you are, you may be able to help me.

I have written a spreadsheet to calculate the velocity of a rocket after time t.

The rocket is powered by expanding air forcing water out of the bottom.

The speadsheet starts with and initial air pressure and air volume inside the bottle. Values of time are entered into the spreadsheet manualy and the velocity of the rocket is calculated at that moment in time.

The biggest limitation in the formuals however, is that after a certain time (approx 0.0309035s)all the water has been expelled from the rocket and thrust equals zero. Any value of time after this critical value continue to produce values of velocity that are nonsense!!

I need to plot a graph of Velocity against Time for values of time 0 - 0.0309035s with small increments. How can i go about doing this without having to manually enter values of time and note coressponding velocities?

Many thank in advance to anyone who can help.

Tom

[FunkyNige]

If I've read what you're asking right, then column A would be your time and column B your results. You can put 0 into A1, A1+0.00001 into A2, then click and drag the little square in the bottom right of the cell down until you get 0.3 (or whatever it was). If you enter the formula into B1, taking the time from A1, then copy this down using the little square again, Excel will use the time in Ax to calculate Bx.

Then select the data and press chart.

Hope that was what you meant!

[greenv8s]

The easiest approach is just to apply brute force. Stick a formula in col 1 that produces a sequence of incrementing time values. The smaller the increment the more accurate the result will be. Stick a formula the next column that works out the instantanous thrust at that moment. You might want to use additional columns for intermediate results such as volume expelled and pressure remaining. In the next one calculate the acceleration, in the next one the velocity, in the next one the distance. Each of these can be defined very simply from the values above and to the left of the current cell. Once you have defined a row, copy it down through the rest of the spreadsheet as far as necessary to cover the time range of interest.

[tombaron]

Thanks to the two of you. I think you have cracked it!! been woking on it all day and my brain hurts now. GreenV8s - I have got columns of intermediate calcs such as mass of water expelled to alter the new mass in F=ma. I tried factoring in drag but it involves the previous velocity (Cd is know so im using that formula) and so would involve some confusing iteration sh1te that got too confusing!

[greenv8s]

You can do that quite easily. The trick is that each sample (i.e. row) is calculated from the values in the row above it. So you can work out thrust from the volume/pressure/whatever from the preceding row, you can work out drag from the speed in the preceding row, this gives you net thrust, you know the mass (from the preceding row) so you can work out acceleration. Working it out like this assumes that everything stays constant between samples so it is only an approximation, but you can try tweaking the time delta down to see whether the answer changes significantly. Basically make it smaller and smaller until the answer you're interested in stops changing.

[Hirich]

Probably not the answer that you want, but I suspect a better approach would be to do this with proper calculus, as you have several variables.
- Thrust from the ejected water (dependent on internal pressure, which varies with time, and the performance of your rocket 'engine')
- The reducing mass of the accelerating object (as the water is ejected)
- Gravity (or the component thereof)
- Aero drag, dependent on volume
So I suspect that your formulae are incorrect anyway. Using F=ma (or a=F/m):
Instantaneous acceleration = (1/m)*(Sum of forces acting on the rocket)
= Thrust - Gravity component - aero drag
m (of rocket) is a function of time
Thrust is also a function of time
Gravity (as an acceleration figure) might vary (if there is a trajectory). It definitely varies, as the mass reduces, but this cancels out
Drag (if you include it) is a function of velocity
Which leaves you with a nice messy formula. Clean it up to create a forumla of the form a = f(t)
The integrate from t=0 to t=(fuel runs out)

However, if Excel is the way you need to proceed, can you not include an IF function: IF (mass of stored water = 0), Then Thrust =0; If (mass of stored water > 0), Then Thrust = Calculated Figure

[tombaron]

greenv8s said:

You can do that quite easily. The trick is that each sample (i.e. row) is calculated from the values in the row above it. So you can work out thrust from the volume/pressure/whatever from the preceding row, you can work out drag from the speed in the preceding row, this gives you net thrust, you know the mass (from the preceding row) so you can work out acceleration. Working it out like this assumes that everything stays constant between samples so it is only an approximation, but you can try tweaking the time delta down to see whether the answer changes significantly. Basically make it smaller and smaller until the answer you're interested in stops changing.

Cheers. You obviously know your stuff!

Sounds quite difficult to do, maybe i havent understood you too well. I'm not interested in an especially accurate answer, thats why im using the simple Euler method, besides i dont know how else to do it!

[tombaron]

Hirich said:

Probably not the answer that you want, but I suspect a better approach would be to do this with proper calculus, as you have several variables. - Thrust from the ejected water (dependent on internal pressure, which varies with time, and the performance of your rocket 'engine') - The reducing mass of the accelerating object (as the water is ejected) - Gravity (or the component thereof) - Aero drag, dependent on volume So I suspect that your formulae are incorrect anyway. Using F=ma (or a=F/m): Instantaneous acceleration = (1/m)*(Sum of forces acting on the rocket) = Thrust - Gravity component - aero drag m (of rocket) is a function of time Thrust is also a function of time Gravity (as an acceleration figure) might vary (if there is a trajectory). It definitely varies, as the mass reduces, but this cancels out Drag (if you include it) is a function of velocity Which leaves you with a nice messy formula. Clean it up to create a forumla of the form a = f(t) The integrate from t=0 to t=(fuel runs out) However, if Excel is the way you need to proceed, can you not include an IF function: IF (mass of stored water = 0), Then Thrust =0; If (mass of stored water > 0), Then Thrust = Calculated Figure

Yeah calculus like that is well beyond me for now, it frightens me to think what may be involved! Thanks any way. I suppose i could try using if but i dont want to complicate it too much now, ive been tinkering all day and the whole spreadsheet is so complicated already, i wouldnt be surprised if it was riddled in errors.

[tombaron]

Well thanks to everyone who has helped me tonight. After reading your ideas i realised that i had made things overly complicated with a poor structure to the sheet, and so rewrote it. It seems to give logical answers now, up until a point when velocities start ncreasing again. I can only guess that it is due to approximations and the reducing mass as time progresses?? Anyway its a group project and only 2 of us are really working on it. Ill give it to the lazy ****s to sort tomoro. As far as im concerned ive done my bit!

[Jinx]

Be careful though - Excel uses floating point to calculate series of values hence you will get inaccuaracies creeping in after a long series of iterations (i.e. 4 will become 3.999999999 which later on may become 3.999998 etc).