You should be familiar with the following tasks using ...



You should be familiar with the following tasks using excel in order to be able to complete the statistics assignment. TO GET HELP ON ANY TOPIC, CLICK ON THE EXCEL HELP BUTTON download the practice Excel file from the KIN 306 web site note: When using excel, you may use keyboard shortcuts rather than using the menu bar pull-down menus to do many operations. This manual will list all menu commands and some short-cuts.insert new column (insert column to left of subject column for labels)select column to right of where you want new columnHome tab: Cells: insert >> insert sheet columnplace line below data (to visually separate it from summary statistics to be calculated below line)select row which you want the line to be BELOWHome tab: Font: 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 menu, and select the line desired.Enter the word MEAN in column A, immediately below the lineUsing function wizard to calculate Mean and Standard Deviationcomputing mean and standard deviation using functionsIN EXCEL 2010, FOR STANDARD DEVIATION USE: “STDEV.S”Formulas tab: Function Library: Insert Function: OR USEHome tab: Editing:5824855-74872More functions: category = statistical, then select function (e.g., average)select rangeFormat Number to set # of decimals format numbers to set # of decimalsHome tab: Number: Alternate: Select the cells you want to format, then right click, then use Do not waste time repeating work setting up formulae and formatting, do it once, then…..Copy functions & formatting to paste to other similar, parallel columnsWriting a formulaBMI = 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.62562BMI = 25Note that in Excel, the multiplication sign * must be included when needed, Excel does not multiply round bracketsShow step-by-step formulaShow multiple parts of formula in one stepHow to show a number in scientific notation in excelSelect the cells you want to format, right click, choose “format cells”, choose “Number” tab, choose “scientific” category, Set decimals, OK.Plot a scattergram do a scattergram (a fancy one with 2 sets of data on a single graph)*Insert tab: charts: Scatter: Scatter with only markers:2487295-74295Draft chart shows up on screen, ensure it STAYS SELECTED so “Chart Tools” continue to be available.Chart tools: Design: Data: Select data:70421588900 Select data source: Remove any contents if presentclick on Add buttonselect series name box, click on cell that contains name of data set (use D3 contains “FALL”)select X Values boxselect (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 boxselect (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 OKTo add second set of data on one pair of axis.click on Add buttonselect 2nd series name box, click on cell that contains name of data set (use F3 contains “WINTER”)select X Values boxselect (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 boxselect (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 OKclick OKclick OKChart must be selected or chart tools will not be availableControl legend (Keep legend if you have more than one set of data on one axis pair)Chart tools: Layout: legend: position/none28682951409701595755381019234153810turn off Horizontal Axis Major Gridlines (not needed in this simple plot, may be needed in more complex ones you make)Chart tools: Layout: Axis: Primary Horizontal Axis: NoneChange the plot symbols so that they are:Clearly and easily distinguishable on a grey scale printerThey can be distinguished if one plot symbol lies partially or completely on top of anotherenter appropriate labels for both value (X) axis, & value (Y) axisChart tools: Layout: Labels: Chart titleChart tools: Layout: Labels: Axis titleNOW CHECK THE PLOT AGAINST THE DATA!!Double click on any item in the graph you wish to modify (experiment!)Skills to demonstrate:Why to set the axis rangeHow to set the axis rangeDouble click on the axes to open operation boxSelect: Axis Optionsset reasonable (& not conflicting) min, maxSelect: NumberSet # decimals displayedCopy a graph and a range of cells to word.Double click on any item in the graph you wish to modify (experiment!)To include a regression line and calculate the equation of the lineChart must be selected or chart tool will not be availableChart tools: Layout: trendline: more trendline options (do not choose the shortcut to directly add a linear trend line): select data series: options = display equation411035510795039281101670050To format trendline: select trendline: right click: format trendline: line style = make it not solidCalculate a correlationplace the cursor in the cell where you want the calculated value to appear Formulas tab: Function Library: Insert Function: category = statistical, then select function (e.g., correl)select rangeAlternate method: Formulas tab: Function Library: More Functions: Statistical: then select function (e.g., correl), Show: 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.Show: 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.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 range of excel cells with values and labels to word: Select the range of cells you wish to copy. Copy the range and paste the data table into word document, to show up as a table in word.Backup your workEnsure you have more than one copy of any work you care about!!! The following are advanced techniques required of KIN 506 students, and potentially useful for KIN 306 students when writing lab reports.Advanced bar graph techniquesTo 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-trainingPost-trainingmales4550females3040Standard Deviation of Aerobic Capacity (ml/kg/min)Pre-trainingPost-trainingmales1015females2.55graph the means of variability of groups Insert tab: charts: column: select top left sub-type (plain column graph):Chart must be selected or chart tools will not be availableChart tools: Design: Data: Select data: Select data source: Remove any contents if presentclick on Add buttonselect 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 itselect (drag over) the column of cells containing the first data (e.g., pre-training data for males & females, do not include column titles) OKHorizontal categories label: EDITselect (drag over) the column of cells containing the bin labels (i.e. males & females)OKto add the second data series, click on Addclick in Name box to place cursor thereselect 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 legendhighlight the Values box to select itselect (drag over) the column of cells containing the second data (e.g., post-training data for males & females, do not include column titles).OKOKControl legend (Keep legend if you have more than one set of data on one axis pair)Chart tools: Layout: legend: position/noneturn off Value (Y) axis Major Gridlines (not needed in this simple plot, may be needed in more complex ones you make)Chart tools: Layout: axis: gridline: Horizontal gridlinesenter appropriate title, and labels for both value (X) axis, & value (Y) axisChart tools: Layout: Labels: Chart titleChart tools: Layout: Labels: Axis titleNOW 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:Double Click on the axis line or tick label to open operation boxUse options in box to set reasonable min, max, # decimals displayedNow add the error bars to first data set:Chart tools: Layout: analysis: error bars: more error bar options:Select ONE set of data. (e.g., Pre training)OKSelect "Both" & "Custom"“specfy value”Place cursor in "+" range boxDrag and select the column of male & female pretraining standard deviation valuesPlace cursor in "-" range boxDrag and select the column of male & female pretraining standard deviation valuesOKNow add the error bars to second data set - by repeating the above procedure for the second data setNote:You can insert text (such as a symbol to mark significance) by using: Insert: text: text box.You can change the words used in the graph legend by changing the text of the corresponding cell in the excel spreadsheet.Changes in the data in the excel spreadsheet will automatically be replotted.The same data can be plotted in an alternative organization, as shown below, by: Chart tools: design: data: Switch row/columnNon-parametric test – Chi SquaredChi Squared analysisSee sample data in Excel lesson data anize table of Actual ObservationsCalculate table of Expected Observations (using absolute and relative addresses)Expected responses = (column total x row total) / NUse function: CHITEST (use function help if needed)Function returns probabilityNote that you can copy the first Chi Squared test you build, and use it as a template for additional questions. ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download