Excel Spreadsheet

Part I: Sorting and Graphing

1. Determinethe sorts for Sorts #1, #2, and #3. You will need to use the sort function in excel to explore how a data set based on paint names and property values produced three different sorts: Sort #1, Sort #2, and Sort #3. To do this, select the entire body of data from B7 to H23 then click on Sort and Filter up on the top Ribbon, then click on Custom Sort. Here, as long as the “My Data has Headers” box is checked, you’ll be able to select columns to sort and then a parameter to sort them by (alphabetically, numerically, largest to smallest, A to z, etc). By clicking “Add Level” you can introduce multiple sorts at once where it will do the first sort and then continue to sort the data according to the second sort. This function is a bit trickier so take your time to explore it and discover how it is changing your data. As you discover each sort, type it into a new cell off to the right, for example from the weekly video: “Sort #2 is sorted alphabetically from A to Z of the Paint Name.”

2. GraphColor ID vs. Sorts #1 – #3. Stateany correlations that you find. All graphs, unless specified otherwise, should be scatter plots without any connecting lines. Graphelements that must be included are axis titles (including units if applicable), graph title, adjusted axis values (the data should take up the majority of the graph space), a legend, and clearly distinct marker colors. Make sure to watch the video for examples. If any correlation is seen between Color ID and any of the sorts, state that correlation in a cell near the graph. You have freedom to organize your excel as you see fit as long as it is easy to read!

3. GraphBrightness vs. Sorts #1 – #3. Stateany correlations that you find.

4.  GraphSheen vs. Sorts #1 – #3. State any correlations that you find.

Part II: Graphing and Formatting Equations and Trendlines

1. Graph the activation energy vs temperature data. Again, for credit, make sure that the graph is made properly and all graph elements are present and organized well.

2. Determinethe best trendline for the data (equation and R2 value shown on graph). Right click on your graph data and then click on “Add Trendline.” Check the boxes on Display Equation on chart” and “Display R-squared Value on chart.” Experiment with the different types of trendlines available and different orders of polynomial to determine the best fit trendline. Again, the video walks through this process one step at a time with hints.

3. Createa calculated activation energy column. Once you’ve determined the best fit for the data, you’re going to recalculate the data in a new column using the equation from your trendline. Remember that in the equation “y” is your activation energy and “x” is your temperature in Kelvin (which is a cell you’ll reference). You’ll want to be thinking about graphs in this sense throughout this semester since it will help bridge the gap between a math equation and a real life application. For example, the trendline that I got in the video is more accurately thought of as: EA (mJ) = -3.0000×10-3T4 + 1.1000×10-1T3 + 5.0000×10-1T2 + 1.0004T – 5.2828. Make sure that you label your calculated column correctly and always include units.

4.  Createa difference column between actual activation energy and calculated. Create a new column and set it to equal the actual activation energy data minus the calculated data for each temperature. It’s ok if this value is zero!