MS Excel

1
MS Excel

• Assignments 1, 2 and 3
• PED 3900

2
MS Excel - 1

• Develop a crossword puzzle based on an
  exercise/fitness/sport topic using Excel.
• Create a group of 10 clues (5 Down and 5
  Across) and answers related to your topic.
• Use cell formatting features to customize cells.
• Insert at least one image related to the topic.
• Provide the correct answers to your clues on a
  separate sheet (see the lower left hand corner).
• Samples are found on the web page. The are to be
  used as guides only.
• Email attachment to Dr. Emmett by 10/10/05
• 1200 p.m.

3
(No Transcript)
4
MS Excel - 1

• Select All (Ctrl A) or Click and drag over
  columns to be used
• Format columns - width (set at 3 or close to
  it)
• Format rows - height (set at 15 or close to it)
  
• Hide the background gridlines Tools Options
  Viewthen uncheck Gridlines in the lower left.
• Add a background image Format Sheet
  Background then select the saved picture file
  or change background color Format Cells
  Pattern

5
MS Excel - 1

• Change the color and size of gridlines Select
  the boxes to be outlined then go to Format
  Cells Border (used the box on the left to see
  the changes)
• Align number inside a cell Select the area
  around the puzzle, then - Format Cells
  Number (select Left and Top)
• Write the Down and Across clues on Sheet 1 and
  call it Puzzle
• Insert a picture/logo Insert Picture File
  (if saved on computer or disk) or Clip Art.
• Write the answers on Sheet 2 and call it Answers

6
Excel - 2

• Create a spread sheet with the data provided on
  the web page entitled Data for Excel-2.
• 1a. Sort the subjects alphabetically by their
  initials and change the number of decimals to 1
  and one column with fractions
• 1b. Name the first sheet Group
• 2a. Copy the data and using the sheet tabs at the
  bottom on the excel window, paste into Sheet 2
• 2b. Calculate average values for all variables
  and perform at t-Test
• 2c. Rename the second sheet Stats
• 3a. Copy the data into a third sheet
• 3b. Graph VO2max and HR as a scatter graph. Name
  the graph VO2max and Resting HR. Label the X
  and Y axis.
• 3b. Filter the HR that are below 70.
• 3c. Rename the third sheet Heart Rate and give
  this sheet a light yellow or orange background.
• Email as an attachment to Dr. Emmett by noon
  10/17/05

7
Formatting Data (sheet 1)

• Click and drag through all the data plus the
  first row below the data in columns D through K.
• In the menu bar, go to Format, then to Cells,
  then Number and Number.
• Change the number of decimals to 1
• Click and drag through all the data in column J.
• In the menu bar, go to Format, then Cells, then
  Number and Fraction.

8
Sorting data (sheet 1)

• Click on any cell that has data in it
• From the menu chose Data Sort
• Be sure that the Header row option is selected
  below.
• Find the Subject heading to sort by the
  subjects name.
• Click Okay

9
Calculating averages (sheet 2)

• Click in the first cell below the data of column
  A and drag over to the cell below the data of
  column B.
• In the menu go to Format, then Cells, then
  Alignment, then Merge Cells
• In the two merged cells, type AVERAGE, format to
  left justify and change the text color to blue.
• Click in the cell below all the data of column C.
  (
• In the fx line that is below the menu but above
  the spreadsheet, type AVERAGE(
• After the right ( move the cursor to the cell
  at the top of the column C. Click and drag down
  the last cell of column C.
• You will see something like C2C18. Put a ) at
  the end so that the equations looks something
  like AVERAGE(C2C18)
• In place of clicking and dragging down the
  column, you can also type in the values B2B29.
• Hit return and the average will show in the cell
  you click on at the first.
• Click on the same cell and drag to the right
  through all the cells just below the data of all
  the columns.
• From the menu, Edit Fill Right to apply the
  average equation to the additional cells.

10
t-Test (sheet 2)

• Click in cell G21.
• In the menu bar, got to Insert, Function and
  select TTEST and click Okay.
• Click in Array 1, then click and drag down all
  the data in column G
• Click in Array 2, then click and drag down all
  the data in column I
• In the Tails box, type 2
• In the Type box, type 1
• Click Okay or hit the enter key
• Note the number that appears in cell G21.

11
Filtering Data (sheet 3)

• Click and drag through the specific data you want
  to filter. Dont filter the average value at the
  bottom of the column.
• From the menu, Data Filter AutoFilter
• Click on the black triangle ? next to the
  column or row heading, in this case, HR.
• Choose custom
• In the first box, select less than and in the
  second box type in 70.
• Click Okay
• Change the back ground of this sheet to another
  color

12
MS Excel - 3

• Create an Excel spread sheet which includes the
  following
• A record of 3 different statistics for 5 athletes
  over 3 years
• e.g. basketball shooting FG, 3pt, Rebounds,
  Points, Assists, etc..
• Gather information from www (nfl.com, nba.com,
  nhl.com, mlb.com, etc)
• Include a photo for each person.
• At the end of each row or column of data, use the
  Average function to calculate 3-year averages
  for each person for each stat. DO NOT type in the
  average values. They need to be calculated using
  the Insert Function option in Excel.
• Tip Calculate one average and then use the Fill
  Down or Fill Right option to calculate the
  remaining values.
• After the averages, rank the players 1 through 5
  for each particular statistic with 1 being the
  best and 5 the worst.

13
MS Excel 3

• Next to the average column, include an overall
  ranking based on how well each player ranked in
  the individual statistics.
• Rankings of 1st, 3rd, 1st, 2nd, and 4th would 1
  3 1 2 4 11.
• The LOWER the ranking, the better.
• This players overall ranking would be better than
  12 but not as good as 10.
• Off to the right side, insert a chart for at
  least one statistical category. Be sure to
  customize the chart.
• Insert a link to a related web site. DO NOT use
  the web address at the link (www.espn.com). Write
  ESPN and set up a hyperlink from the name.
• Use Formatting option to add color, background,
  and borders to cells.
• Email as an attachment to Dr. Emmett by 10/24/05

14
Inserting a Chart

• Click and drag over the cells of data you want
  displayed in a chart.
• From the menu, Insert Chart.
• Select the type of chart (column, line, etc.)
• Select Next
• Select Next, again
• Name the X and Y axis. X is along the bottom
  and Y is along the side
• Select Next
• Select Finish
• The chart will appear on your spreadsheet. You
  can move it to a desire location by click and
  dragging.

15
NOTE You only need 3 years of 3 stats for 5
players.