1.2: Lab - Learning to Use MS Excel
- Page ID
- 438404
\( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)
\( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)
\( \newcommand{\id}{\mathrm{id}}\) \( \newcommand{\Span}{\mathrm{span}}\)
( \newcommand{\kernel}{\mathrm{null}\,}\) \( \newcommand{\range}{\mathrm{range}\,}\)
\( \newcommand{\RealPart}{\mathrm{Re}}\) \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\)
\( \newcommand{\Argument}{\mathrm{Arg}}\) \( \newcommand{\norm}[1]{\| #1 \|}\)
\( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\)
\( \newcommand{\Span}{\mathrm{span}}\)
\( \newcommand{\id}{\mathrm{id}}\)
\( \newcommand{\Span}{\mathrm{span}}\)
\( \newcommand{\kernel}{\mathrm{null}\,}\)
\( \newcommand{\range}{\mathrm{range}\,}\)
\( \newcommand{\RealPart}{\mathrm{Re}}\)
\( \newcommand{\ImaginaryPart}{\mathrm{Im}}\)
\( \newcommand{\Argument}{\mathrm{Arg}}\)
\( \newcommand{\norm}[1]{\| #1 \|}\)
\( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\)
\( \newcommand{\Span}{\mathrm{span}}\) \( \newcommand{\AA}{\unicode[.8,0]{x212B}}\)
\( \newcommand{\vectorA}[1]{\vec{#1}} % arrow\)
\( \newcommand{\vectorAt}[1]{\vec{\text{#1}}} % arrow\)
\( \newcommand{\vectorB}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)
\( \newcommand{\vectorC}[1]{\textbf{#1}} \)
\( \newcommand{\vectorD}[1]{\overrightarrow{#1}} \)
\( \newcommand{\vectorDt}[1]{\overrightarrow{\text{#1}}} \)
\( \newcommand{\vectE}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash{\mathbf {#1}}}} \)
\( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)
\( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)
\(\newcommand{\avec}{\mathbf a}\) \(\newcommand{\bvec}{\mathbf b}\) \(\newcommand{\cvec}{\mathbf c}\) \(\newcommand{\dvec}{\mathbf d}\) \(\newcommand{\dtil}{\widetilde{\mathbf d}}\) \(\newcommand{\evec}{\mathbf e}\) \(\newcommand{\fvec}{\mathbf f}\) \(\newcommand{\nvec}{\mathbf n}\) \(\newcommand{\pvec}{\mathbf p}\) \(\newcommand{\qvec}{\mathbf q}\) \(\newcommand{\svec}{\mathbf s}\) \(\newcommand{\tvec}{\mathbf t}\) \(\newcommand{\uvec}{\mathbf u}\) \(\newcommand{\vvec}{\mathbf v}\) \(\newcommand{\wvec}{\mathbf w}\) \(\newcommand{\xvec}{\mathbf x}\) \(\newcommand{\yvec}{\mathbf y}\) \(\newcommand{\zvec}{\mathbf z}\) \(\newcommand{\rvec}{\mathbf r}\) \(\newcommand{\mvec}{\mathbf m}\) \(\newcommand{\zerovec}{\mathbf 0}\) \(\newcommand{\onevec}{\mathbf 1}\) \(\newcommand{\real}{\mathbb R}\) \(\newcommand{\twovec}[2]{\left[\begin{array}{r}#1 \\ #2 \end{array}\right]}\) \(\newcommand{\ctwovec}[2]{\left[\begin{array}{c}#1 \\ #2 \end{array}\right]}\) \(\newcommand{\threevec}[3]{\left[\begin{array}{r}#1 \\ #2 \\ #3 \end{array}\right]}\) \(\newcommand{\cthreevec}[3]{\left[\begin{array}{c}#1 \\ #2 \\ #3 \end{array}\right]}\) \(\newcommand{\fourvec}[4]{\left[\begin{array}{r}#1 \\ #2 \\ #3 \\ #4 \end{array}\right]}\) \(\newcommand{\cfourvec}[4]{\left[\begin{array}{c}#1 \\ #2 \\ #3 \\ #4 \end{array}\right]}\) \(\newcommand{\fivevec}[5]{\left[\begin{array}{r}#1 \\ #2 \\ #3 \\ #4 \\ #5 \\ \end{array}\right]}\) \(\newcommand{\cfivevec}[5]{\left[\begin{array}{c}#1 \\ #2 \\ #3 \\ #4 \\ #5 \\ \end{array}\right]}\) \(\newcommand{\mattwo}[4]{\left[\begin{array}{rr}#1 \amp #2 \\ #3 \amp #4 \\ \end{array}\right]}\) \(\newcommand{\laspan}[1]{\text{Span}\{#1\}}\) \(\newcommand{\bcal}{\cal B}\) \(\newcommand{\ccal}{\cal C}\) \(\newcommand{\scal}{\cal S}\) \(\newcommand{\wcal}{\cal W}\) \(\newcommand{\ecal}{\cal E}\) \(\newcommand{\coords}[2]{\left\{#1\right\}_{#2}}\) \(\newcommand{\gray}[1]{\color{gray}{#1}}\) \(\newcommand{\lgray}[1]{\color{lightgray}{#1}}\) \(\newcommand{\rank}{\operatorname{rank}}\) \(\newcommand{\row}{\text{Row}}\) \(\newcommand{\col}{\text{Col}}\) \(\renewcommand{\row}{\text{Row}}\) \(\newcommand{\nul}{\text{Nul}}\) \(\newcommand{\var}{\text{Var}}\) \(\newcommand{\corr}{\text{corr}}\) \(\newcommand{\len}[1]{\left|#1\right|}\) \(\newcommand{\bbar}{\overline{\bvec}}\) \(\newcommand{\bhat}{\widehat{\bvec}}\) \(\newcommand{\bperp}{\bvec^\perp}\) \(\newcommand{\xhat}{\widehat{\xvec}}\) \(\newcommand{\vhat}{\widehat{\vvec}}\) \(\newcommand{\uhat}{\widehat{\uvec}}\) \(\newcommand{\what}{\widehat{\wvec}}\) \(\newcommand{\Sighat}{\widehat{\Sigma}}\) \(\newcommand{\lt}{<}\) \(\newcommand{\gt}{>}\) \(\newcommand{\amp}{&}\) \(\definecolor{fillinmathshade}{gray}{0.9}\)- Computers with MS Office installed
- Learn basic Excel functions and create an XY scatter plot in Excel.
- Evaluate data using linear regression analysis.
- Plot multiple data sets in a single graph.
- Correctly implement intermediate Excel functions and graphing.
- Demonstrate how to use pre-defined Excel functions, and create custom functions, for calculations.
Laboratory Skill
- Gain proficiency in graphing with Excel and learning basic functions.
- Create graphs that can be used as templates for subsequent labs in the course.
- Practice using graphical data to solve problems.
Equipment and Materials
- Computer with MS Office installed
Safety and Hazard Information
N/A
Background Information
If you have never used MS Excel before, it is a useful tool that has many applications. For example, you can use it to calculate your grades, keep track of your monthly spending, calculate averages, create invoices, etc. It is also a very important tool in the sciences for storing and analyzing data. This is because data can be fit to an equation and used to determine unknown values or predict future outcomes.
For this lab, you are going to perform a series of exercises designed to help you become more familiar with MS Excel and how it can be used for data analysis. After completing these exercises, you will also have useful templates that can be used for graphing data in subsequent chemistry labs, so make sure you save all of your work!
If you need additional assistance take a look at this YouTube tutorial:
When creating XY plots for exercises 1 and 2, X represents the independent variable on the horizontal axis at the bottom of the graph. Y represents the dependent variable on the vertical axis to the left side of the graph. These plots then will consist of ordered pairs of data that are related to each other in some way, and this relationship can be fit to a descriptive mathematical equation.
When you are asked to plot some value against another, for example, Mass vs. Volume, this is read as “\(y\)-axis variable vs. \(x\)-axis variable”. So for Mass vs. Volume, Mass would be plotted along the \(y\)-axis, and Volume would be plotted along the \(x\)-axis.
Special Instructions (if any)
N/A
Procedure
Exercise \(\PageIndex{1}\) – Creating XY Scatter Plot and Linear Regression Analysis
Not long after you move into an old house, you find a small wooden box in the attic full of what appears to be different sized solid pieces that appear to be gold. Did you just discover a fabulous treasure, or not? After running a quick Google search, you discover that gold and fool’s gold (pyrite) look very similar, so how can you determine the identity of the material in the box?
Fool's Gold
Real GoldThere is a simple experiment that you could conduct to help identify the type of metal, based on density.
The density of any material is equal to the mass of the sample divided by the volume of the sample, and every substance has a unique density value.
\[\begin{align} & D = m/v \\ & D = \text{ Density} \notag\\ & m = \text{ mass (in g)} \notag\\ & v = \text{Volume (in mL or cm}^3)\notag \end{align}\]
Density is an intensive property, i.e. a property that is the same regardless of amount of material. So, if you were to measure the mass and volume of several pieces of your “gold”, you could then calculate a density for the material, and from that, determine whether the metal is real gold or pyrite.
The density values for the two materials are:
Gold: 19.3 g/cm\(^3\)
Pyrite: 4.8 g/cm\(^3\)
To conduct the experiment, you collect 7 different sized pieces and weigh each piece. You also determine the volume of each piece using a displacement method that will be discussed in Lab \(\PageIndex{1}\). Briefly, this method involves taking a known amount of water, plunging an object in the water and observing the differential volume of the object (i.e. the displaced volume). After measuring the volume in mL, you can convert those values to cm\(^3\), using the conversion factor 1 mL = 1 cm\(^3\).
After completing your measurements, you have collected the following data:
Table \(\PageIndex{1}\): Mass vs. Volume Data for Exercise \(\PageIndex{1}\)
| X | Y |
|---|---|
| Vol (cm\(^3\)) | Mass (g) |
| 1.26 | 6.210 |
| 1.51 | 7.243 |
| 2.00 | 9.778 |
| 2.37 | 11.431 |
| 2.48 | 11.879 |
| 2.68 | 12.963 |
| 3.10 | 15.005 |
To calculate the density for the objects measured, you are going to conduct a linear regression analysis using Excel. Linear regression analysis for this experiment is just a fancy way of saying that you are going to plot the data points as a scatter plot on a 2D graph, and determine if you can draw a straight line that connects them together.
Procedure
a. Open a new spreadsheet in Excel. The cells in the spreadsheet are organized vertically by lettered columns and horizontally by numbered rows.
b. In the spreadsheet, type (beginning at cell A1) in all of the information that is provided above in Table \(\PageIndex{1}\), including the header information for each column. When completed, your Excel data should look exactly like Table \(\PageIndex{1}\), with mass data under Y, and volume data under X.
c. Also, in cell D1, type in the words Density Data.
d. Now that you have your data, what can you do with it? What you have created is a 2D coordinate system. Each volume-mass pair represents a point in 2D space, so now you are going to create a graph for these data points.
e. Click here (or see below) for a video tutorial on how to create an XY Scatterplot in Excel.
f. Once you have completed your graph, save your Excel file and complete the related questions in the lab report.
Exercise \(\PageIndex{2}\) – Plotting Multiple Data Sets in a Single Graph
a. Create a new sheet in Excel and name it Exercise \(\PageIndex{2}\).
b. Create a new table on the Exercise \(\PageIndex{2}\) worksheet and type in the following information:
Table \(\PageIndex{2}\): Pressure vs Volume data at different temperatures
| X | T = 0\(^{\circ}\)C | T = 25\(^{\circ}\)C | T = 50\(^{\circ}\)C |
|---|---|---|---|
| Vol (L) | P (atm) | P (atm) | P (atm) |
| 1.00 | 22.41 | 24.47 | 26.52 |
| 1.50 | 14.94 | 16.31 | 17.68 |
| 2.00 | 11.21 | 12.23 | 13.26 |
| 2.50 | 8.97 | 9.79 | 10.61 |
| 3.00 | 7.47 | 8.16 | 8.84 |
| 3.50 | 6.40 | 6.99 | 7.58 |
| 4.00 | 5.60 | 6.12 | 6.63 |
c. Since this table has 3 sets of Y values (1 for each of the Temperature (T) values 0, 25, and 50 degrees C) you are going to plot 3 sets of data in the same graph.
d. Click here (or see below) for a video tutorial on how to plot 3 sets of data in the same graph in Excel.
Once you have completed your graph, save your Excel file and complete the related questions in the lab report.
Exercise \(\PageIndex{3}\) – Calculating Mean (Average) and Standard Deviation
Create a new sheet in Excel and name it Exercise \(\PageIndex{3}\).
An average of a set of numbers, also known as the Mean, is calculated by adding all of the values, and dividing by the number of values. For example, to calculate the average of the four numbers, you add the numbers, and divide by 4.
\[\text{Average } = (4.5 + 5.0 + 5.5 + 5.0) / 4 = 5.0 \nonumber\]
The Standard Deviation (SD) is a measure of how spread out the numbers are around the average. When all of the values in the average are very close together, the SD is small. When the values vary significantly, the SD can be high. Why is this relevant? Imagine that you are applying for a job as a Nurse, and you are told that the average starting salary in the area where you want to work is $48, 000. Sounds great, right? However, if the average is $48, 000, then that means some people earn more than that, and some earn less. If the SD is $2, 000, then most of the salaries are close to $48, 000, but if the SD is $20, 000, then some salaries could be as high as $68, 000, while some could be as low as $28, 000.
In this exercise, you are going to use Excel to calculate the Average and SD for two sets of patient data, and then analyze the results. You have two patients who have been advised by a Nutritionist that they need to restrict their daily caloric intake to an average of 1800.0 Calories per day, for a 10-day period. Patients are asked to record their daily caloric intake values. To determine whether the patients met their goals, you will use MS Excel to calculate the Mean and Standard Deviation, and then analyze the results.
Table \(\PageIndex{3}\): Day vs Caloric Intake Data of Patients 1 and 2 for Exercise \(\PageIndex{3}\)
| Caloric Intake | ||
|---|---|---|
|
Day |
Patient 1 | Patient 2 |
|
1 |
1800.0 | 1800.0 |
|
2 |
1800.0 | 2400.0 |
|
3 |
1800.0 | 1800.0 |
|
4 |
1800.0 | 1500.0 |
|
5 |
1800.0 | 1300.0 |
|
6 |
1800.0 | 1500.0 |
|
7 |
1800.0 | 2300.0 |
|
8 |
1800.0 | 2600.0 |
|
9 |
1800.0 | 1000.0 |
|
10 |
1800.0 | 1800.0 |
|
Mean |
||
|
Std Dev |
||
a. Copy the data shown in the table above into Excel.
b. Click here (or see below) for a video tutorial on how to use Excel functions to calculate the Mean and Standard Deviation for both sets of data.
c. Once you have completed your calculations, save your Excel file and complete the related questions in the lab report.
Exercise \(\PageIndex{4}\) – Other Excel Functions
a. Create a new sheet in Excel and name it Exercise \(\PageIndex{4}\).
b. Click here (or see below) for a video tutorial on how to use other Excel functions for different mathematical calculations.
c. Once you have completed your calculations, save your Excel file and complete the related questions in the lab report.
Experimental Report
| Date: | Name: |
| Section: | Team: |
| Instructor: |
Exercise \(\PageIndex{1}\) – Creating XY Scatter Plot and Linear Regression Analysis
a. Copy and paste an image of your completed graph in the space provided below.
b. Was the material that you evaluated real gold, or Fool’s gold? How do you know?
Exercise \(\PageIndex{2}\) – Plotting Multiple Datasets in a Single Graph
a. Copy and paste an image of your completed graph in the space provided below.
b. Using the equation from your graph, what would be the calculated Pressure (P, \(y\) value) for 6.5 L of volume (\(x\) value) at 50\(^{\circ}\)C?
c. In general, at all three temperatures, what happens to pressure as volume increases?
Exercise \(\PageIndex{3}\) – Calculating Mean and Standard Deviation
a. Copy and paste an image of your table with data for Patients 1 and 2 in the space provided below. This image should include the calculated values for Mean and Std Dev.
b. Compare the values for Mean and Std Dev for the two patients. Did both patients meet the requirements for an average of 1800.0 Calories per day? Explain in your own words your interpretation of the Std Dev values for the two sets of patient data.
Exercise \(\PageIndex{4}\) - Other Excel Functions
Insert a copy of your complete Excel function table here.