ScienceCapstone Exercises

You have a busy semester with your science classes, and all of them expect you to be able to use Excel to report your findings, perform calculations, and create visual representations of the data. You recorded the results of your chemistry experiment in your lab notebook, and now it is time to enter the data into Excel. When you are done entering the data, you will need to create formulas and generate some graphs. For your biology course, you conducted an experiment, and created an Excel workbook to record your results. You want to use the table feature, to filter and sort the data to analyze the results. You also need to create a chart to visualize one of the categories of your experiment.

Enter and Format the Text Labels

You are ready to create the first Excel worksheet. You have planned the structure of your worksheet, and decide to enter all of the labels first and do some basic formatting before you enter the values and create formulas. You make sure that you have placed the data, in this case, volume before mass, to make the creation of charts easier.

a)    Create a new workbook, and then save it as 05e_DensityLab_LastFirst.

b)   Type the worksheet title Density Determination of Unknown Metal Sample in cell A1.

c)    Merge and center the range A1:G1. Middle Align the merged cell.

d)   Apply bold and 16 pt font.

e)    Select row 1 and change the row height to 35.

f)    Rename the sheet Data

g)   Type the following labels:

·     Sample ID in cell A3

·     Volume (mL) in cell B3

·     Mass (g) in cell C3

·     Density (g/mL) in cell D3

·     Average Density in cell A12

·     Standard Deviation in cell A13

·     Identity of Sample in cell A14

h)   Wrap the text in the range A3:D3, apply bold, change the fill color to Blue, Accent 1, Lighter 60% and center align the contents.

i)     Select column A and format it using AutoFit Column Width.

Enter the Lab Data

Using the data recorded in your lab notebook, you are ready to enter your results. You were given six metal samples of varying mass, and using the volume displacement method, you need to determine what kind of metal the sample is. You have already calculated the volume of each sample (subtracting the initial water volume from the final water volume).

 

a)    Enter the data into your Data worksheet as shown below:

b)   Format the range B4:C9 with the Number format, with 2 decimals.

c)    Select the range A4:A9 and center the Sample IDs.

Use Formulas and Functions

Now that you have designed your worksheet and entered the data, you are ready to create a formula to determine the density of each sample. You also need to calculate the average density and standard deviations.

a)    In cell D4, create a formula to determine the density of each sample.

 Density = Mass/Volume.

b)   Copy the formula for the rest of the samples.

c)    Enter a function in cell C12 to calculate the average density of all the samples.

d)   In cell C13, create a formula to calculate the standard deviation (STDEVA) for the range D4:D9.

e)    Format the range D4:D9 and C12:C13 with the Number format.

Use a Lookup Function

You also need to make sure your worksheet contains information for the true density (accepted density) of known metal substances so you can identify your sample. Your instructor has given the list of the class samples and the true density from your chemistry textbook. You will use a lookup function to assign the substance name that corresponds with the average density result.

a)    Create a lookup table starting in cell A16, using the data shown below. Apply the Number format, with 2 decimals to the values. Add a bottom border to the cell range A16:B16.

Accepted Density

Substance

11.35

Lead

7.87

Iron

10.49

Silver

2.70

Aluminum

8.90

Nickel

0

Invalid Result

b)   Select Column B and change the column width to 12.75.

c)    Select the range A17:B21 and sort the table Smallest to Largest.

d)   Assign a range name Identity to the lookup table.

e)    Create a VLOOKUP function in cell C14 that returns the name of the substance based on the Average Density. Use the range name you just created for the table array.

 

Create the X, Y (Scatter) Chart

A useful way to view and present the data is with a graph, which provides a visual summary of the experiment. You will need to show the linear relationship for the densities of each sample and add a trendline to show linear regression.

a)    Select the values only, for the volume and mass of each sample (range B4:C9).

b)   Click the INSERT tab and click Insert Scatter (X, Y) or Bubble Chart, and choose Scatter.

c)    Move the chart below the lookup table to span the range A24:I41.

d)   Select the Chart Title placeholder and type Density of Aluminum. Apply bold and 20 pt. font to the title.

e)    Add Axis Titles: Volume (mL) to the Primary Horizontal axis, and Mass (g) to the Primary Vertical axis. Apply bold and 12 pt font to the labels.

f)    Format the horizontal axis, so that the minimum bound is 2.0 since all volumes start after this point.

