This is a user’s guide to my entry spreadsheet model that is comprehensive enough for the reader to literally create his own spreadsheet from scratch. It presumes the reader has Microsoft “Excel” on his computer. I believe without proof that the Microsoft “Works” spreadsheet could be used to execute the calculations, but I have doubts about its graphing capabilities. “Excel” has the graphing capabilities that I used quite effectively.
When I first set this up, I created workbook pages in Excel for generic entry data regarding Earth, Mars, and Titan. The same analysis was programmed into one (Earth), and then copied to the other two (Mars and Titan), and then customized for each set of input data. I have since used the Mars spreadsheet the most, and I also added a bit to that analysis (in terms of speed of sound for local Mach number). That Mars entry model is the baseline spreadsheet layout presented here. I have never updated the other two.
Some of these spreadsheet cells have color shading. The yellow refers to user input data. The green is where I highlighted “end-of-hypersonics” at about Mach 3, and the brown is the closed-form estimates from the original 1956-vintage analysis that I found to be unreliable, and labeled “DO NOT USE”. The simplified model was described in reference 1, and its convective heat transfer estimates I edited in an earlier article here on “exrocketman” (reference 2). Actually, the closed form solutions for peak gees and peak heating rate are not bad. It is the closed-form total heat absorbed estimate that is really very wrong. But, I just do not trust any of those closed-form estimates. Numerical integration I trust.
The entry model uses an exponential scale height approximation to the density versus altitude relationship, and uses altitude as the ultimate independent variable, just as it did in 1956. It assumes constant ballistic coefficient, and constant local flight path angle, and is therefore set up in two-dimensional Cartesian rectangular coordinate format for simplicity. Calculations begin at “atmospheric interface” conditions, and work downward from there. I used the data given in reference 1 for those interface altitudes. (This is also a model that I do remember seeing in engineering graduate school, although my notes from then are long gone.)
In particular, as a custom user-input (also highlighted yellow) I inserted some extra rows to provide a finer digitization of the altitude, so that I could get explicitly-calculated data closer to Mach 3. The user is always free to do things like that. Just be sure to drag your calculations down across the inserted rows, because sometimes formulas do not increment in the spreadsheet in the way you intended.
Figure 1 (at end of this article) is a bitmap image of the Mars entry model spreadsheet copied into Windows “Paintbrush”, and edited there to restore the overall cell headings. It will be a little hard to read the detailed data in that overall view, but that is not the point. The point is: where are the basic user inputs? They are in the block of cells from columns A to O, and rows 1 to 5. Variable names, units of measure, and values are in separate cells, with the actual input values highlighted yellow. The user just needs to plug in the appropriate numbers for his case.
These basic inputs comprise the (constant) vehicle ballistic coefficient beta in cell C2, and its “nose radius” rn in cell C3. That radius is actually just the radius of curvature of the heat shield surface facing the oncoming flow. The value refers specifically to radius of curvature at the stagnation point, if the curvature varies.
The density versus scale height model is an exponential curve fit approximation involving scale height and an effective density at “zero” altitude that is a curve fit constant, not the actual surface density. There is a range of altitudes, between which this curve fit approximation is valid. Those four items are input as follows: density “zero” in cell F2, scale height in cell F3, lower altitude limit of applicability in cell F4, and upper altitude limit of applicability in cell F5.
Those last two inputs are for simple visual comparison with the altitudes where “things are happening” in your analysis. They are not used in the calculations at all. If the computed deceleration pulse falls outside those limits, then you need to revise your density scale height model so that it falls inside. The density “zero” rho0 and scale height densH need to be selected so that calculated density matches rather closely the actual atmosphere density versus altitude (as in reference 1) between the two limits.
The entry conditions are described by interface altitude, vehicle velocity at that interface, and the flight path angle at interface (as degrees of depression below local horizontal). The over-simplified analysis will hold that flight path angle as constant, but experiences with non-lifting warhead entry show this to be a pretty good approximation, surprisingly enough. Those input data go in as follows: interface altitude zatm in cell I2, interface velocity Vatm in cell I3, and interface path angle theta in cell I4.
For the convection model, the stagnation-point convective heating rate k-factor (krate) in the old correlation goes in cell L2. A separate k-factor (ktot) for the closed-form total absorbed heat went into cell L3, but that is not to be used, because it is inaccurate, and has been shaded brown “DO NOT USE” as shown. The heating rate k-value krate used here is the correct metric-units version of the factor discussed in reference 2, not the somewhat-incorrect value used in reference 1. There is no correct value to use for the total-absorbed k-factor ktot.
I have shown the escape velocity at interface altitude Vesc in cell N4, although that is not required to be a user input, and is not used in the analysis. I included it merely for easy visual comparison of entry conditions with escape conditions. The analysis can be used for entry interface velocities above interface escape speed. Circular orbit speed at interface is escape divided by the square root of 2.
The C-factor value in cell O2 is not a user input, but it is a constant used in the analysis. It is computed from the listed user inputs as follows:
Cfactor = Rho0*1000*densH/(2*beta*sin(theta/57.296)) where densH is the scale height value and rho0 is the “zero” value in the correlation, beta is the vehicle ballistic coefficient, and theta is the depression angle of the trajectory in degrees.
The actual formula contained in the cell O2 is:
where the 57.296 converts degrees to radians
Column A from rows 7 to 40 describe the altitudes in the analysis, with an alphanumeric heading in cell A7. Going down the column, each altitude is the previous altitude minus 5, units being km. The first altitude is the entry altitude, which it copies from the user input in cell I2. That cell formula is simply
with all the drag-down calculations below it looking like that in cell A9:
Velocity at any given altitude is computed from the corresponding altitude by means of the old modeling equation, and it is listed in column B from row 8 down to row 40, the alphanumeric heading being in cell B7. That formula is
V = Vatm*exp[-Cfactor*exp(-z/densH)]
where z is the altitude in km
and with the corresponding spreadsheet formula in cell B8 being:
note the $ signs that lock in the constants against incrementing with the pull-down of the calculation.
The next column (C) contains horizontal range data computed as R=-(z-zatm)/tan(theta), for which the corresponding formula in cell C9 is typical:
Slant range is on column D, and is computed using the sine instead of the tangent. The corresponding formula in cell D9 is typical:
Computed time starts from zero at entry interface in cell E8. From there the increment of time is the difference in slant range from current to previous, divided by the average of the current and previous velocities. This increment is added to the previous time to estimate current time. The formula in cell E9 is typical:
Deceleration gees is really just the velocity difference current minus previous, divided by both the time difference current minus previous, and the standard acceleration of gravity. There is a meter-km conversion in it, and the negative result is made positive to minimize confusion. The formula in cell f9 is typical, and it starts from zero in cell f8:
Column G is just a repeat of column B, just to get the velocity data in the spreadsheet table located to the right of the slant range data in column C. That enables cross-plotting of the data using slant range as the independent variable. Intuitively, that slant range is your position in the sky between the entry interface and the “end-of-hypersonics” at around Mach 3. Horizontal range “wraps” around the curved surface of the planet or moon, and altitude becomes the radial extension beyond the surface radius.
Column H contains the calculated density coming from the scale height correlation equation given above. It is calculated from the corresponding altitude very simply, and the formula in cell H8 is quite typical:
Column I contains the computed stagnation point convective heating rate, below the alphanumeric heading in cell I7. It is computed from the empirical (and dimensionally-inconsistent) correlation equation:
Q = krate*[(rho/rn)^0.5]*[(V converted to m/s)^3.0]
where rho is the density in column H
The formula in cell I8 is typical:
Column J is the numerically-integrated total absorbed heat up to the corresponding time t in column E. The increment in heat is the time difference current minus previous, multiplied by the average of the current and previous heating rates. This increment is added to the previous total heat, starting from zero in row 8. A typical cell formula is that in cell J9:
The speed-of-sound and Mach number computations in columns K and L of the Mars spreadsheet were never added to the Earth or Titan spreadsheets, as can be seen in Figures 2 and 3 below. I added these specifically to incorporate a linear variation approximation of Martian speed of sound applicable between 10 and 60 km altitudes. It’s not all that far off, all the way down to the surface. This was based on the typical Mars atmosphere model reported in reference 1. That speed of sound correlation was c, m/s = -0.740*(z,km)+236.4, and the corresponding Mach number in column L was nothing more than just V/c, with due regard for the km/s-m/s conversion.
I did not fill the entirety of columns K and L with this, as can be seen in Figure 1. I started arbitrarily at 80 km in row 19, and carried it all the way down to the surface. Any reader can do exactly the same thing, you just need to curve-fit speed-of-sound data versus altitude to suit your needs. The corresponding typical cell formulas are those of K19 and L19, as follows:
=-0.74*A19+236.4 speed of sound in K19
=1000*B19/K19 Mach number in L19
Processing Output Graphs
I usually set up my sheet to start at interface altitude, decrementing by 5 km to the surface or maybe one increment below. This works just fine for both Earth and Mars. For Titan, I used 25 km decrements, because the atmosphere is so deep. There are not enough rows available at any such decrement to “capture” the Mach 3 point, so I have to insert extra rows at finer decrement to “capture” it closely enough. Be sure and re-drag your calculations down across the inserted rows, in order to be sure all the cell formula incrementing gets done correctly – I have seen this fail otherwise.
I recommend cross-plotting your output data right there in the spreadsheet. I use four “standard” formats: (1) deceleration gees (column F) versus slant range (column D), which shows exactly where in the trajectory that pulse is, (2) velocity (column G) versus slant range (column D), which shows the sudden and dramatic decrease in speed through the deceleration pulse, (3) range (column C) and slant range (column D) versus altitude (column A), to illustrate the constant-angle nature of this trajectory, and (4) heating rate (column I) and heat absorbed (column J) vs slant range (column D) for comparison to gees (the heating rate pulse occurs a little earlier in the trajectory than the deceleration pulse).
I usually copy these plots to Microsoft “Paintbrush”, where I can easily add labels and notations and extra artwork, as desired. In particular, I like to mark the Mach 3 points on the plots. The analysis happily calculates data past that point, but the entire entry model is no longer applicable beyond it, because the flow field is no longer hypersonic. But the major effect is a shift from aerodynamic force dominance to gravity force dominance. From this point, gravity drags the trajectory ever more sharply and quickly downward (the two-dimensional straight line assumption is bad).
The Revisited Mars Lander Model
An example is the Mars entry spreadsheet, as it is currently set up for entry from low circular orbit. The detailed inputs are blown up for legibility in Figure 4, and correspond to the image in Figure 1. The revisited vehicle design has a larger ballistic coefficient than any of the probes, being “representative” of a much larger manned lander (400 kg/sq.m), with a fairly blunt heat shield (12.4 m radius of curvature at the stagnation point). The four annotated graphs were presented in reference 3. Entry interface is time zero at 135 km altitude, 3469 m/s, and 1.63 degrees depression angle.
The Mach 3 end-of-hypersonics point is 4140 km downrange and 17.2 km altitude. This is at 4141 km slant range: remember the estimated path is still locally depressed only 1.63 degrees, even though the range actually stretches quite a way around the curved planetary surface. Velocity is 675 m/s, and 1.17 KJ/sq.cm of heat has been accumulated by convection at the stagnation point. All of this is about 1426 sec (not quite 24 minutes) after entry interface.
Peak deceleration gees (near 0.72) occurred at about 1187 sec, 25 km up, and about 1772 m/s velocity. Peak stagnation point convective heating rate (about 2.6 W/sq.cm) occurred at about 1034 sec after interface, and 35 km up. Velocity was about 2800 m/s. One can even compute the delta-vee afforded by hypersonic drag, as the entry interface velocity minus the end-of-hypersonics velocity: some 2794 m/s, which is about 80% of the total interface velocity.
The Earth Entry Model
This is a generic model that somewhat resembles some of our 1960’s-vintage unmanned space payloads, but at conditions similar to those of Apollo coming back from the moon. These are 200 kg/sq.m objects with a 1-meter nose radius, entering at escape speed (11.058 km/s) and 1 degree below horizontal. (Apollo was over 300 kg/sq.m, with a much blunter heat shield.) Interface altitude (time zero) is 140 km. The original annotated results plots for this case were given in reference 2.
The model predicts end-of-hypersonics about 684 sec (just over 11 minutes) after interface, at an altitude of 45 km (just over 147,000 feet), 5443 km downrange, and 837 m/s. About 35.2 KJ/sq.cm convective heating has been accumulated at the stagnation point. From there the trajectory would bend sharply downward, with stabilizing drogue chutes deployed at no more than about Mach 2.5 (near 690 m/s).
Peak deceleration gees would be near 5.4 at 470 sec, about 50 km up, and near 5770 m/s. Peak convective stagnation point heating would be near 182 W/sq.cm, about 395 sec after interface, and 65 km up, and at about 9385 m/s velocity. Aero deceleration delta-vee is some 10.221 km/s, for about 92% of the entry velocity.
The Apollo capsule had a “heavier” ballistic coefficient, and a blunter heat shield. It would be an interesting study to run Apollo at these entry conditions and compare to the actual entry trajectory returning from the moon. Amazingly enough, even this model “looks pretty close”.
The Titan Entry Model
This is a generic model of a large probe entering Titan’s atmosphere at something “typical” of interplanetary transfer speed, and at 1 degree depression below horizontal. Interface was at 800 km and 3 km/s. The ballistic coefficient is 200 kg/sq.m, with a 1-meter nose radius. The original annotated results plots for this case were given in reference 2.
End-of hypersonics at local Mach 3 is estimated to be 11,472 sec (a bit over 3 hours) after interface, at about 300 km altitude, and wrapped around the moon about twice at nearly 29,000 km range. Speed is 730 m/s. The numbers show a very low peak deceleration of around 0.06 gees, peaking right before the end of hypersonics. 6.4 KJ/sq.cm was accumulated, with a very low peak of 1.4 W/sq.cm at about 400 km and 2509 m/s.
I rather doubt this entry model is very realistic, because of the way it wraps around the moon. The entry depression angle is too low.
1. “Atmospheric Environments for Entry, Descent, and Landing (EDL)”, C. G. Justus (NASA Marshall) and R. D. Braun (Georgia Tech), June, 2007
2. “Back-of-the-Envelope Entry Model”, 7-14-12, posted at “http://exrocketman.blogspot.com”
3. “Manned Chemical Lander Revisit”, 8-28-12, posted at “http://exrocketman.blogspot.com”
Figures are below (click on one to see them bigger, "esc" gets you back):