excel job

Student Instructions for Excel Assignment 1 Module 2

1. Step 1: Download the excel file from eCampus under Excel Assignments, Excel 1, with the title, “Excel 1 Data” and open in Microsoft Excel. There will be three columns, one titled ID, one titled days, one titled tattoo.

a. About the data i. Part of the research I’m doing for my dissertation looks at whether inmates

with visible tattoos return to prison faster (a shorter number of days) than inmates without visible tattoos. I’ve provided some data here to look at differences in “days” or days outside prison for inmates with and without tattoos. These are actual data from the Florida Department of Corrections.

ii. Variables 1. ID

a. This is an inmate identification number 2. Days

a. This is the number of days between release from prison and return to prison. You can divide this by 365 days to look at the number of years if interested.

3. Tattoo a. This indicates whether or not an inmate has a tattoo. Tattoo

= 1 if an inmate has a tattoo, and tattoo=0 if an inmate does not have a tattoo.

2. Step 2: I’ve titled two new columns, one titled, “Days if No Tattoo”, one titled, “Days if Tattoo”. These columns will separate inmates into two columns, listing the number of days depending on whether or not they have a tattoo.

a. First, you want to create a formula that separates the data in columns A, B, and C into the columns E and F, titled “Days if No Tattoo” and “Days if Tattoo” respectively. To type a formula into excel first type “=” to indicate you are telling it to execute a command. Start with the “Days if No Tattoo Column”. You want to tell excel that if an inmate has no tattoo (so if Tattoo=0), to report the number of days in the cell you are writing in, and if not to leave the cell blank (you can tell excel to leave something blank by typing “”). To do this, use what is called an IF statement.

i. First, type = in the cell below “Days if No Tattoo” to indicate you will use an excel command.

ii. Second, type IF( , to indicate you want to use Excel’s if function. It has three parts: logical test, value if true, and value if false. The first part you use to tell excel what you are looking for, in this case you are looking for inmates without tattoos, i.e. tattoo=0.

iii. Third, after the IF( ,click on the cell directly below “Tattoo” as this is the cell you want excel to look at and ‘test’. But what do you want to test,

you want to see whether tattoo (in the cell you clicked) =0. So after, IF( click the cell directly below Tattoo and then type =0. This is saying, if tattoo= 0 (so if an inmate has no tattoo)

iv. Next type a comma to move onto the next part of the statement “value if true” which is what you want excel to put there if the tattoo=0 is true. So if an inmate has no tattoo, you want excel to report the number of days from column B, thus select the number directly below “Days” in column B.

v. Again type a comma, which takes you to value if false. So if the inmate does have a tattoo (i.e. tattoo=0 is false), you want the cell to be blank because you are only trying to get “Days if no tattoo” in this column. To indicate that if the value is false the cell should be blank, excel denotes ‘blank’ as “”. IF YOU DO NOT COMPLETE THIS STEP YOU WILL GET ZEROS AND NOT BLANKS WHICH WILL CHANGE THE CALCULATIONS YOU DO LATER.

1. Only inmates without tattoos should have days showing up in the “Days if no tattoo” column. Similarly, only inmates with tattoos should have days showing up in the “Days if Tattoo” column.

vi. Type a closed parenthesis. If you did this correctly, the number of days should appear for the first inmate in the new column you created.

3. Step 3: Follow the steps in step 2 for the cell directly below, “Days if Tattoo”, instead this time you want to see when tattoo=1 as the logical test for the if formula. Once completed the cell for the first inmate should be blank, for this column.

4. Step 4: Copy the cells right below “days if no tattoo” and “days if tattoo” down to the last inmate. To do this, click your mouse on the first cell and drag until both cells are highlighted with a black outline. There will be a black square at the bottom right corner. Move your mouse on this square until it becomes a plus sign. When it does, click and drag down until the last inmate.

5. Step 5: Calculate the mean, median, mode, variance, and standard deviation number of days for (a) inmates with tattoos and (b) inmates without tattoos. Do this in the cells I have labeled so that I can see your calculations for each.

a. The formula for mean is, =average( ), where the cells you want to take the average of are inside ( ). So after typing =average( , select the beginning cell you want to use and drag your mouse down selecting the other adjacent cells you want as well. When all the cells are highlighted close the parenthesis )

b. The formula for median is, =median( ) c. The formula for mode is, =mode( ) d. The formula for variance is, =var.s( )

i. Where the s indicates sample variance since we are not looking at all the inmates in FL.

e. The formula for standard deviation is, =stdev.s( ) i. Again the s indicates sample.

6. Step 6: You should now have a total of ten numbers: two means (days with and without tattoos), two medians, two modes, two variances, and two standard deviations. Please create a word document with six or more sentences.

a. In the first sentence compare the mean number of days of inmates with and without tattoos and explain what it means.

i. Ex: Inmates with tattoos have a lower average number of days meaning they return to prison faster than non-tattooed inmates since they have fewer days out in society.

b. In the second sentence compare the median number of days of inmates with and without tattoos and explain what it means.

c. In the third sentence, compare the mode number of days with and without tattoos and explain what it means.

d. In the fourth sentence, compare the variance of days for inmates with tattoos to the variance of inmates without tattoos. Explain what this means.

e. In the fifth sentence, compare the standard deviation of days for inmates with tattoos to the standard deviation of days to inmates without tattoos and explain what it means.

f. In the sixth sentence, tell me what number of days (range of days) 95% of the values fall between. Use the empirical rule and your calculation of standard deviation to figure this out.

g. Take more sentences if you need to. You need to show me you understand what differences in these measures means.

7. Step 7: Submit your assignment to eCampus by 11:59 PM EST Monday September 22nd.