Nov 10, 2009

Celestial Architect Spreadsheet

To simplify the effort for myself, & for others who may be interested i've created a spreadsheet for moderate control without much effort.

Download Celestial Architect Spreadsheet 1.2 below:

EDIT:   Updated to 1.1. Renamed the Spreadsheet and added another page dealing with the moon.
EDIT:   Updated to 1.2 with a better temperature calculation and a sheet for albedo, all thanks to Orion.

Using The Sheet

The "Sheet" is a spreadsheet-- a file you can open with Microsoft Office, or free alternatives such as OpenOffice or it's Mac port, NeoOffice.  All you need to do is enter numbers in the yellow boxes, & all the other figures will be instantly recalculated.

Your Sun

Now, as i've mentioned before, the most important variable is the mass of your sun, from which all it's other stats can be pretty accurately derived.  The sheet assumes that your star is a main sequence star, like 90% of the universe.  If you want to try a dwarf or giant star, you are on your own, but it shouldn't be too hard to plug in those numbers if you are familiar with spreadsheets.  I think all you would have to do is change the way luminosity is calculated to something appropriate to your new star type.

Some things about stars to be aware of:

  • A smaller star puts out a lot less heat & light than you might expect, & the opposite is true of larger stars.
  • Smaller stars have habitable zones which are closer to the star, so the planet's year is shorter.  Again the opposite is true of larger stars.
  • As a star's size increases, so does the depth of the habitable zone, so with a bigger star you have more room to adjust your orbital position (& thus length of the year) or maybe fit in multiple habitable planets.

Habitable Zone

The Habitable Zone (also known as Goldilocks Zones) is the area in space where a planet that could supports "life as we know it" might be.  It doesn't mean that a suitable planet will be there, or that life will exist, just that both are theoretically possible in the zone. As might be expected not everyone agrees exactly how big the habitable zone is.  I've chosen a newer, more conservative estimate from this PDF by Tom E. Morris, which has great in-depth explanations.

If you are clever you may be able to think of an unusual circumstance that allows your planet to be habitable outside of the normal zone.

The Habitable Zone from Wikimedia Commons.

Length of Days & Years

The length of your planet's year is set by the mass of it's sun, & the distance of it's orbit.  However the length of it's day can be pretty much whatever you want.

You might even want to tidally lock your planet to it's primary (as the Moon is to Earth), so that one side is eternally day, & the other night.  Tidally locked planets are generally thought to be incompatible with "life as we know it", but a lot may depend on the specifics of the planet.  At the very least the weather will be freakishly weird.  Any planets in the habitable zone of an M-class star are believed to likely be tidally locked.  Note "M-class" doesn't really mean "earthlike" as it did in StarTrek.  M-class are generally red dwarf stars.

To really complicate the calender you can make your planet into a moon of a larger planet such as a gas giant.  Or you could put multiple stars in your solar system.  If so, good luck, that's more math than i want to deal with.

Heat & Light

Temperature is one of the variables that, in my opinion science fiction doesn't use to it's full potential very often.  There are plenty of "desert planets" & "ice planets", but they are blandly monotone, & realistically uninhabitable.  With just the right temperature you could instead have a hot planet where only the polar regions are cool enough for human life, with an deadly equatorial belt where water boils away.  Or a cold planet where a narrow equatorial band is the only place where the ice ever melts, with massive ice caps dominating the planet.

Albedo is the measure of how white or reflective your planet is overall. Any radiation reflected away isn't going to warm your planet. You can find out more about the albedo of various materials here.

The "Greenhouse Effect" mentioned isn't about climate change apocalypses.  It's more basic than that.  A greenhouse traps some of the energy that would normally be reflected back to the sky.  Our atmosphere does the same thing, which keeps the planet from getting too cold.  It's the strength of the greenhouse effect that is critical, which (among other factors) makes Venus a deadly inferno.

Be aware that this is not the final word on your planet's temperature.  There are lots of other factors like axial tilt, land/sea arraignment & coverage, & mountain placement which will effect what the temperature is at a particular time & place.  We'll get to that later.

Finally i should thank "Geoff" for his page Creating an Earthlike Planet.  Even though the equations are found elsewhere, i didn't understand many of them until i read his explanation.

