1 / 18

CS105 Lab 6 – Introduction to Excel

CS105 Lab 6 – Introduction to Excel. Announcements: Quiz 3 is now available and due on Tuesday, October 2 at 10 pm MP3 is posted and due Saturday, October 13 at noon You can work in groups of not more then 3 people. If you need to find a partner, e-mail to Bedoor ( alshebli@uiuc.edu ).

alma
Download Presentation

CS105 Lab 6 – Introduction to Excel

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. CS105 Lab 6 – Introduction to Excel Announcements: • Quiz 3 is now available and due on Tuesday, October 2 at 10 pm • MP3 is posted and due Saturday, October 13 at noon • You can work in groups of not more then 3 people. • If you need to find a partner, e-mail to Bedoor (alshebli@uiuc.edu) CS 105 – Fall 2007

  2. Objectives • Learn about copying and pasting in Excel • Learn how to use functions in Excel • Understand differences between absolute and relative cell reference • Learn to use the fill handle • Learn the difference between copy, fill, and move CS 105 – Fall 2007

  3. Download the File • Open a web-browser. Go to the course website: http://www.cs.uiuc.edu/class/cs105 • Click on “Lab Discussions.” • Right-click on Excel Worksheetfor Lab 6. Choose “Save Link As” or “Save Target As” and save it on your desktop. CS 105 – Fall 2007

  4. A Spreadsheet column letters B3:E6 Range Read as “B3 to E6” B9:E9 Single Row Range A3:A8 Single Column Range Row numbers CS 105 – Fall 2007

  5. Entering Formulas • Enter a formula in cell E7 to calculate the total points the student has earned • Start your formula with = • There are several ways to write this formula One way is: =B7 + C7 + D7 Are there other ways? = sum(B7:D7) CS 105 – Fall 2007

  6. Copying Formulas • Copy the formula in cell E7 to the cell just below it (use Ctrl-C to copy and Ctrl-V to paste) • What happens? Why? • You can check formula in E8 in two ways: • Click on a cell and look at the formula field • Press Ctrl-~ to see formulas in all cells CS 105 – Fall 2007

  7. Copying Formulas using fill handle • Now copy the formula in cell E8 to the cells E9:E11 (this time, use the fill handle: click on it, hold, and drag it down three cells.) CS 105 – Fall 2007

  8. More Formulas • Now, enter a formula in cell F7 to calculate the grade value. Use the total points in cell B16. (You want to divide the total points earned by the total points possible) • NOTE: You might think of using the average function here, but it will not work if the tests have different weight, which is actually the case CS 105 – Fall 2007

  9. Copying Formulas Again • Now, copy the formula in cell F7 to cells F8:F11 • What happens? Why? • How can we change the formula so this doesn’t happen? = E7 / $B$16 CS 105 – Fall 2007

  10. Cell References • By default, Excel adjusts cell references in a formula when the formula is copied and pasted • The cell references will be changed by the same number of rows and columns that you moved the formula • If you want the row or column to stay the same, put a dollar ($) sign in front of it CS 105 – Fall 2007

  11. Correct Version • Use Ctrl-Z to Undo the Fill-Handle operation in cells F8:F11 • Put the formula from the ninth slide in cell F7, then copy and paste this to the cell below it • You can also use the fill handle in another way: • Double click on the fill handle in cells E8 and F8 to automatically fill out columns E and F CS 105 – Fall 2007

  12. Calculating Average Scores • To calculate average in cell B14, enter the formula = Average(B7:B11) • Copy cell B14 to the four adjacent cells to the right. (C14:F14) • Or use fill handle, but this time horizontally • Note: double click always fill vertically CS 105 – Fall 2007

  13. Highest and Lowest Scores • We will use the Min and Max functions to calculate the minimum and maximum of a range of values. • In cells B19:F20 we need to compute the lowest and the highest grades respectively. • How you will do this? CS 105 – Fall 2007

  14. Why Spreadsheets? • Suppose the total points for the semester is changed to 360. • Put 360 in cell B16. • Notice how all the other numbers are automatically recalculated. CS 105 – Fall 2007

  15. Reference in B3 Copied to D4 Reference Type $A$1 $A$1 Absolute A$1 C$1 Mixed $A1 $A2 Mixed A1 C2 Relative How do Cell References Change? CS 105 – Fall 2007

  16. Move, Copy, & Fill *Cell data in these operations may have been altered. See next slide for details. CS 105 – Fall 2007

  17. Move, Copy, & Fill (continued) CS 105 – Fall 2007

  18. More on fill • On a separate sheet enter “test1” in cell A1 • Fill ten cells down. What fill does? • Enter “test1” in cell B1 and “test4” in cell B2. • Select both cells and use fill. What now? • Fill works with: • Numeric data, e.g. 1,2,3,… or 1,3,5,… • Time, e.g. 10:00, 11:00, 12:00,… • Dates, 1-May, 1-Jul, 1-Sep,… • Etc, etc, etc. CS 105 – Fall 2007

More Related