Lab 4: Microsoft Excel
This lab is due by: Friday, October 17th, 2008 at 6 p.m.
NOTE
If you don't have Microsoft Excel on your computer, you can use the computers at the Library. You can also use the CoC computers but only if you have an active CoC account.
Objectives
1. To learn how to use Microsoft Excel.
2. To learn precision and general formatting in Excel.
3. To learn how to do formulaic calculations within Excel.
4. To learn both absolute and relative cell referencing.
5. To learn conditional formulation within Excel.
6. To learn how to create and configure different types of graphs in Excel.
Intro. to Excel:
Microsoft Excel is a spreadsheet and database application, which makes use of cells in order to organize and work with data. Excel has many uses; from pure number-crunching, to graphical presentations, to organization of information in a grid layout. It can also handle several higher-level mathematical functions for those who really want to do such things but also want to avoid purchasing Maple or Mathematica (Athough I do not suggest Excel as any kind of true replacement). Anyway, enough introduction, now to the lab.
By the way, cells are those little boxes where the data of a spreadsheet goes.
THE LAB:
For Starters, DOWNLOAD THIS FILE!*
It contains the spreadsheet you will be working with.
The columns and rows of the spreadsheet are like those of battleship. The columns are labeled as letters from left to right starting from A, and the rows are labeled from top to bottom starting from 1. Cells are referenced by their letter designation followed by their number designation, so B3 would be the release year for Metropolis.
Now it's time to manipulate some data.
But first note that fields B30, B31 and B32 are empty! Yes, we are requiring you find out the release years for the last 3 movies and input it in the years columns.
There are number of websites you can get this info from. Here are a couple of them:
Ok, so now you have your first 4 columns filled out. Now, let's manipulate some of the data in these columns.
Let's determine the profit made by each of these movies.
All the profit values that you calculate should be stored in column E.
The formula for such a calculation is simple:
Gross - Budget = Profit
So select Cell F3 (profit cell for Metropolis). Notice the formula for F3 is empty.
You could just manually calculate the values by hand and them enter them into the corresponding Excel field. However, if you do so you are not making use of Excel and will thus receive ZERO POINTS for your calculations if you do so on this lab.
To make good use of Excel and get credit for your calculations, please do the following:
With F3 selected, type "=E3-D3" (w/o the quotes) in the formula box f(x) and press Enter. This tells Excel that F3 is equal to E3-D3.
Note, the cell F3 will display the correct answer, but if you select F3, the formula bar will still display "=E3-D3".
Now we will fill in the rest of the Profit column in either of the following 2 ways.
The Labor-intensive method:
You could just repeat the same process for all the movies to determine their profits. But notice, there are 30 movies, so entering the formulas for all 29 you've yet to enter would be menial and easily mistyped. So I highly suggest you make use of Excel's formula copying utility.
The Quick and Easy way:
Excel's copy and paste methods are actually quite intuitive. By selecting a cell and pressing the Ctrl+C, you copy the formula for that cell. When you paste this copied formula into another cell, E xcel applies the formula relatively; using the spatial relation of the original cells involved in the formula to the cell containing the formula. For example, if the formula for F3 is =E3-D3, then selecting F3 and hitting copy (Ctrl + C) and then selecting F4 and pasting (Ctrl + V) into F4 would give F4 the formula =E4-D4.
So if you want to get this lab done faster do the following:
1. Select the profit cell for Metropolis(F3).
2. Hit Ctrl+C (Or go to the Edit dropdown tab and select Copy).
3. Select all the profit cells for the other movies by clicking on cell F4 and dragging all the way to cell F32.
4. Hit Ctrl+V (Or again go to the Edit dropdown tab and select the paste option).
The values for the Profit cells for all 30 movies should now be respectively filled in.
Now let's calculate the Profit % made by each movie.
The formula is defined as follows:
Profit / Budget * Adjustment Factor
So now we'll apply this formula to figure out the Profit % for Metropolis.
Based on how we calculated the Profit cells before, you might think the formula for this cell is "=F3/D3*B36". But using this form would lead to relative referencing issues when you tried to copy and paste Metropolis's formula onto others. The Profit % for all the other movies would end up being zero due to multiplication by the empty cells below B36.
To solve this issue, we can use absolute referencing for the Adjustment factor cell.
To tell Excel that you always want to look at cell B36 for every Profit % formula, we will use the
following formula for Profit % for Metropolis:
=F3/D3*$B$36
This tells Excel to always use B36 but to allow F3 and D3 to change relatively.
Use the convenient copy and paste methods to calculate the Profit % for the remaining 29 movies.
Cell Formating/Precision:
HINT
You can select all cells by clicking on one cell and then holding down your mouse button and then drag it over the rest of the cells.
The values for the Budget and Gross columns should be floating point numbers.
All the values in the Budget column are rounded to 2 places.
All the values in the Gross columns are rounded to 3 places.
To round the Budget values off to the nearest whole number, select Budget cells for all 30 movies.
Then select the Format dropdown menu option near the top of the screen.
Within the dropdown menu, select the "Cells" option.
Change the category of the cells from General to Number.
Then change the number of decimal places option to that appropriate for an Integer
All the cells in the Budget column should now be rounded off as integers.
Repeat the same procedure for all the cells in the gross column, but instead of rounding it off to a whole number, round it off to 1 decimal place.
Sorting:
IMPORTANT
Make sure you highlight all the data or else you may sort only the Movie Names and each movie's info may not stay with the appropriate movie name!
Let's take our data and sort it!
Sorting is a useful feature in Excel. It allows you to place data in a desired order (i.e. alphabetically or numerically).
To sort your data you must start by highlighting the data that you would like to sort
Next click on the Data menu at the top.
Lastly choose Sort from the drop-down menu.
When you do this a box entitled Sort will pop up. It will give you the options to Sort by, Then by, and so on. You can also tell it if your list has a header or not.
Now sort the Movie Names in ascending alphabetical order, then by opening weekend gross. Recall that this is accomplished in one sort operation. You can choose either ascending or descending order for the weekend gross column.
Graphing:
HINT
Step 3 in the wizard allows for more options than step 2 so don't sweat it too early. Feel free to make your graph look pretty (it can even have 3-d effects). But if you don't really care what it looks like, that's cool too.
Let's make a graph!
Click on the little button in the toolbar which looks like a graph, or click the Insert dropdown menu and click Chart...
A little window called the Chart Wizard should pop up.
This window should display quite a few chart type options, such as bar graphs, scatterplots, line graphs, surfaces, etc.
Please choose a Column type graph (subtype is up to you) and make a graph showing the Profit % on one axis and the Movie Name on the other axis.
There are many ways to draw a graph in Excel but the easiest way is to highlight all the data columns(in this case, it would be the Movie Name and Profit % columns) and then press the Chart Wizard button and follow the steps. Make sure you select the 'Series in' Rows button.
Please drag the graph down so that it does not cover the rest of the spreadsheet. It would also be nice if you could name your graph and also name the x-axis and y-axis, but that's optional.
The instructions for making the graph are not very detailed.......you just have to play around with it for a bit. And if you still can't figure it out, that's what the TA's are for.
Conditionals:
Now use the following formula (written in python syntax) to fill in the Adjusted % for all 30 movies.
if profit_percent > 0:
profit_percent = profit_percent + 10
else:
profit_percent = 0
DO NOT just enter the python code directly into your Excel spreadsheet!
To use this formula, you must learn the how to use the IF conditional in Excel.
The syntax for the IF function in Excel is as follows:
=IF(logic_test,value_if_true,value_if_false)
For the logic test, only a single = is used unlike in the python syntax.
After filling in the Adjusted % cells for all movies using the IF function and copy and paste methods, format them as integers.
Data Entry:
HINT
You can input any random numbers for Year, Budget and Gross - they don't have to be the right values.
Enter the information for a new movie.
It can be any movie you want.
Enter the release year, Opening Weekend, Budget and Gross.
Once you enter these 5 fields, the information for the next 3 columns will be automatically calculated and displayed.
Once you have added this new movie, re-sort your movies alphabetically, and update your graph to show the new movie.
Synopsis:
Here is what you should have done:
1. Downloaded the lab4.xls file from this website.
2. Opened that file using Microsoft Excel.
3. Entered the correct release years for the last 3 movies.
4. Calculated Profit cells for all the 30 movies.
5. Calculated Profit % for all movies.
6. Changed the arrangement of the Movie Names from a chronological list to a list sorted alphabetically and then by opening weekend gross.
Once again, make sure you highlight all the data or else you may sort only the Movie Names and each movie's info may not stay with the appropriate movie name!
7. Made a graph of Movie Name vs Profit %.
8. Used the IF method of Excel to fill in the Adjusted % and formatted these cells as integers.
9. Made a new entry for a movie
Here's what you still need to do:
1. Submit your completed lab4.xls file to T-Square.
2. Enjoy the rest of your evening!
General Tips:
1. If you made some huge error which messed the positions of all the cells, you can always 'undo' that by pressing Ctrl + Z.
2. If you didn't follow directions correctly from the start and don't know how to reverse the errors on your existing spreadsheet, just download a new one and start again. It shouldn't take very long to do it again and it maybe easier than reversing the errors on your existing file.
3. To select multiple cells, click on one cell and then hold the left mouse button down and drag it. To select cells from different columns, do the same thing but while pressing the Ctrl button.