Excel Basics:
Exercise Excel 2-3

Title: Jan's Illustrated Computer Literacy 101
Did you want Working with Numbers: 2007,2010,2013,2016  or españolIcon: Change web

You need to use what you just learned, and maybe learn a little more. Complete all parts of the following exercises. Don't forget to backup your Class disk when you have completed the exercises or whenever you stop for the day and saved a document along the way.

These exercises use files from the numbers resource files. The default location for these files is c:\My Documents\complit101\numbers\ You cannot make changes to these files and save them in the same place. Save the changed documents to your Class disk. This keeps the original resource files intact in case you need to start over or another student will be using this same computer.
full floppy disk How to handle a full disk


Where you are:
JegsWorks > Lessons > Numbers

Before you start...

Project 1: Excel IntroTo subtopics

Project 2: Excel Basics  
    Getting StartedTo subtopics      
    ArrangeTo subtopics  
    AutoFillTo subtopics
    FinishTo subtopics
    Summary
    Quiz
    Exercises arrow to open topics 
    Exercise" Ex. 1 Theater tickets
    Exercise" Ex. 2 Soccer budget
    Exercise" Ex. 3 Pings
    Exercise" Ex. 4 On Your Own

Project 3: Format & ArrangeTo subtopics

Project 4: Groups & FormulasTo subtopics

Project 5: DesignTo subtopics

Search
Glossary
Appendix

Exercise Excel 2-3: Pings - Average, Max, Min

What you will do: edit an existing spreadsheet
autofit columns
use standard widths
autofill
use page break preview
create formulas using Average, Max, and Min

Start with: amazon.xls from your resource files

The editors of the newsletter Computers Today want to write an article about what things influence how fast your Internet connection is. To measure Internet speed they are timing a signal that they send to certain web sites. Such a signal is called a ping. The chosen sites have been pinged every hour every day for weeks. The editors plan to analyze the data to show people how the time of day, the day of the week, and the kind of web site affect how fast your connection is to a site on the Internet. [The data are quite real but were gathered for a school science fair project rather than for our imaginary newsletter editors.]

  1. Open: Open the file amazon.xls from your resource files or download it now. Sheet1 shows some of the data collected on the Internet site for Amazon, www.amazon.com. (A real site) The data extends off screen to the right to Column Y.
     
  2. Class disk Save as amazon pings2.xls in the excel project2 folder of your Class disk.
  3. AutoFit: Widen Column A with AutoFit so you can read the times in those cells. 
     
    The times are not actually correct. Each one should be at 38 minutes after the hour instead of exactly on the hour.
     
  4. AutoFill: Rewrite the series of times. (Edit the first time to 12:38:00 AM and drag down the column.)
     
  5. AutoFill: In cell C3 type  Saturday . Then drag the fill handle along the row until you reach Column Y. Then release the mouse button. The screen will scroll if you drag the fill handle out of the window. Now you have the days of the week matched with the dates. This is helpful since the editors want to look at the day of the week as a factor in Internet speed.
     
  6. Standard Width: The word Wednesday is too long to fit in the column width. AutoFit Column G to show all of Wednesday. Click and hold the right edge of Column G to see its new width. Set the Standard Width to this width. (Hint:  Format  |  Column  |  Standard Width )
     
  7. Edit: In cell A2 after Ping Average add  (ms) . This shows that the ping time is measured in milliseconds (thousandths of a second), which is abbreviated ms.
     
  8. Page Break Preview: Move the breaks so that a week is together on one page with Saturday as the first day. Add a page break by right clicking a cell in Column X and choosing  Insert Page Break . Two breaks appear, a vertical and a horizontal. Remove the horizontal break by dragging it up or down until it meets the top or bottom blue line. Return to Normal view.
     
  9. Resize column: Drag the right edge of Column B to the left until it is 2.14 wide. Now it doesn't waste so much space.
     
  10. Labels: You will create some formulas in the next step. Add labels for your results. In cell A31 type   Averages: In cell A32 type  Maximum:  In cell A33 type  Minimum:  In cell AA4 type  Averages: In cell AB4 type  Maximum:  In cell AC4 type  Minimum: 
     
  11. Formula: To calculate an average you must add the values and divide by the number of values there were in the list. You could write a formula but Excel includes one already. You will start with column C even though it only has two data entries. In cell C31 type  =AVERAGE(C6:C29)  and press ENTER.
     
  12. Formula: The function MAX will find the largest value in a list. The function MIN will find the smallest value in a list. In cell C32 type  =MAX(C6:C29)  and in cell C33 type  =MIN(C6:C29) .
     
  13. AutoFill: Select cells C31:C33 and drag to the right to column Z. You will have to drag off screen a little bit and let the window scroll. If it scrolls too far, don't release the mouse button, but drag back left and scroll back. It can be tricky. Cell Z31 shows the error  #DIV/0!  because there are no values in the column. Delete the contents of Z31:Z33.
     
  14. Formulas: In cells AA6, AB6, and AC6 create formulas similar to the ones above that use the values in the row to find the average, maximum, and minimum values. Copy these formulas down the column to row 29.
     
  15. Format numbers: Format all the cells with formulas to show just 2 places to the right of the decimal.
     
  16. Header: Set a custom header with your name and the date on the left, the file and sheet names in the middle, and Exercise Excel 2-3 on the right.
     
  17. Prepare to Print: Open Page Setup and set Orientation to Portrait, Print titles to $1:$2 for rows and $A:$B for columns. Spell Check. Check Page Break Preview to see that all data will print. Make corrections, if necessary.
     
  18. Print Preview: Do you have 4 pages? Does each page have the Times showing and the titles at the top? Header? Do the rows for Averages, Maximum, and Minimum show on each page? Does the fourth page show the columns for row Averages, Maximum, and Minimum?
  19. Class disk Save.
     
  20. Print Print all four pages of Sheet1.

Exercise Excel 2-3 completed