Instructions for second page to be added...


  1. this is an amazing little spreadsheet, but it omits a major part of the determination of a habitable zone: the planets mass, (which in order to make things realistic and simple, should be derived from its size and overall composition) and atmosphere. the reason for this is the definition of the habitable zone.

    the habitable zone, as im sure you know, is where liquid water can exist and stay as liquid water (some would argue that life could exist outside the habitable zone, just going dormant during summer or winter, whenever water goes out of its desirable phase). gravitational and atmospheric pressures make up a large part of where the boiling point lies, and thus where the habitable zone lies.

    for example, a higher gravity planet would presumably attract a more dense atmosphere, creating very high pressures. these combined pressures would make it harder for water to boil, resulting in a higher boiling point, and thus a planet which requires to be closer to its star than the average earth sized planet.

    i am currently working with a planet thats roughly mars sized (probably larger, im terrible with these calculations, so im not sure if it would be realistic), thus having lower gravity and a thinner atmosphere. because of this it would probably be further out from its star, at the higher end of what this spreadsheet determines is my habitable zone. im not sure how large or massive this orb is, so im not sure if you have to worry about it that much.

  2. oops, sorry, i just noticed that you do have a planet size/density calculator, its just tucked away in another tab. this is my first time using excel in ages. but even so, it does not seem that the habitable zone beginning and end is affected by this change in mass.

  3. Thanks, i'm glad you find it useful.

    You misunderstand exactly what the 'Habitable Zone' means. It's a ring around the star where a habitable planet might exist. You can have a habitable zone without any planets in it. I updated the text of this post to make that clearer.

    You are right that mass and atmosphere are important in determining whether a planet is actually habitable. Mass and density are dealt with in this post:

    I'd love to provide more info about how mass effects the atmosphere, but i simply haven't been able to find it.

  4. Just wondering, would it be possible to "reverse-engineer" the values in the Celestial Architect Spreadsheet so you could enter the average temperature of the planet, and work backwards from there?

    (Sorry, spelling errors)

  5. Metalraptor:
    Technically no. The average temperature is based on 4 other variables (since this is a simplification). So an average temperature of 20º could be achieve by different combinations of closeness to the sun, larger or smaller suns, and different levels of albedo and a greenhouse effect. There's not only one combination that will yield 20º.

    But practically, if you choose values for 3 of the variable, you could switch around the formula to do that, or simply raise or lower the last variable until the temperature is what you want.

  6. Just another question regarding the Celestial Architect Spreadsheet. The moon and planet section seems to be based around a planet with a single moon. What if one wants to do a planet with more than one moon? Would they just add the combined mass of the moons?

  7. Metaraptor:
    No, you should do each moon individually. You should be able to duplicate that page if you want-- or just write the info down somewhere else, and then put in the info for your next moon.

    Each moon should be at a different distance from the planet.

    Multiple moons would be more or less unaffected by each other. Technically they would tug on each other a little, but the difference would be negligible.

    Multiple moons would have a cumulative effect on the tides-- in rather complicated ways. In short when moons are lined up, or on opposite sides of the planet their pull would create especially high tides.

  8. A superb program.
    *downloading a copy to use* thank you for making and sharing this.

    have nice days and be well.

  9. I'm concerned about the temperature calculation you have implemented. For a blackbody, the basic equation is T = 65 * (W^0.25), where T is in Kelvins, W is expressed in watts per square meter, and 65 is just a scaling constant to end up in units of kelvins. Planets, of course, are a little more complicated than ideal blackbodies, but they're not impossible to tackle.

    In cell D48, you take the fourth root of the insolation before subtracting the albedo correction. The order of mathematical operations is incorrect. The albedo correction must be applied to get the net wattage before taking the fourth root. The constant 374 doesn't make much sense- this is clearly just the wattage at which a blackbody radiates into space at 288 K (i.e., Earth's average temperature), and it does not belong in the equation. You need to go back to first principles to understand why this shortcut won't work.

    The full equation should look like:

    T = 65 * (1+G) * ( 340*L/(R^2) * (1-A) )^0.25

    where G is the greenhouse increment, L is the star's luminosity relative to Sol, R is the planet's semi-major axis in AU, A is the planetary albedo, and 340 is the solar constant averaged over the surface of a rotating sphere (i.e., 1360 W/sq m divided by 4, since spheres have four times the surface area of the circle of starlight that they're intercepting in space). The calculated temperature will be in kelvins.

    The main point is that a body's temperature changes as the fourth root of the effective rate at which the body receives energy; all energy terms in the equation must fall under the radical. It should be clear from this why trying to use Earth's effective surface wattage as a fudge factor to scale the temperature of another planet with a different effective surface wattage cannot give you the correct mathematical answer.

  10. Thanks for the clear error report Orion. I'll try to fix that. I don't remember exactly which source(s) i used, i think i ended up adapting something on the edge of my understanding.

    My math is weak enough that the only way i catch equation errors is by comparing the result to a known planet-- but temperature on a real planet is so complex that the numbers wouldn't match anyway.

  11. Well, you need to start somewhere to get a ballpark figure for a planet’s temperature. Insolation, albedo, and a stab at the greenhouse factor are the simplest model you can do. A more detailed model will give the user control over the fraction of the albedo due to oceans, forest, ice, fields, deserts and overall cloud cover. It sounds daunting but it’s really very doable in a spreadsheet. You can see, for example, how the albedo goes down as the oceans get bigger- water absorbs most of the light hitting it. Bare rock, on the other hand, may reflect 20% or more of the light back into space. You multiply the fraction of the planetary surface that is terrain type X by the albedo for that terrain type, and then sum all the terrain types to arrive at the total albedo.

    Technically the greenhouse factor belongs under the radical as well (as a scale factor for the effective wattage rather than the effective temperature- note that you must take the fourth power of the greenhouse effect if you move it under the radical with the other energy terms), but you can get away with it as a legitimate fudge factor as written above if you are careful and understand what it means. For Earth, the greenhouse factor is 1.1294- the ratio between the observed mean surface temperature 288 K and the radiative equilibrium temperature of 255 K. In other words, it’s an empirical observation about how much various climate feedbacks raise the temperature by trapping outgoing IR radiation and reflecting it back to the surface.

    On Earth, all these factors combine to raise the temperature by 13% (or to raise the effective surface flux by 62.7%, if you prefer). One presumes that human-habitable planets will have greenhouse factors that range from about 10% to 15%- outside of this range you get into atmospheric pressures too low to breathe on one end, and carbon dioxide levels too high to breathe on the other. I might push those boundaries to 5%-20% under special conditions, but no further than that.

    Runaway greenhouses will raise the effective surface temperature by a factor of e, the base of natural logarithms (2.7182818…), but no more than this. See Venus for an example.

  12. I had originally planned to make the spreadsheet calculate the albedo based on user input, but ran into some problems.

    If you would like to collaborate on this chart as a co-author, contact me by email (accessable in my blogger profile)

  13. Fantastic resource! I know it's several years later, but before I do the conversion myself, has anyone moved this to Google Sheets?