BIOL 347 General Physiology Laboratory
Using Excel to Analyze Data
Background
on Data Analysis
During the course of the semester, you will need to write lab reports. In these
reports, you will be expected to compile your data use it to present your results
in a meaningful way. This is an important part of all scientific research.
Using Excel will help you a great deal in organizing your data and in
presenting it in a way that makes it easy to understand, and in distilling a
cogent, accurate summary of your studies. You can then use these in formulating
conclusions to explain the results of your work to others.
To begin, open Excel. If it is not in the QuickLaunch
toolbar, open the Hard Drive, and look for it in the Applications folder. Look
at the top of the screen. If there is no toolbar, go to View, then Toolbars,
and select Standard. Once you have entered some data into your spreadsheet, you
can begin to extract meaningful information from it. Too much data will
overwhelm a reader interested in your results. The goal of data analysis is to
present your findings in a way that doesnıt leave out important information,
but doesnıt include so much as to muddle your report.
The most important (and probably most common) function is the average or
arithmetic mean. Suppose you run 5 trials of an experiment in order to insure
the accuracy of your measurements. You now have 5 individual numbers, but this
can be unwieldy. To represent the general trend of these numbers, you may want
to use the average. This way, you only have one number to report, but it takes
all 5 of your other numbers and represents them (hopefully) accurately.
But the mean is not necessarily always an accurate representation. Suppose that
four of your trials have results in the range of 5-6 units. The fifth result is
in the .001 range. Averaging these numbers would be somewhat misleading. There
are several ways around this problem, but we will only discuss the two most
important ones. The first way around this would be to use the median. The median
of a data set is the number that is in the middle when the numbers are arranged
in increasing order. This has the benefit of not averaging in outliers, but its
drawbacks should also be apparent.
The second method is to use standard deviation. This function allows you to see
how close your data are to the mean value. For a given data set, a low standard
deviation is desirable. This indicates that a data set is very close to the
mean value, and that there arenıt any significant outliers. A high standard deviation
means that a data set has a high range of variability. When talking about
running trials, where repeatability is desired, a low standard deviation value
is what we would like to see.
Excel has many functions to help you in finding averages, standard deviations,
and can also be very helpful in making charts and graphs, which, when included
in your report, make data easier to visualize and understand.
Functions in Excel
Suppose you have a data set entered in your spreadsheet already. If you want to
find the average, click in the box where you want the average to appear, and
then make sure that the toolbar is showing, and click the f(x) button on it.
This opens up a window with several options. Immediately, you are given the
option to use the most recently accessed functions. On a typical computer,
average and standard deviation will be on this list, but if they are not, click
on All in the left drop down list and look through the alphabetical list of the
many functions Excel has to offer.
Once you have found the function you want to use (in this case, Average), click
Ok. You are given the chance to enter which boxes you want the function to
apply to (it will appear as F1:F5 or something like that). If you want to
average a given set of trials, just make sure that those boxes are selected,
and click Ok. If the right boxes are not the ones selected, move the window,
and drag select the boxes you do want the function to apply to, and click Ok.
The spreadsheet will update automatically. This is the process you will use for
any function in Excel, from Median to Average to StDDev.
There are some convenient ways to save yourself time
in using functions. If you have already declared a function for one of your
trials and you have five more, and you want to use the same function (say,
Average) on the other trials, you donıt have to input the function for every
single square again. Just click on the bottom right corner of the box that has
the function in it, and drag it over to whichever other cells you want the
function to apply to. Excel will handle the rest.
Graphing in Excel
Once you have a data set, you may want to graph it. When you graph data, it is
important to recognize the distinction between dependent and independent
variables. A dependent variable is usually the one being measured. An
independent variable is one that occurs at regular intervals, such as time.
Dependent variables are placed on the y--axis, while independent variables are
placed on the x-axis. If you were to measure the temperature of a cup of coffee
over time, then time would be your independent variable, and temperature would
be your dependent variable. The temperature of the coffee will change; time
moves forward incrementally, independent of what the coffee is doing. The graph
of the function would be described as a ³Temperature vs. Time² graph (note that
the dependent y-axis is first, and the independent x-axis comes second).
Excelıs graphing wizard allows you to easily graph your dependent variable
against an independent variable. If you have your x-axis variables on the left,
and the y-axis variables on the right, you can graph them easily without
rearranging. It is therefore recommended that you think beforehand what your
dependent and independent variables are before you begin inputting data into
Excel. It will save you a great deal of time and unnecessary hassle.
Once you have your variables lined up, just drag select from the top left
corner of your x-axis variables o the bottom right of the y-axis variables.
Click on the bar grasp icon on the toolbar. A new window will pop up. Select
the XY Scatter, and clic Next. A preview of your
graph will pop up. Title your chart and axes appropriately, click Next, and then Finish. A graph will pop up in your document.
Showing Trends with Graphs
A graph is a useful visual aid, but you may want to indicate trends on it more
clearly. To do this, you may want to add a Trendline,
or a best-fit line, a mathematically computed line which best approximates the
given data points. Go to the Chart menu and Add Trendline.
Double click on the Trendline to bring up the Format Trendline options. Under the Options Tab, check the options
to display the equation on the chart and to show the R squared value. Then drag
these to a location so that they donıt interfere with the graph itself.
This is a useful step because it gives you a slope for your graph, and that is
a very useful piece of information. As all calculus students know, slopes
indicate change and rate of change. In many calculations, this can be useful.
The R squared value is a way of telling how accurately the trendline
approximates the data, or how strong a correlation there is. An R squared value
of 1 is perfect correlation.