g)   Add a linear trendline and choose the options to Set Intercept and Display Equation on chart.

h)   Create a footer on the worksheet with your name, the sheet name code and the file name code.

i)     Scale the worksheet to fit on one page. Print the worksheet out.

j)     Save and close the workbook.

Create and Format a Table

Your biology experiment on yeast fermentation under a variety of conditions has been completed. You have created a worksheet for the all of the fermentation data, and one for just the results of one the substances. You want to work with the data, using Excel’s table features, but also may need to work with your original worksheet, so you decide to copy the worksheet first, and then convert the data to a table.

a)    Open the 05e_FermentationLab workbook and save it as 05e_FermentationLab_LastFirst.

b)   Select the Fermentation Data worksheet and copy it to a new worksheet. Rename the duplicate worksheet Fermentation Data Table. Change the tab color of the new sheet to Blue.

c)    Click any cell within the data range A4:P25, and convert the data to a table.

d)   Apply Table Style Medium 6 table style.

e)    Delete Column C in the table, since 0 minutes is not needed for the analysis.

f)    Merge and Center the title over the range A1:P1, and change the font size to 16 pt., and then apply Blue, Accent 5 fill color.

Filter and Sort the Table Data, and Use a Structured Reference

As you look over your results, you would like to try to analyze it. You decide that you should sort the data alphabetically by the substance, and then the concentration of each substance. Filtering the results for the substances where you had significant amounts of fermentation will also aid you in deciding what were good substrates for yeast.

a)    Create a custom sort on the Fermentation Data Table worksheet, in the data table, where the first level sort is by substance in A-Z order, and a second level by concentration from smallest to largest order.

b)   Filter the data to display only the following substances: Ethanol, NaCl and Sucrose.

c)    Add a new field to the right of the 65 minute field. Rename the new field Good for Fermentation, then apply Wrap Text to the cell, and change the column width to 14.50.

d)   Create a formula with a structured reference, using the IF function, that determines if the amount of CO²production at 65 minutes is greater than or equal to 2.0, it will display the word Yes, otherwise it will display the word No.

Apply Conditional Formatting

To highlight the values at 15 minute intervals, you decide to apply conditional formatting to illustrate the differences.

a)    Apply the Orange Data Bar conditional formatting in the Gradient Fill section to the 30 minute interval.

b)   Apply the Green Data Bar conditional formatting in the Gradient Fill section to the 45 minute interval.

c)    Apply the Highlight Cells Rules, Greater Than conditional formatting to the 60 minute interval, where values greater than 1.5 are formatted with Light Red Fill with Dark Red Text.

d)   Create a new conditional format that applies bold red font where the values equal Yes in the Good for Fermentation column.

e)    Select the Good for Fermentation column and center the data.

f)    Select and center all of the column headings.

Create a Line Chart

The results for salt (NaCl) are interesting, in that you would not have expected salt to be a good choice for yeast fermentation. You know that salt is added to bread dough to stimulate the yeast’s ability to raise the dough. At differing concentrations, it appears that it does make a difference in the amount of CO2 created, as indicated by the rise in volume in the pipette. You have isolated the results for NaCl, and decide to create a line chart to visualize the changes over time.

a)    Select the range A5:N9 on the NaCl worksheet. Create a line chart.

b)   Move the chart to its own sheet named Line Chart.

c)    Apply the chart Layout 4 from the Quick Layout gallery.

d)   Insert the chart title: NaCl Concentration Evaluation.

e)    Add Axis titles: Minutes on the Primary Horizontal axis; Volume (mL) on the Primary Vertical axis.

f)    Move the legend to the right.

Finalize the Workbook

You want to prepare the fermentation lab workbook in case someone wants to print any of the worksheets. To ensure the worksheets print only on 1 page, you need to adjust the page setup options.

a)    Create a footer on each worksheet with your name, the sheet name code and the file name code.

b)   Apply landscape orientation to the NaCl worksheet. Change the scaling so that all the columns fit on one page, and the gridlines will print.

c)    Apply landscape orientation to the Fermentation Data Table worksheet. Change the scaling so the sheet fits on one page.

g)   Save and close the workbook.

Upload both files to the drop box for the Final Exam.

Attachments:

• 1512985730-05e_FermentationLab.xlsx