Lab 5: Microsoft Excel

Lab 5

Microsoft Excel

[Turnin: an edited Excel spreadsheet]

Objectives:

Learn how to use Microsoft Excel
Learn how to do simple calculations in Excel
Learn the difference between absolute and relative cell references
Precision and formatting in Excel
Learn how to create graphs in Excel

What is Microsoft Excel?

Microsoft Excel (or just plain old Excel) is a spreadsheet application. Spreadsheets are brilliantly conceived ways to organize data by grouping it into rows and columns. As a promising Tech student, you may never need to look at Excel after this lab. However, if you ever decide to drop out of college and get a job plugging numbers into computers and/or doing mindless administrative chores, Excel may soon become the best seven million lines of code ever written.

Vocabulary

Cell -- the small boxes where data exists in a spreadsheet

Function -- with your extensive Scheme knowledge, no definition is necessary

The Assignment

Retrieve the assignment from here and open it using Excel.

You will see the following Spreadsheet:

Note that the first column in the spreadsheet is labeled A, and the first row is labeled 1. Similarly, each cell in the Excel spreadsheet has a unique identifier, much like your gt number. Spreadsheets use a coordinate system to access information, so the "NAME" cell is at A1 (the "A" column and "1" row). You can access the "Jim Greenlee" field by using A9. A9 and A1 are called "cell references".

Writing Formulas

Let us now work through some sample calculations that will help you complete the rest of the lab. Let's figure out what Jim Greenlee's Exploit Index is.

The Exploit Index is defined as:

Exploit Index = Mad-Genius * Sadism

How can the Excel spreadsheet accomplish the same task? Each cell can either contain data or a formula. Move your cursor to where Jim Greenlee's Exploit Index should go (I9) and click there.

Now, rather than hard-coding the value, let us type in a formula to calculate Greenlee's exploit index. Type:

=D9*E9 (Hit enter)

Note that Excel displays what you type in the formula bar--the row directly above the column guides.

The previous exercise was so interesting that you should now go grab a Coke and turn on some tunes, LOUDLY. You will need it to stay awake through the rest of this lab.

Let's analyze the line that you just typed in. This line multiplies what is in cell D9, Jim's Mad-Genius, with the value from cell E9, Jim's Sadism, and displays the result in the spreadsheet cell I9. By clicking on I9, you can see the exploit index formula displayed in the formula bar. The value you should see in I9 should be a rather mediocre 1764.

Copying Formulas

Now we should calculate the Exploit Index for everyone on our list. We can do this in 2 ways:

The stoopid way

Repeat the procedure that you just applied on Jim Greenlee to everyone on your list. This is what is called the UGA solution. It is slow and horribly inefficient, but it will give you the correct answer... eventually. Eventually, in CS, is an unacceptable term, so we will liberally deduct points for anyone who solves these problems in this manner.

The 'let me get done with this quickly' way

Use the cut and paste commands in the 'Edit' menu. When you copy a formula from one cell, the spreadsheet is smart enough to apply that formula to the other cells in a relative fashion. That means that if your formula is in cell I9 and references cells D9 and E9, then when you copy it to I3, it will reference D3 and E3.

Let us walk through this step by excruciating step.

Select the cell whose formula you need to copy (I9 in this case)
Select the copy option from the edit menu. This copies the formula.
Now select all the cells to which you want to apply the formula (I2-I17).

Hint: To select many cells at the same time, you can click and hold your mouse button on the first cell, and drag it and then release it on the last cell.
Choose paste from the edit menu.
The calculated results should appear in all these cells.

In the above calculation, we used a relative addressing scheme so that we could copy the formula easily but still make it apply to each individual row. Sometimes, however, we want a cell reference to stay constant. There are ways to use a value in just one particular cell as the source value. This is called absolute cell addressing.

Let's calculate the final value for the Exploit Index. This is accomplished by normalizing the previously calculated value for the Exploit Index (refer to the steps above)

Exploit Index = (Mad-Genius * Sadism) / Normalizer

The value for the normalizer is given in cell B20.

In Excel-speak, the formula for Jim Greenlee's Normalized Exploit Index would be given as:

=(D9*E9)/$B$20 <hit Enter>

The $B$20 means that you will be using the specific value in cell $B$20 for all your formulas, and this WILL NOT change after you use the efficient algorithm to calculate the final values for Exploit Index.

Precision and Formatting

For any number in Excel, you can set the precision for displaying it. Precision controls how many digits are displayed after the decimal point.

How to change a cell's formatting

Select the cell you want to adjust
Right click on the cell
From the pop-up menu that appears, select "Format Cells."

Now format the appropriate values in your spreadsheet for Mad-Genius so that two--and ONLY two-- decimal places are displayed on the screen. To format multiple cells concurrently, select all the cells that you need to format, go to the format menu, select the cell option, and then hunt around for the correct option.

Hint: This should present itself rather obviously under the Number option.

There are other formatting options that are more aesthetically pleasing. For example, you can change the text attributes of the labels, shade them, give them borders and generally make them pretty. Awww. You may beautify your spreadsheet as much as you like. Feel free to be creative, but don't even think about altering any of the values already provided in the spreadsheet.

Graphs

In Excel's context, a graph is defined as a diagram that displays a summary view of the data in a concise form. Below is a table that indicates situations where one type of graph can be more useful than another:

	Type of Graph	Useful When
	Bar Graph	Comparing one aspect of different categories.
	Pie Graph	Comparing percentages
	Line Graph	Looking at trends (not necessarily at equally spaced time intervals)
	XY Graphs	Looking at trends over equal intervals

Let us apply this to the spreadsheet we have been altering. Show a graph that displays each person's Exploit Index. Put the people on the X-axis and the Exploit Index on the Y-axis.

To plot the graph, go into Insert->Chart.

You are responsible for figuring out how to draw the graph and which type of graph you choose to represent the data. Make sure that you put the graph on the sheet that you have been altering.

You are now armed with all the Excel you will need to complete this lab.

The Final Assignment

Get the Excel file you will be editing

(you should have done this already if you read the lab).
Calculate the Normalized Exploit Index for all the people listed

(you should have done this already if you read the lab).
Fill in the Mad-Genius column to 2 decimal precision

(you should have done this already if you read the lab).
Plot a graph of the Normalized Exploit Index vs. Person's Name

(you should have done this already if you read the lab).
Fill in the Cruelty column.

The formula for Cruelty is:

Cruelty = Cruelty Factor * Mercilessness

Hint: The absolute value information will be useful here
Use Excel to fill in the Penitence column. Penitence is defined as:

Penitence = 1 / Incorrigibility

You will note, however, that there are there a couple of values in the incorrigibility column that have a zero numeric value. As everyone knows, dividing anything by zero leads to nasty answers. In order to avoid this pitfall, read up on the IF feature in Excel.

The Penitence is calculated as follows:

(define Penitence
You can find the syntax for the IF function in the Excel on-line help.
In order to get a complete picture of how despicable each villain truly is, calculate the Mean Badness of each gangsta listed in the spreadsheet.

Mean Badness = (Mercilessness + Cruelty + Mad-Genius + Incorrigibility) / 4

The translation to Excel-speak is left to you.
After you have calculated the Mean Badness for each and every villain, use Excel's built-in sort feature to sort the villains in ASCENDING order based on their Mean Badness.

Hint: Look in the Data menu option
Finally, create a graph of each villain (X-axis) vs. Mean Badness (Y-axis). Make sure it is on the same spreadsheet as the data.
Turn in the lab5excel.xls file through WebWork
Go and enjoy life