Load and activate the Analysis - Loudoun County Public ...



STATISTICS AND EXCEL BASICS:How to find the Means, STDEV and do an ANOVA and “T test” using EXCEL1. Enter your data in Excel.Have nothing but numbers in the columns. Do not have 20%, 20 cm, or 2min 10 sec. Leave no empty columns between data columns.Put the label at the top of each column.Columns (independent variable: tissue type)Rows (dependent variable: enzyme RXN rate) 403860025400A. 00A. 2. To calculate the means:Add a row immmediately after the data.Label it “mean”Click under the first data column in the “mean” row. (A)Type: =AVERAGE( then highlight the data in that column ) note:data must be enclosed in “ ( )” Click Enter.Do the same for the other columns or copy and paste this formula to the other columns.You will use the means in making the graph.3. To calculate the Median:Add a row immmediately after the data.Label it “Median”Click under the first data column in the “median” row. (A)Type: =MEDIAN(then highlight the data in that column ) note:data must be enclosed in “ ( )”Click Enter.Do the same for the other columns or copy and paste this formula to the other columns.4. To calculate the Mode (only if there are repeating numbers)Add a row immmediately after the data.Label it “Mode”Click under the first data column in the “Mode” row. (A)Type: =MODE( then highlight the data in that column ) note:data must be enclosed in “ ( )” Click Enter.Do the same for the other columns or copy and paste this formula to the other columns.5. To calculate the Range:Add a row immmediately after the data.Label it “Range”Click under the first data column in the “Range” row. (A)Type: =RANGESubtract the highest from the lowes value – this is the rangeDo the same for the other columns or copy and paste this formula to the other columns 6 To calculate the standard deviation:Add a row immmediately after the data.Label it “standard deviation”Click under the first data column in the “standard deviation” row. (A)Type: =STDEV( then highlight the data in that column ) note:data must be enclosed in “ ( )”Click Enter.Do the same for the other columns or copy and paste this formula to the other columns.You will use the standard deviation in making the graph.GRAPHING: Creating your graphs:Highlight the mean (average) information – including the headers – for all the data you want to graph.Go to charts and select the appropriate chart for the dataOnce the graph is inserted, select “Chart Quick Layouts” to include the title, and axis labels.If you select the graph and it shows a “green +” then select that to add Chart Elements To Add Error Bars:Option 1:On the same chart in excel, click on the bars in the bar graph (or data point on a line graph). On the tool bar select Chart Layout “error Bars” (if you do not have this as an option, see Option 2 below)Select “error bar options”, under vertical error bars click on both directions. Under error amount click on CUSTOM, then specify the value that you got in step 6 by highlighting (selecting) the cell that has the standard deviation. Add the same number for both positive and negative values, click OK. Error bars should appear on your graph. The standard deviation represents the variability of the data or the range of your data. OPTION 2:If you do not see the “Error Bar Option” highlight the graph, if a green + sign appears to the right of the chart select that + sign for “chart elements”Next select the “Error Bars” then go to the bottom and select “More Options”Select “Custom” and “Specify Value”Add the same number for the Positive and Negative Error Value – OR you can select or click on the cell in the Excel spreadsheet with the Error Bar Number420052516510007524753683000ANOVA and TTEST: To perform an ANOVA and a ttest you can use the Microsoft Analysis Toolpak: HYPERLINK "javascript:" Load and activate the Analysis ToolPakClick the?File?tab, click?Options, and then click the?Add-Ins?category.If you're using Excel 2007, click the?Microsoft Office Button??, and then click?Excel OptionsIn the?Manage?box, select?Excel Add-ins?and then click?Go.If you're using Excel for Mac, in the file menu go to?Tools?>?Excel Add-ins.In the?Add-Ins?box, check the?Analysis ToolPak?check box, and then click?OK.If?Analysis ToolPak?is not listed in the?Add-Ins available?box, click?Browse?to locate it.If you are prompted that the Analysis ToolPak is not currently installed on your computer, click?Yes?to install it.ANOVA: Statistical Analysis – performing an ANOVA (comparison of data with 3 or more variables) – this is only for a PC if using a MAC you must use the STATPLUS add in.For most of our data you will use a One-Way Anova available from the Data Analysis Tool PakHere is a tutotorial from YouTube: using a Mac, install the StatPlus add in and the choice for an ANOVA will be under the Statistics (SP) – choose OneWayANOVAHere is a youtube video to help as well: Statistical Analysis – performing a ttest (comparison of 2 variables OR if ANOVA of 3 or more variables is significant p<0.05) For help: calculate the ttest there are several options depending on what computer you are using.4010025172085Table 100Table 1Option 1 (any computer):Add a row and Label it “ttest”43434008509000Click on the adjacent empty cell Click on Formulas in the tool barClick on Insert then Statistical then select T.Test from the menu bar . You should see instructions similar to Table 1 in the cell. OR you can directly type =t.test in a cellFor “array 1” select the first column of data to be comparedFor “array 2” select the second column of data to tbe comparedFor “tails” select 2 since we predict that one group will be higher than the other415290017081500For “type” select 3 since the standard deviations are different for each group (comparing unequal variance)Add the information, hit return and the number generated is the P-value. Option 2 (school computer and PCs) Add a row and Label it “ttest”Click on the adjacent empty cell Click on Formulas in the tool bar5086350133350Table 200Table 2Click on “More Functions” then “Statistical” then select T.Test from the menu bar (you should see a table similar to Table 2)For “array 1” select the first column of data to be comparedFor “array 2” select the second column of data to tbe comparedFor “tails” select 2 since we predic that one group will be higher than the otherFor “type” select 3 since the standard deviations are different for each groupAdd the information, hit return and the number generated is the P-value.Option 3 (MAC users) 45148501460500Click on Formula Builder and type in ttest You should see the a table similar to the one on the right labeled Table 3For “array 1” select the first column of data to be comparedFor “array 2” select the second column of data to tbe compared5762625253365Table 300Table 3For “tails” select 2 since we predict that one group will be higher than the otherFor “type” select 3 since the standard deviations are different for each groupAdd the information, hit return and the number generated is the P-value. Interpretation of the statistics table.This is the table does not need go into your Research Notebook but you will report the P value for the two tailed test. You need to look at the two-tailed P value that was generated from your test, this is the Pvalue. This means that you care about values higher or lower than your control group’s mean. One tailed would be used if we cared only about values that were higher (or only values that were lower) than the control mean. Please use the two-tailed values.Note that this P value is 1.35103E-05. This means 1. 35103 x 10-5. This is a very small number and certainly < 0.05. This means that the mean of this experimental group is significantly different than the mean of the control group. We can reject the null hypothesis (that the experimental and control groups are the same) and support the alternative hypothesis (your hypothesis in your paper). You do not need to use all of those digits when referring to the P value in your paper. Just 2 decimal numbers is plenty 1.25103E-05 becomes 1.25E-05.When looking at Pvalues, remember the followingReport the result of the ttest as the actual number Report the P Value as either p < 0.05 or P>0.05The p Value is the probability that our NULL HYPOTHESIS (Ho) is true. If the p Value is < 0.05 = REJECT the NULL and accept the Alternative hypothesis (our two groups are statistically different – there is statistical difference between the means)If the P Value is > 0.05 = ACCEPT the NULL Hyposthesis (our two groups ar too simialr to claim they are different – there is no statistical difference between the means) ................
................

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

Google Online Preview   Download