The objectives of laboratory 5 include learning some advanced functions of spreadsheets including sorting, charting, the reference operator, statistical functions and the IF operator.
In addition to these features of spreadsheets, you will have the opportunity to design and use a spreadsheet to solve a real-world problem - calculating scores and grades for a small class.
Laboratory 5 introduces how to sort data and produce charts and graphs of data in spreadsheets. We also introduce how to use the reference operator ($) to simplify the construction of formulas, the use of more built-in mathematical functions, including statistical ones, and how to test logical conditions with the IF operator.
Excel can do complex sorting based on data in one or more rows/columns.
Excel also has advanced charting features available with the Chart wizard. Because charts are linked directly with the data, if the data in the cells is changed, the chart will change automatically.
The reference operator ($) in Excel "glues down" a reference to a particular cell so it doesn't get modified when the formula is copied and pasted. Try dragging a number of formulas with and without the $ operator on parts of formulas and see how they change.
Statistical functions that you may want to use for this lab:
You may want to test the contents of some cell or group of cells and perform different actions depending on what you find. For example, suppose you have sales data for each month of the year for 3 regions of the country. You want to calculate which months are "good" and which ones are bad. You can accomplish that with the IF operator in Excel as follows.
Suppose the months are in cells A2-A13 (January is in A2), and the
data for the regions are in columns B (East), C (West), and D
(Midwest). Lets assume that we choose the number 50 to be the cutoff
for "good" sales figures. Then we can classify the sales values
for the midwest region in column E with the following formula in
cell E2 (then extend it down to cell E13):
=IF(D2>=50,"good","bad").
If we have more than 2 choices, we can use more than 1 IF statement,
by nesting the statements together. For example, try this formula:
=IF(D2>=50,"good",IF(D2>35,"OK","bad")).
Excel will, by default, print the results of formulas, rather than the formulas, in a spreadsheet. You can, however, view and print the formulas in each cell in a few different ways.
To view the formulas in a spreadsheet (Excel v. 7/Win95), you can select [Tools][Options ..], then click on the [View] tab, then put a check mark in the formulas box.
An alternative way to view formulas in a spreadsheet is with the key combination [Ctrl-`] (control and the left single quote). This will toggle between viewing the formulas and viewing the results of the formulas in the spreadsheet.
To print the formulas, view them first, then print while viewing them.
In the last part of Lab 5, the goal is to design a spreadsheet in Microsoft Excel for computing scores in a class where you are the T.A. You should think about the problem, how you would solve it by hand, and then how you can use spreadsheet formulas to solve the problem more quickly and accurately.
The new features of Excel introduced above will help you solve this problem.
Go to the index of
lectures for ECS15 - Fall 1997 .
Go to the homepage for ECS15 - Fall 1997 .