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)
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
males females
0 10 20 30 40 50 60
Mean Aerobic Capacity of Males & Females
Pre-training Post-training
Maximum Aerobic Capacity (ml/kg/min)
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.
Pre-training Post-training 0
10 20 30 40 50 60
Mean Aerobic Capacity Pre and Post Training for Both Sexes
males females
Maximum Aerobic Capacity (ml/kg/min)
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
