# How can Excel be used to model the flight of a rocket? [closed]

Firstly, sorry for any silly mistakes or improper use of this website - this is my first time using it!

I am trying to use Excel to model the flight of the Apollo 11 Saturn V rocket using the real values from the mission evaluation report. However, I can't seem to get the right acceleration. It is always too low. My initial acceleration at t=0.1s is 2.06 m/s^2 while the values in the evaluation report show the acceleration to be close to 10 m/s^2. My final goal is to calculate the acceleration at the S-IC OECO event (at t=161s). My spreadsheet showed 28m/s^2 while the real value is 39m/s^2. Obviously I have done something terribly wrong since the difference between the values is quite large, but I've been looking over my spreadsheet for days now and I just can't find my problem.

What should the columns be?

This is what I have so far:

• Range time (s) - in increments of 0.1s down the column
• Mass (kg) - the mass at that time calculated by previous mass - mass flow rate*0.1s
• g (m/s^2) - the acceleration due to gravity at that altitude
• Weight (N) - mass*g
• Thrust (N) - the thrust at that time using real values from the graphs in the evaluation report
• Drag (N) - using the drag equation and estimated coefficients
• Total force (N) - weight + thrust + drag
• Acceleration (m/s^2) - using F=ma so I did total force/mass
• Velocity (m/s) - previous velocity + acceleration*0.1s
• Altitude (m) - previous altitude + velocity*0.1s

I've taken into account changing thrust, mass flow rate, acceleration due to gravity and drag as well.

The values in the report are given as 'inertial acceleration' but I haven't learnt this yet (still in high school). So is inertial acceleration different to the acceleration I have calculated using F=ma? Have I misused Newton's second law? Was I wrong to consider the weight of the propellants in this case?

I'll appreciate any and all helpful feedback and I can provide more information if needed. Thank you so much! :)

• Note that there is a big difference between "can" and "should". Spreadsheets can often be forced to do programming tasks but above a certain level of complexity they are not the right tool for the job. – dmckee --- ex-moderator kitten Aug 24 '14 at 4:21
• @dmckee: Don't underestimate Excel. I have done major simulations with it. One can do serious numeric math with VBA. I have clocked calculations at close to a hundred MFLOPS on my machine. While that's a far cry of the GFLOPS that this computer is actually capable of, it's still better than what you could get on a Cray in the late 1980s, which, for the older folks among us, is not that long ago... :-( – CuriousOne Aug 24 '14 at 4:29
• I don't underestimate excel: its macros make it Turing complete. But it is a horrible development environment. Debugging is hard. Comments can be added but not read without right-clicking cells, there is no easy way to use revision control, etc. etc. Above a certain scale using a spreadsheet is the same kind of masochism as using a language from the Turing tarpit. Possible, but only worthwhile for a lark. – dmckee --- ex-moderator kitten Aug 24 '14 at 4:33
• @Mel C: Congrats to your interest to simulate the launch! I agree with Ross, that Excel is a reasonable choice, since it has a lot of goodies built in. Another reasonable choice would be Python, if you wanted to go really fast, since it comes with great numerical libraries, but, like Ross said, that's not your problem. Can you link to the report that you are working with? I would love to take a look at how much data there is. Thanks! – CuriousOne Aug 24 '14 at 4:33
• @dmckee: I agree, debugging a poorly designed spreadsheet is hard to impossible. One has to approach it much more like hardware design (for integrated circuits or FPGAs) , i.e. unit testing is absolutely crucial. Major calculations need to be cross checked with data, for which the answer is known. For VBA macros, the debugging is very much like for every other interpreted language. The one thing that VBA has going for it is the incredibly large user base. I have not encountered a single problem, for which there isn't a working solution e.g. on stackexchange! – CuriousOne Aug 24 '14 at 4:38

First - Excel is a fine tool for doing this kind of simulation at the level you want to do. Just remember that integrating equations of motion involves certain errors - you want to make sure you minimize these errors. Two things you can do: use small time steps, and take the average acceleration / velocity over the time step to compute the new position. So instead of saying

