You Should Be Familiar with the Following Tasks Using Excel in Order to Be Able to Complete

Excel lesson

You should be familiar with the following tasks using excel in order to be able to complete the statistics and record book assignments.

Table of Contents:

Using function wizard to calculate Mean and Standard Deviation 2

Format Number to set # of decimals 3

Format Text 3

Writing a formula 3

Copying excel material to word 4

Plot a scattergram 5

To include a regression line and calculate the equation of the line 6

Calculate a correlation 6

Do a paired t-test 7

Backup your work 7

Advanced bar graph techniques 8

Non-parametric test – Chi Squared 10

TO GET HELP ON ANY TOPIC, CLICK ON THE HELP BUTTON

download the practice Excel file from the PE 306 web site (http://www.wwu.edu/~chalmers/ then select the PE 306 page, and follow the instructions there to save a copy to your storage device)

start excel (double click on the excel file you wish to open)

note: When using excel, you may the wizard buttons in the toolbars rather than using the menu bar pull-down menus (or keyboard short cuts) to do many operations. This manual will list all menu commands and common menu buttons to short-cut some of these menu commands.

Fall / Fall / Winter / Winter
subject / Body Weight / jump 1 / jump 2 / jump 1 / jump 2
1 / 121 / 5 / 6 / 8 / 9
2 / 132 / 8 / 9 / 12 / 13
3 / 130 / 10 / 12 / 13 / 15
4 / 155 / 15 / 18 / 19 / 20
5 / 110 / 22 / 25 / 27 / 29
6 / 105 / 28 / 30 / 33 / 32
7 / 11 / 11 / 15 / 15
8 / 28 / 33 / 35 / 34

open new workbook (one opened when you started excel)

file > new

addresses of cells

copy label from cell and paste to another cell ( or cut before pasting)

name sheet one, keep as original data

change text in a cell

save while working

file > save (or keyboard shortcut)

copy blocks of cells to paste to a new sheet, name the sheet

select cells, cut, paste

insert new column (insert column to left of subject column for labels)

select column to right of where you want new column

insert > column

place line below data (to visually separate it from summary statistics to be calculated below line)

select row which you want the line to be BELOW

Use this button to place a line. To place the line shown (the last line you placed, a thin underline in figure at left), click on the left side of the button. To place other types of boarder lines, click on the arrow to get the following menu, and select the line desired.

Alternate: use menu bar to place boarder

format > cells then select BOARDER tab, then click on the underline icon (third one down on left side)

Enter the word MEAN in column A, immediately below the line

Using function wizard to calculate Mean and Standard Deviation

computing mean, min, max and standard deviation using functions

Function wizzard:

category = statistical, then select function (e.g., average)

select range

Alternate: use menu bar to select function

insert> function

category = statistical, then select function (e.g., average)

select range

HEd 435 only: Format Number to dollar format

Format number to dollars: menu bar to format

format > cells > then select NUMBER tab, then click on CURRENCY, then set desire # decimals, then set negative numbers to be shown as (red).

Note: you can format a cell to dollars using the button, but it does not offer you the range of options the menu bar method does.

Format Number to set # of decimals

format numbers to set # of decimals

Alternate: use menu bar to format

format > cells then select NUMBER tab, then select category = number, and set # of decimals

copy functions to paste to other similar, parallel columns

relative addresses (understand this concept)

Format Text

formatting text

Use the above menu bar to format text

Alternate: use menu bar to format

format > cells then select FONT tab

Writing a formula

BMI = BODY MASS INDEX

= WEIGHT (kg) / height2 (m)

e.g., 5 ft 4 inch & 145 lbs

= 64 inches, 145 lbs

= 1.62 meters, 66 kg

= 66 / 1.62562

BMI = 25

(note that in Excel, the multiplication sign * must be included when needed, Excel does not multiply round brackets)

absolute addresses (understand this concept, compare to relative addresses)

practice writing formula with address for next score

copying formula to related test data points

Skills to demonstrate:

1. How to split the screen vertically and horizontally to view all of your data.
2. How to insert a new worksheet.

3.  How to show a number in scientific notation in excel

Copying excel material to word

For writing a report: You will produce a professional report by copying the graph and pasting it into a Word document to create on Word file with text and figures. To copy the graph to word: Select the graph (Ensure you select the whole graph, i.e., click near the edge of the graph frame and see the outtermost edge of the graph indicated by the selection box. Do not select only the axis portion of the graph by clicking at the center of the graph.) Copy the graph. Then open your word report document, and place the cursor where you want the graph to go, and paste the graph in.

For writing a report: Your word report must have page numbers. In the word program use view > header and footer > click on left-most page # icon

The page # can be centered, as you center text using the format menu bar center icon

PE 506 - do advanced on plotting means with error bars, lesson near end of excel notes.

Plot a scattergram

do a scattergram

*

go to the DATA page

TRICK: To view the top & bottom of your page that is too big to fit on a screen, use a HORIZONTAL SPLIT SCREEN. Similarly, to view extreme right and left sides beyond the view of the screen, use a VERTICAL SPLIT SCREEN.

TRICK: To select continuous data across a split screen- select the first cell in one window, then shift+click of the last cell in the second window. The full range between these two selections will be selected.

insert>chart (OR click on chart wizard button )

select CHART TYPE = XY(scatter), and select top left sub-type (plain scatter graph)

click NEXT

select series in columns button (under Data Range tab)

select series tab

if any items are listed in the series box, click Remove until they are all gone

**

click on Add button

highlight X Values box to make it black

select (drag over) the column of cells containing the data you wish to plot on the X axis (do not include column titles) (use JUMP1 for the demonstration)

highlight Y Values box to make it black

select (drag over) the column of cells containing the data you wish to plot on the Y axis (do not include column titles) (use JUMP2 for the demonstration)

To plot two (or more) sets of data on one pair of axis.

If you have more than one data series to plot on the one graph (e.g., both males & females on one graph) follow the procedures from * to ** above then...

Place in ONE cell adjacent to each of the pairs of data to be plotted a label for each of the data pairs that you would like to appear in your figure legend (e.g, Males, Females). This will usually be much better than using one of the variable names for the figure legend.

click on Add button

click in Name box to place cursor there (this is an additional step from above)

select a cell containing the label of the data first set of data (e.g. "males")

highlight X Values box to make it black

select (drag over) the column of cells containing the first data (e.g., male data) you wish to plot on the X axis (do not include column titles)

highlight Y Values box to make it black

select (drag over) the column of cells containing the corresponding first set data you wish to plot on the Y axis (do not include column titles)

to add the second series, click on add AGAIN

click in Name box to place cursor there

select a cell containing the label of the data second set of data (e.g. "females")

highlight X Values box to make it black

select (drag over) the column of cells containing the second data (e.g., female data) you wish to plot on the X axis (do not include column titles)

highlight Y Values box to make it black

select (drag over) the column of cells containing the corresponding second set data you wish to plot on the Y axis (do not include column titles)

continue as below

click NEXT

select Legend tab

turn off show legend (Keep legend if you have more than one set of data on one axis pair)

select Gridlines tab

turn off Value (Y) axis Major Gridlines (not needed in this simple plot, may be needed in more complex ones you make)

select Titles tab

enter appropriate title, and labels for both value (X) axis, & value (Y) axis

click NEXT

place graph as object in: then select the sheet for graph to go in (I suggest a blank sheet)

(optionally: place graph as new sheet (but this appears to plot it full page size, and I have not been able to resize it smaller))

click FINISH (finally)

name the sheet the graph is in (so you can find it)

NOW CHECK THE PLOT AGAINST THE DATA!!

Double click on any item in the graph you wish to modify (experiment!)

Optional - to set a fixed range on an axis:

Click on the axis line or tick label to open operation box

Use options in box to set reasonable (& not conflicting) min, max, step sizes, # decimals displayed etc.

Skills to demonstrate:

1. How to remove fame and shading from a graph.
2. How and why to set the axis range, and have it be the same across two graphs you wish to compare.
3. How to do 2 graphs on one axis pair.
4. How to change a graph once you make it (e.g. axis label, position of legend, source data).
5. How to copy a graph and a range of cells to word.

To include a regression line and calculate the equation of the line

Click on the data set to select it, you can select one data set within a plot of more than one data set.

Chart > add trendline > type = linear; options = automatic & display equation

Calculate a correlation

calculate a correlation

place the cursor in the cell where you want the calculated value to appear

insert> function (or use function wizard )

category = statistical, then select CORREL

select range for each data set (array of data)

Do a paired t-test

do a t-test (paired)

tools>data analysis>t-test paired

select both variable ranges, including labels

enter 0 (zero) as hypothesized mean difference

ensure labels is selected

keep ALPHA = 0.05

select output to new page

Remember:

In excel 0.0002051 is written as 2.051E-4

stats terminology / excel terminology & menu choice
dependent t-test / T test: paired two sample for means
Independent t-test / T-test: two sample assuming equal variances

For writing a report: You will produce a professional report by copying the output table and pasting it into a Word document. To copy the output table to word: Select the range of cells you wish to copy. Copy the range. Then open your word report document, and place the cursor where you want the table to go, and paste the data table in.

print a single page

close excel file (or program) by clicking on small "x" box at top right of window

Save your work to your folder space on the WWU server

Backup your work

Ensure you have more than one copy of any work you care about!!! L

The following are advanced techniques required of PE 506 students, and potentially useful for PE 306 students when writing lab reports.

Advanced bar graph techniques

To plot mean and standard deviation values for groups to report results.

We will use the following data to produce the following sample graph:

Mean Aerobic Capacity (ml/kg/min)
Pre-training / Post-training
males / 45 / 50
females / 30 / 40
Standard Deviation of Aerobic Capacity (ml/kg/min)
Pre-training / Post-training
males / 10 / 15
females / 2.5 / 5

graph the means of variability of groups using the chart wizard

select CHART TYPE = COLUMN, and select top left sub-type (plain column graph)

click NEXT

select series in columns button (under Data Range tab)

select series tab

if any items are listed in the series box, click Remove until they are all gone

click on Add button