Be obsessive—IT people attend to details because in IT every little thing matters.
In this lab exercise, we'll review basic Excel spreadsheets and then work with Excel's charting/graphing capabilities.
For this lab exercise, download this example spreadsheet: lab4-checkbook.xlsx. We'll try to make it look like this sample spreadsheet:
F3that takes the opening balance from
F2and subtracts the withdrawals and adds the deposits. When you've got the formula, then copy it down the column.
SUMIFFormula. Enter in the expense-type category names by hand and then write a formula to compute the sum of the withdrawals for each type. Use the function wizard to do this by highlighting the expense type cell (
C17in the sample), choosing the Formula tab, and then "Insert Function", searching for the function
SUMIF, clicking "OK", and then letting the function wizard walk you through the process. After creating your formula with the wizard, your formula should look something like this:
SUMIF(checkbook type names, type name condition, checkbook values to be summed)where checkbook type names is a range from the checkbook itself, type name condition is a single cell from the category column in the expense table, and checkbook values to be summed is another range from the checkbook. (Hint: the two ranges from the checkbook should start and end on the same rows.) When your function is working, copy it down the column for each type category. Remember to put commas between your 3 arguments and to use absolute and relative references appropriately.
Now, create a pie chart that shows the distribution of your expenses as shown in this sample pie chart.
To create this pie chart:
B16:C21in the example.
Also create a line chart that shows the balance of your account over time, as shown in this sample line chart. Create this chart as follows:
B3:B13in the example), and then press and hold the
Ctrlbutton to select the range of cells with the balance value (
F3:F13in the example). This will result in a more complex cell range specification (
B3:B13,F3:F13in the example).
It is important to know how to choose an appropriate chart (or charts) for expressing the message you want to deliver. As discussed in class, Excel provides a number of chart/graph types, including pie charts, bar/column charts, line graphs and scatter plots. Always choose the best one for your data and message.
For this lab exercise, consider the data shown in lab4-costs.xls and answer the following questions. Do not build any of the charts, just indicate which kind of chart you would build.
Jot down your written answers, and type them into the "Comments" field of your Moodle submission.
Submit your checkbook workbook as an attachment to your Lab 4 Moodle submission. Type your answers to the charting questions into the "Comments" field. If you did the college-costs extra credit, attach your college costs workbook as well, and make note of it in the "Comments" field.