$$x_{new} = x_{old} + v_{new} * dt$$

do

$$x_{new} = x_{old} + 0.5*(v_{old}+v_{new}) * dt$$

etc.

There are also many tricks you can use in Excel to minimize the chance of errors - I hope you know how to use named ranges (after assigning the name "gravity" to cell A6 and putting the value 9.81 in it, you can type gravity instead of $A$6 in your formulas, etc).

Some other things to consider (it sounds like you already know this):

• mass decreases as fuel burns
• thrust increases as fuel burns
• the rocket did not go straight up
• at these velocities, coriolis forces play a role in the motion of the rocket

Coriolis forces are always at right angles to the instantaneous velocity, and they appear because an object flying in a straight line will "feel the earth rotate underneath it". Relative to the earth this means the rocket will (on the northern hemisphere) deviate "to the right" as seen from the top.

As the rocket is not flying "straight up", you need to consider that the force of gravity is not directly opposing the thrust of the rocket, and therefore you should not simply subtract the "weight" of the rocket from the thrust - instead you need to do vector subtraction.

I suspect this is the thing you did not do - account for the direction of the thrust.

Incidentally, I hope you will take the time to look at http://www.braeunig.us/apollo/saturnV.htm

There is an enormous amount of detailed info here - it should be of significant help as you try to refine your model.

Finally - looking at the NASA report you linked in your comment (may I suggest you put the link in the question itself - comments are ephemeral) it is clear from table 4-1 that the acceleration at the time of "first motion" was $10.47 m/s^2$.

I have to believe that means they are computing everything relative to a free-falling object (in other words, "standing still" means acceleration of $9.8 m/s^2$). Understanding NASA's terminology and frame of reference will be essential to getting this question right… unfortunately that is not my area of expertise, but that's the information I would look for.

update

I found a very useful NASA report" "space vehicular accelerometer applications"

Quoting from this:

I conclude that "inertial acceleration" is the total vector of acceleration - both due to gravity, and due to thrust. Initially, these two are colinear - but as the rocket trajectory changes, they will be at an angle to each other. Taking this into account in your equations is probably the most important thing to do in order to get some agreement between the values you computed, and the ones given in the flight data you are trying to match. Your reported error of roughly 10 $m/s^2$ agrees well with that hunch...

Good luck.

• Wow thank you so much for your detailed answer! I actually have been using that website and it has proved to be quite useful but I did not consider the direction of the thrust or the Coriolis force. I'll look into that now! Thank you again! :) This website is great! – Mel C. Aug 24 '14 at 5:47

Without seeing your calculations and a link to the report I can't be sure, but I suspect you are reading the report incorrectly. You say the report lists the takeoff acceleration as $10$ m/sec$^2$, which is over $1$ g. This would be very high for a liquid rocket. Wikipedia lists the takeoff weight as $6,600,000$ lbs and the thrust as $7,648,000$ lbs which leads to an acceleration of about $\frac {7648}{6600}-1 \approx 0.158$ g\$. Excel is a great tool for this-that is not your problem.

Added: In terms of what should the columns be, that depends on what data you have and what you consider constant. At one level, you could consider the fuel flow and thrust constant and just model the acceleration as thrust/mass less gravity. That would lead to columns of time, mass, acceleration, velocity, position. At another level, you could consider that the thrust increases as the altitude rises, the fuel flow probably changes with time, etc. Then you need more columns.

• Agree that excel is a fine tool for approaching this problem at the high school physics level. The comments under the question are absolutely off topic as they have nothing to do with the underlying issue. – Floris Aug 24 '14 at 4:42
• Hi Ross and Floris, you're right I am perfectly happy using Excel for now as I'm not doing anything too complicated. Thanks for your answer and evaluation! Someone else did a very similar analysis to me and also has values of acceleration greater than 1g. This spreadsheet does not include a weight column, so I'm wondering if I even need to subtract the force of weight from the thrust. Is this something to do with inertial acceleration? Thanks again :) braeunig.us/apollo/SaturnV.pdf – Mel C. Aug 24 '14 at 5:37