Firebrush Array Reflection

Firebrush Array was a great team to work with. I was very proud of the Dough in the Wall website. The time constraint was pretty difficult on top of other classes and work, but we managed to pull it together.

I was glad to work with a few different new things, such as flex box, media queries, and php.

Although I had played with php for WordPress theming, I was not prepared for setting up the php connection to the database and interacting with the form. Even though, we didn’t link the database to the form for the final presentation, I spent a lot of time figuring it out.

I was really worried at first, trying to figure out how the form would have to be with the php so that it would work well with a database. In the end, I was very happy with our solution. I think we could have made it production quality after addressing security concerns to try to prevent injection into the form and database.

I really strengthened my skills with css and databases by helping others in our team and in other teams. I think learned the most by looking at the issues other people were having and talking them through different solutions.

As a group we didn’t really have any conflicts or power struggles. I found our positions and work divisions to be very balanced playing off our individual strengths.

Food was often considered. I can’t work on an empty stomach. I got food a number of times with members of my group both before and after class. I would say that it was definitely the most important part of this assignment.

Individual Spreadsheet 2: Federation Planets: Remix

 

Introduction

My data set was mocked up using Mockaroo mimicking my original spreadsheet assignment with the addition of land, water, and metal resources; as well as X and Y coordinates. I changed the Planet’s names from hex values to a custom formula that appends letters from Lorem ipsum to last names.

[:last_name:]\w{3,5}

My goal for this assignment was to go a step beyond determining whether a planet is inhabitable. I wanted to show the locations of habitable planets as well as useful resources of the nearest planets in order to find out which would be the most logical to explore for an upcoming mission.

Formulas

My first formula is a query that selects all the initial data using a named range, ALL, and filters out uninhabitable planets using “where” to only show planets that have eccentricity less than 0.05, temperature between 15 and 115 Celsius and a magnetic field. I then ordered the results alphabetically using order by on column A.
=QUERY(ALL, “select A,H,I where B<0.05 and C<115 and C>15 and D=TRUE order by A”)

 

My second formula is similar to the first except that it also filters out planets that are further than 3000 light years from the origin. Also, I selected all that data except for column D to have a small table of the data for the nearest planets.
=QUERY(ALL, “select A,B,C,E,F,G,H,I where B<0.05 and C<115 and C>15 and D=TRUE and H>-3000 and H<3000 and I>-3000 and I<3000 order by A asc”)

Additionally, I counted the number of habitable planets using the COUNTA formula
=COUNTA(A3:A)

I also counted the number of planets within 3000 light years by counting the values in the table from the second query.
=COUNTA(E3:E)

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.

I used on color scales on columns E through I in the Data sheet. The darkest colors in the scale represented percentages close to 1 or coordinates close to the origin. I used conditional formatting on columns B, C, and D to show data that indicated habitable variables in green. Data that indicated uninhabitable variables were written in red text.

Charts

I used the data from my first query to show all habitable planets on a coordinate grid in its own sheet to function as a two-dimensional map derived from a bubble chart.

On the Dashboard sheet, I created pie charts to show the percentage of resources for each of the nearest planets. To make headers for the charts I used a formula to get the contents from the cells from my table and concatenated the word “resources” to it.
=Concatenate(CELL(“contents”,Summary!E3),” Resources”)
Even though in some cases the percentages of the combined resources totaled more than 1.0, I knew the chart function would correct percentages to reflect the total of resources as 100%.

For each of the nearest planets, I also created temperature gauges.

I created a radar chart to show the eccentricities of the nearest planets. Even though the chart wasn’t intended for this purpose I think it creates a quick visual that can be easily interpreted.

I created a mini map of only the nearest planets using a bubble chart to show the planets on a coordinate grid.

Using the Dashboard

I wanted the dashboard to answer questions at a glance for the nearest planets. You can quickly see that planets Effertzfed and Predovicnip have little landmass and Hansenyou’s has little water. Also you can see that Hansenyou’s, Kundeson, Millerself and Predovicnip are extremely hot. Bruentubed has a high orbital eccentricity and may be slightly too volatile. You can also see that Kundeson is very close to the origin.

If a commander were to choose which planet was the most important to explore they may choose:
Kundeson for it’s location
Bruentubed for wealth of resources given eccentricity isn’t too much
Effertzfed for its large amounts of water and metal which could make a nice industrial planet

Future Implications

Even though real maps often show three-dimensional space on a two dimensional grid. I think a three dimensional map would be more appropriate for a celestial map. If I could find a chart that could process three dimensions, I would simply need to generate a z-coordinate. Also, I would find a way to ensure that all three resources totaled to 1.0 even though it didn’t really affect my data on this assignment.

Fin.

Individual Spreadsheet: Federation Planets

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.

Fin.