Introduction
My data set was mocked up using Mockaroo to imitate Nasa’s spreadsheet of planets from our solar system. I decided to go with a Trekkie vibe to make sense of why someone would want data from a thousand planets. I decided my spreadsheet would hold data and calculations of planets allied with or owned by the Federation. Ultimately, the spreadsheet holds information that might be useful if a mission were to go to one of the planets and answers the question of whether or not the planet has the potential for habitable life.
Formulas
My initial three formulas were simple mathematical equations to fill in some of the planet’s properties that could be calculated from the previously generated columns. To calculate the radius of the planet, I used the equation for escape velocity:
to solve for r. My final formula was as follows:
=((F2^2)*((1)/(2*E2*B2)))
where F2 is escape velocity, E2 is gravity, and B2 is mass.
Similarly, I found the semi-major, and minor axis using the following formulas:
P=a(1-e) A=a(1+e)
My final formulas were as follows:
=(J2/(1–O2)) =(K2/(1+O2))
where J2 is the perihelion, K2 is the aphelion, and O2 is the eccentricity.
I then calculated whether each planet would be habitable using a nested if statement to make sure it had the following factors: a magnetic field, a mean temperature between 15 and 115 degrees Celsius, and eccentricity less than or equal to 0.05. By having the opposite of the ideal condition, the if statement will exit at the first non-ideal condition returning the value “Uninhabitable.” If the if statement reaches the final else, then that means the wrong conditions were not met and in turn the planet is “habitable.” The formula is as follows:
=IF(EXACT(U2, “FALSE”), “Uninhabitable”, IF(Q2>115, “Uninhabitable”, IF(Q2<15, “Uninhabitable”, IF(O2>0.05, “Uninhabitable”, “Habitable”))))
where U2 is magnetic field, Q2 is mean temperature, and O2 is eccentricity.
The EXACT equation matches the string value in U2, either “TRUE” or “FALSE”, to the exact string “FALSE” to determine if that planet does not have a magnetic field.
My next formula returns a string that states whether a planet is habitable or uninhabitable in a full sentence for easy reference or querying. The formula is as follows:
=PROPER(CONCATENATE(“Planet”,” “, RIGHT(A2,LEN(A2)–1), ” “, “is”,” “, Y2))
where A2 is the planet’s name or hex value and Y2 is the previous formula’s value, either “Habitable” or “Uninhabitable.” The PROPER formula capitalizes the first letter of each word in the final string. CONCATENATE adds the strings: “planet” and “is” to the value in A2 and Y2 with spacing to result in a sentence, such as, “Planet 672Cb0 is Uninhabitable.” the RIGHT formula is used to remove the octothorpe of the hex value to make the sentence read better. It does so by counting from the right of the string in A2 the length (LEN) of the of the value in A2 minus 1 and returning that many characters from the right side.
The summary tab makes use of the COUNT formulas. A basic COUNTIF counts the number of habitable planets by determining if the value in Y is equal to “Habitable.” This looks like: =COUNTIF(DATA!Y2:Y, “Habitable”)
The same is done for the value, “Uninhabitable”: =COUNTIF(DATA!Y2:Y, “Uninhabitable”)
The COUNTIFS formula is used to count the number of planets that meet multiple criterion, in this case: planets with rings, moons, and a magnetic field, like so:
=COUNTIFS(DATA!T2:T, “TRUE”, DATA!U2:U, “TRUE”, DATA!S2:S, “>0”)
To be counted, each row must have a value of “TRUE” in the ring and magnetic field column and must have a number greater than 0 the column with the number of moons.
COUNTA is used to count the number of rows in column A after the heading row as such:
=COUNTA(DATA!A2:A)
This tells me how many rows of data or how many many planets there are.
The MAX formula shows the max value in column B (Mass). CONCATENATE is used to add the unit “*10^24 kg” to that value. The formula is as follows:
=CONCATENATE(MAX(DATA!B2:B), ” “, “*”, ” “, “10^24kg”)
The adjacent cell takes the previous formula and wraps it in a LOOKUP formula to look up the name of the planet that corresponds to that value. This looks like:
=LOOKUP(MAX(DATA!B2:B), DATA!B2:B, DATA!A2:A)
LOOKUP requires the mass to column to be sorted.
My final formula creates a list of habitable planets. It does so by filtering the values in Y(Habitable) that match the exact string “Habitable.” It then returns every unique value in column A that satisfies that filter. The formula is as follows:
=UNIQUE(FILTER(DATA!A2:A,EXACT(DATA!Y2:Y,“Habitable”)))
Formatting
To make the data more readable I used alternating colors on the entire DATA sheet and the rows of data in the SUMMARY sheet to establish the colors of headings and alternate soft colors on the following rows.
A conditional rule on the entire sheet makes the background of empty cells dark gray.
Column I tells how close planets are to the sun. A color scale make planets close to sun, lower values, more red, and planets further away, higher values, more white.
Column Q contains temperature values. A color scale makes the background of these value a certain color. Hotter temperatures appear more red and colder ones appear more blue. A midpoint is set to 20. This means temperatures close to 20 degrees celsius appear a neutral white-ish green color.
Formatting on Column Z makes planets that are habitable stand out by declaring the background of the cell solid green and the text white if the string does not contain the value “Un.” This essentially filters out all values of “Uninhabitable.” If it contains the value “Un” the text color is set to red.
Zachary Randolph 