Now that you have Excel, you’re going to need to use it. If you have no idea how, see the document “Intro to Excel” on the course webpage.
You have three major goals in this exercise. First, you’re going to convert the numbers in a data table from one unit to another. Second, you’re going to make a plot of your converted numbers. Third, you’ll use your plot to interpolate between the data points; that is, use the numbers that are there to figure out one that isn’t.
- A person buys five different quantities of apples on five different days. Each time they pay a slightly different price per pound. The pounds they bought and the price in dollars are listed in this table. Enter these data into a blank Excel spreadsheet. Each data piece should be in a separate cell, just like the table below. Give each of your columns a title (again, like the table).
When you enter numerical data, just enter the numbers and no units. This will let you perform calculations on them later.
- Convert each number in pounds into kilograms. Assume there are 2.20 pounds in a kilogram (2.20 pound/kg). You can do this quickly and easily for all your rows if you follow the instructions for using Excel on the course Blackboard page.
- Convert the prices in U.S. Dollars to Euros. Assume 1 U.S. Dollar equals 0.9010 Euros (0.9010 Euro/dollar).
- If you divide the price paid in Euros by the mass of apples bought, you’ll arrive at the price paid in Euros per kilogram (€/kg). Do this for all the data rows.
- Use Excel’s AVERAGE function to average the Euros per kilogram numbers you just calculated. Do this in a cell below the cells where you calculated €/kg.
- Use Excel’s standard deviation function, STDEV.S, to calculate the standard deviation of the price per kilogram figures you just averaged. You can put this number right below your average.
- Highlight all the cells you have numbers in and copy them into a Word document.
- Construct a scatter chart (plot) of your price in Euros and masses in kilograms numbers (not the price per kilogram numbers). Give your chart a title and enter titles for the x and y axes.
- Add a linear trendline to the chart you just created. Set the equation for this line to appear on your chart.
- Copy the chart into your Word document below the cells you pasted earlier. Size your graph so it fits into the document.
- Use your plot to determine how many kilograms of apples you could buy for 5.00 Euros.
- Save your Word file as a PDF for submission to Canvas. More instructions for this are on the course Canvas page.
Each version of Excel is slightly different, so make sure you can navigate the version you have available to you at home or in computer labs. Excel can come in handy in other classes, especially if you continue on to Chem 106.