Assignment 1 – Exploring data

Commands  for  Statistical  Procedures,   Tests,  and  Charts  in  Excel   Mean: =AVERAGE(click and drag on cells); click “enter” Median: =MEDIAN(click and drag on cells); click “enter” Mode: =MODE(click and drag on cells); click “enter” Range: (highest value – lowest value)

***There is no command for mode in Excel, so this needs to be done manually.

Standard Deviation: =STDEV(click and drag on cells); click “enter” Confidence Interval:

=CONFIDENCE(alpha, standard deviation, size); click “enter”

*** Alpha is 1 – confidence level, usually is 0.05 *** Click on the cell with standard deviation *** Size refers to the sample size

Upper Limit of Confidence Interval: (mean + confidence interval) ***There is no command for this in Excel

Lower Limit of Confidence Interval: (mean – confidence interval) ***There is no command for this in Excel Correlation Coefficient (R):

=CORREL(click and drag on cells in array 1, click and drag on cells in array 2); click “enter”

Regression Sum of Squares (R2): Multiply Correlation Coefficient by itself ***There is no command for this in Excel z-values (Table): =NORM.S.INV(0.9750); click “enter”

You will use this to find the value at a 95% confidence, two-tailed test.

Standard Deviation for Binomial Distribution: =SQRT((p)(p-1)/n); click “enter”

Charts,  Plots,  and  Graphs   Scatter Plots

1. Highlight data (all variables) a. If your data is not next to each other, you can hold

the “control” key while highlighting the other variables. This allows you to highlight columns/rows that are not next to each other and not lose the ones you highlighted earlier.

2. Click “Insert” 3. Click on the scatter plot

Pareto Charts

Creating Pareto Charts on Excel is a bit more involved (not difficult, just more steps involved); however, we found a great instructional video online that you can use for help. The video explains how to create a Pareto Chart, step by step with shortcuts and in plain language. https://www.youtube.com/watch?v=bxr0iL6NI1g Circle Charts (Pie Charts):

1. Highlight data *** If data is in different places, follow the instructions listed in the Pareto Charts.

2. Click “Insert”. 3. Click on Pie Chart

Bar Graphs:

1. Highlight data *** If data is in different places, follow the instructions listed in the Pareto Charts.

2. Click “Insert”. 3. Click on Bar Chart

Histograms and Bar Graphs: We found two great videos that will instruct you on how to create Histograms http://www.youtube.com/watch?v=RyxPp22x9PU And Bar Graphs http://www.youtube.com/watch?v=opl1xnC6cCI The steps to create these two graphs are very similar to the other graphs. The videos are easy to follow and in plain language.