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

KIN 506 Excel 2013 lesson

You should be familiar with the following tasks using excel in order to be able to complete the statistics assignment.

download the practice Excel file from the KIN 506 web site

Using function wizard to calculate Mean and Standard Deviation

To install advanced statistical functions:

File > options > Add-Ins > Analysis Toolpak > GO > select Analysis Toolpak > OK

Then find “Data Analysis” in “DATA” tab

IN EXCEL, FOR STANDARD DEVIATION USE: “STDEV.S”

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

select range

Copy functions & formatting to paste to other similar, parallel columns

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

Plot a scattergram

do a scattergram (2 sets of data on a single graph)

Insert tab: charts: Scatter: Scatter with only markers:

Rt click chart: Select data:

Remove any contents if present

click on Add button

select series name box, click on cell that contains name of data set (use D3 contains “FALL”)

select X Values box

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 FALL for the demonstration)

select Y Values box

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 FALL for the demonstration)

Click OK

To add second set of data on one pair of axis.

click on Add button

select 2nd series name box, click on cell that contains name of data set (use F3 contains “WINTER”)

select X Values box

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 WINTER for the demonstration)

select Y Values box

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 WINTER for the demonstration)

Click OK

click OK

click OK

Chart Elements: add axis title, legend, remove gridlines

Change the plot symbols so that they are:

·  Clearly and easily distinguishable on a grey scale printer

·  They can be distinguished if one plot symbol lies partially or completely on top of another

NOW CHECK THE PLOT AGAINST THE DATA!!

Change the text and axis lines to BLACK.

Add Axis tick marks

Consider:

1. Why to set the axis range
2. How to set the axis range (see above)

To include a regression line

RT click data set: add trendline

To format trendline: select trendline: right click: format trendline: line style = make it not solid

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

Insert tab: charts: 2-d column: select top left sub-type (2-d column graph):

Rt click: Select data:

Select data source:

Remove any contents if present

click on Add button

select series name box, click on cell that contains name of data set (e.g. "Pre-training"). This is the term that will appear in the figure legend.

highlight Values box to select it

select (drag over) the column of cells containing the first data (e.g., pre-training data for males & females, do not include column titles)

OK

Horizontal categories label: EDIT

select (drag over) the column of cells containing the bin labels (i.e. males & females)

OK

to add the second data series, click on Add

click in Name box to place cursor there

select a cell containing the label of the data second set of data (e.g. "Post-training"). This is the term that will appear in the figure legend

highlight the Values box to select it

select (drag over) the column of cells containing the second data (e.g., post-training data for males & females, do not include column titles).

OK

OK

Control legend

turn off Gridlines

Add axis lines

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

Now add the error bars to first data set:

Chart tools: Design: Add Chart Element: error bars: more error bar options:

Select ONE set of data. (e.g., Pre training)

Select "Both" & "Custom"

“specfy value”

Place cursor in "+" range box

Drag and select the column of male & female pretraining standard deviation values

Place cursor in "-" range box

Drag and select the column of male & female pretraining standard deviation values

OK

Now add the error bars to second data set - by repeating the above procedure for the second data set

NOW CHECK THE PLOT AGAINST THE DATA!!

Note:

·  You can insert text (such as a symbol to mark significance) by using: Insert: text: text box.

Non-parametric test – Chi Squared

Chi Squared analysis

See sample data in Excel lesson data worksheet.

Organize table of Actual Observations

Calculate table of Expected Observations (using absolute and relative addresses)

Expected responses = (column total x row total) / N

Use function: CHITEST (use function help if needed)

Function returns probability

Note that you can copy the first Chi Squared test you build, and use it as a template for additional questions.

Backup your work to a cloud site as soon as you are done

Ó 2015, Gordon Chalmers, Ph.D. 7 Updated 10-27-15