Exercise Excel 2-3: Pings - Average, Max, Min
|What you will do:
||edit an existing spreadsheet
use standard widths
use page break preview
create formulas using Average, Max, and Min
Start with: amazon.xls from your
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
- Open: Open the file amazon.xls from your resource
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.
Save as amazon pings2.xls in the
excel project2 folder
of your Class disk.
- 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.
- AutoFill: Rewrite the series of times. (Edit the first time
to 12:38:00 AM and drag down the column.)
- 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.
- 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.
- 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.
- 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
. 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
- 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.
- 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:
- 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.
- 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
- 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.
- 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.
- Format numbers: Format all the cells with formulas to show
just 2 places to the right of the decimal.
- 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.
- 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.
- 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
Maximum, and Minimum show on each page? Does the fourth page show the
columns for row Averages, Maximum, and Minimum?
Print all four pages of Sheet1.