Excel Assignment Paper
Open the Excel data file in the Week 8 folder. Your spreadsheet should like this:
This week we will complete the calculation and explanation of descriptive statistics with the help of Excel. Below are the commands needed to compute the respective values.
Measures of Variability/Dispersion1:
Standard Deviation Quartiles:
=min(array of values) =max(array of values) =(Max-Min)
=VAR.P(array of values)2 =STDEV.P(array of values)
=QUARTILE.INC(array of values, 1)3 =QUARTILE.INC(array of values,3)
1 In order to calculate the Range in Excel, we need to calculate the High and Low value(s) in the data set(s).
2 In Excel, like all statistics programs, you have the option of calculating some measures for a “sample” or for a “population”. We are going to use the Population form. The alternative form of the function in Excel for variance and standard deviation would have “.S” instead of a “.P”. Excel Assignment Paper.
3 In Excel, there is the option to INClude the median the calculation of the quartiles or EXClude the median. Including, as we have done, will affect the Q1 and Q3 calculations and provide more symmetrical quartiles. The disadvantage to this is that it will make it more difficult to identify outliers. Thus, we assume more normality in our distribution.
ORDER A PLAGIARISM-FREE PAPER NOW
=SKEW.P(array of values)
=KURT(array of values)
The measures of skewness and kurtosis may be unfamiliar and/or new to you. These values can also be used to describe how normally distributed a set of data is. Each also has a threshold – or rule of thumb – value, which one can use to determine how normal (or not) a distribution is. Skewness measures the symmetry of the distribution. The closer the value is to 0 the more symmetrical the distribution is, e.g. the less skewed. The value measures the relative size of the two tails. Data that has skewness measured greater than | 1 | are highly skewed (positive or negative direction indicates the direction of the skew). Kurtosis measures the “peakedness” of the distribution. Excel calculates this value using the “minus 3” rule – a correction that actually reflects a normal distribution with a value of 0. Thus, in Excel, the closer the value is to 0, the more normally distributed the distribution is. Both values, like many of our descriptive measures, are heavily influenced by our sample size.
Like before, add these measures in the Excel spreadsheet. Begin below “mean”, in cell A49. Your spreadsheet should look like this (open your previous assignment if needed):
Using the commands above, let’s calculate the values. Ultimately, you should come up with the following values – formatted in tabular form:
4 The Interquartile Range (IQR) describes where the middle 50% of the data is located.
All Reported Crimes
Annual Number in Boston 1985-2014
Mode N/A (multimodal) Median 35,788 Mean 43,069 Min. 22,018 Max. 70,003 Range 47,985 Variance 258,567,142.6
Standard Deviation Q1
16,080.02 31,718.75 56,188 24,452.25 0.47 -1.27
How would we explain these results? First, we see a fairly large range. Annually, we have seen a near consistent decrease in the overall number of reported crime in Boston. Its peak was in 1989, with just over 70,000 crimes reported, and a low in 2014, with just over 22,000 crimes reported. With a large range usually comes a large standard deviation. A value of just over 16,000 indicates that the typical distance each annual total is away from the mean is about 16,000. So, the annual values tend to differ. We know the IQR represents where the middle 50% of the data lie, so half of all years had between about 31,000 and 56,000 reported crimes. Excel Assignment Paper.
Variability and Dispersion
Calculate the measures of variability and dispersion for the same 3 offenses you have worked with previously. Create a similar table for those same 3 offenses.
Copy/paste them into or create in a Word document (.doc or .docx) which will be submitted.
Beneath each table (3 you picked and created), write a 100-word paragraph describing the measures of variability and dispersion.
Ensure all of your tables and write-ups are submitted in one Word (.doc or .docx) file for Assignment 3.
Worked Example for Week 10
Using the same data from our previous Worked Examples, along with new data, I will show you how you can test the difference between means using some basic descriptive statistics of the two samples being compared. As a result, you will see an example of hypothesis testing: a statistical test that examines the possible significant difference between two groups. The process can be done for proportions as well, but for our purposes we will focus on means.
To perform this test we will require descriptive statistics for two groups. The first group will be the sample of prisoners we have used in all of our Worked Examples. The second group will be a new hypothetical group from a different prison. Let’s assume the first group is male prisoners and the second group is female prisoners. Thus, we are testing to see if there is a significant difference in the mean number of months incarcerated for males and females.
Our null hypothesis is always written that the two means are equal. To reject this, we need to find a significant difference. Similar to “innocent until proven guilty”, we assume equal means until proven different. The null hypothesis can be written as follows:
H0: μ1=μ2 or 1=2
Our alternative hypothesis is written that the two means are not equal (significantly different). Excel Assignment Paper.
H1: μ1≠μ2 or 1≠2
Notice the subscript numbers next to the symbols for means: this is simply referring to each group. Each symbol with a “1” subscript requires the descriptive data from Group 1 and each symbol with a “2” subscript requires the descriptive data from Group 2. It does not necessarily matter which group you refer to as “1” and “2”; what is important is that you are consistent throughout the hypothesis test processes*. If you mix the groups throughout the steps, you will end up with incorrect and invalid results. Now that we have stated our hypothesis, we must list the descriptive statistics needed for the hypothesis test.
Group 1, as we stated, is the male prisoners. Since we have used this data throughout our Worked Examples, we already have all the information needed.
ORDER A PLAGIARISM-FREE PAPER NOW
N1 = 10 1 = 4 s12= 3.4
Recall N is our sample size. is the mean. S2 is the variance. This is very important; the standard deviation is not required in this test, we need to use the variance (standard deviation “squared”).
Group 2, as we stated, is the female prisoners. For our purposes, I will simply supply the descriptive statistics needed for the hypothesis test.
N2 = 12 2 = 2 s22= 2
Now that we have the required information we can begin to test the hypothesis that the mean number of months incarcerated is equal between male and female prisoners. From our two small samples we see that males have an average of 4 months and females have an average of 2 months. Remember, these are very small samples so we must use that information in combination with the variability to determine if we have enough information to conclude the difference is significant.
Step 1: Compute the standard error.
This step is tedious and requires a lot of information.
S1- 2=√(N1 )(N1 )S1- 2=√( )( ) N1 N1
Step 2: Compute the test statistic (t-value). We simply subtract the mean of group 2 from the mean of group 1 and divide by the standard error, the value we just calculated. Excel Assignment Paper.
T= T= T= 2.74
Step 3: Determine the critical value. This step requires knowledge of what alpha level you will be using and a T-distribution table of values. As is common in criminal justice research we will look at alpha levels of .05 and .01.
The critical value for our hypothesis test with an alpha level of .05 is 2.086 The critical value for our hypothesis test with an alpha level of .01 is 2.845 Step 4: Compare test statistic (t-value) and critical value. Interpret.
We computed a test statistic of 2.74 which is larger than the first critical value of 2.086; we reject the null hypothesis that the mean number of months incarcerated is equal between males and females. We are stating that based on the information provided to us we can say at the .05 alpha level (or with 95% confidence) the means are different.
When examining the test statistic of 2.74 at the .01 alpha level we fail to reject the null hypothesis that the mean number of months incarcerated is equal between males and females. This is due to the test statistic being lower than the critical value of 2.845. We are stating that based on the information provided to us we can’t say at the .01 alpha level the means are
different. Essentially, we can be 95% confident that the observed difference is true or not due to sampling error/chance but we cannot be 99% confident.
*As a note, depending on the values of the means and the order in which you subtract one from the other you may end up with a negative test statistic (t-value). That is fine. When this happens simply compare the numerical value itself to the critical value just as you would if the value was positive. The negative only implies directionality, a component we are not focusing on. The interpretation of actual difference is what is important. Excel Assignment Paper.
"Our Prices Start at $11.99. As Our First Client, Use Coupon Code GET15 to claim 15% Discount This Month!!"