Choosingwhich Computer Software to Use

In this activity, you will download a spreadsheet and open it in Excel (most of you should have it installed on your computer already) or Google Sheets (free in any browser) or any other program that you'd like to use that can do these things (you might have Minitab, for example, and know that it can do the job easy as pie). Hey, if you wanted to, you could work out all the answers with pencil and paper. There are many paths to the right answer, and part of the assignment, step one, is for you to decide how you want to approach it. Naturally the easiest thing to do is to choose the program that you're most familiar with and go from there. The instructions are going to be written for Google Sheets, because Excel looks a bit different on different versions (Mac vs. PC, different years), so if you have no clue what to do, choose Google Sheets. How this works is pretty similar for all versions of Excel and Google Sheets, and any time there's a difference, you can just do a quick web search for "how to find and replace in Excel on a Mac" or whatever, and there are all the instructions you need right at your fingertips.

Before we go further--here's a bit of advice for those of you who "don't like computers because I'm so bad at it". Say a little prayer or whatever wishing for the strength to fake it until you make it. Pop an imaginary placebo pill that's filled with computer power. Because guess what--most people who are good at computers are just exactly the same as you except they just work a little harder searching for answers instead of giving up, and a little determination is all you need to close the gap.

Downloading the Data

Data Analysis Activity 1.xlsx

Click the file above, and it should download to your computer. On most computers, just clicking on it will open it in Excel. If you want to open it in Google Sheets, then go to drive.google.com (you might need to set up a Google account or log in with your Gmail account to get in) then upload this file into Google drive, then click on it and choose Open with/Sheets.

(If any of those steps gets you stuck, remember that you can solve it by typing a question into the search bar: "how to open an excel spreadsheet in Google sheets" and there will be videos and step-by-step screen shots and any level of detailed instructions that you would like.)

Once you've got the data open, you should be able to see that there are several columns of information with a label at the top (a survey question), and each row is a person's responses. So in row 1 are the questions, in row 2 are one person's answers, in row 3 are another person's answers, and so on.

Recoding and Cleaning the Data

Changing data from one format into a new code is called "recoding". Once the data is open, to do some of the required tasks, we need to change some of the data from words into numbers.

For the variable titled "How many drinks do you usually have on a night out?" we are going to change the answer "None" to 0, we will change 1-2 drinks to1.5, 3-4 drinks will be set at 3.5, and 5+ drinks will be coded as 5.5.

How can we do that? Well a slow way is to just write over it in each cell (deleting never and writing in a zero). A faster way is to click Edit and then click Find and Replace, and then in the window that pops up, we put "None" in the Find box and "0" (that's a zero, not an O) in the Replace box. Then click Replace All, and there we go, all the Nones turned into zeros and now align right (as is customary for numbers in these spreadsheets).

Next, you need to do another find and replace, and this time replace "1-2 drinks" with "1.5" (you don't type in the quotation marks, just what's inside). Go on from there following the code for 3.5 and 5.5. When you're done with that variable, it should all be numbers aligned to the right.

How about the next variable?For the question, "Do you ever feel pressured to drink?" the answers are currently:Never, Sometimes, Most of the Time, Almost Always, and Always. Instead, we want to turn this into a score where never gets 0 points, Sometimes gets 1, and so on, counting up by one for each level from 0 to a maximum of 4.Use find and replace. It helps to check the option for matching the entire cell contents so you don't get Almost Always turning into Almost 4 or something like that.

Forthe variable titled "How many days per week do you usually drink alcohol?" we only have two tiny little issues to fix before all the answers are numeric. We are going to find the midpoint again for the range we got, so just manually go into the cell where it says 3 or 4, delete that, and write in 3.5. Also, "2 nights" should be changed to 2.

For the how you met your friends variable, no recoding is necessary for this activity.

Once the data is coded and cleaned as described above, you're ready to start doing calculations to answer some questions.

Instructions for each step are part of the questions that follow.

What is the average number of drinks per night in this group? Round off your answer to the nearest drink.

One way to get the answer is to go down into cell A34 and type in "=average(A2:A32)" and then hit enter ... again, don't type the quotation marks, just what's inside.

Group of answer choices

1 drink per night

2 drinks per night

3 drinks per night

4 drinks per night

What percentage of students replied that they feel pressured to drink "Most of the time"?

There aren't very many, so just count them. Remember that "Most of the time"was coded as 2 and a percentage is gotten by taking the number of interest divided by the total number possible, then multiplied by 100. Note that if you wanted to use an empty excel file as a calculator to divide some number, you can type "=8/10" and it will do the fraction for you. Multiply by 100 in your head by just moving the decimal two spaces to the right. Round to the nearest percent.

Group of answer choices

3%

6%

9%

12%

Concept question about scales: for the pressure to drink variable that was a multiple choice in words and got turned into a numerical score, what type of recoding have we done? Choose the answer that first describes the original scale, then describes the new scale.

Group of answer choices

Nominal categorical scale recoded to quantitative scale

Ordinal categorical scale recoded to quantitative scale

Categorical scale recoded to nominal quantitative scale

Quantitative scale recoded to nominal categorical scale

What's the average of the pressure to drink score? Round to the nearest tenths place. To calculate it, you can again do it with the equals average parentheses range thing again. This time when you choose a blank cell to put the equation into and write the equation, you need to tell it that the data you want isn't column A from row 2 to 32, instead it's column B from row 2 to 32, which is written like this: =average(B2:B32)

Group of answer choices

0.8

1.0

1.2

1.4

What's the average number of days per week this sample goes out drinking? Round to the nearest tenths place again.

Group of answer choices

0.4 nights per week

0.9 nights per week

1.6 nights per week

2.1 nights per week

How many people met their friends that they usually go out with through their roommates?

One way is to just count, it doesn't take long with a tiny dataset like this.

If you're adventurous and want to learn a new technique, try to get counts for them all with a Pivot Table. Highlight all the data for this friends variable (drag your mouse or hit shift while scrolling with the up down arrows or just click the letter D in the column label). Then on the menu up top click Data and click the option called Pivot Table. Then where it says Rows - Add field, click on "How did you meet your friends that you usually go out with?" And then where it says Values - Add field, again click the same variable name. At first it says "Summarize by SUM", but if you click into the menu beside the word SUMyou can change it to COUNTA and then the table on the left will give you the number of each (which is a beautiful thing when this saves you from counting 100 or 1000 or more responses.

Group of answer choices

2

5

15

48

What's themost appropriate statistic to calculate for the how you met your friends variable?

Group of answer choices

Mean

Median

Mode

All equally valuable for this variable

https://psu.instructure.com/courses/2280851/files/150930668/download?verifier=meDI9bS6TwVTxEV9mhl3keh6IAwPetAFONqB5f3l&wrap=1 :) How many drinks do you usually have on a night out? Do you ever feel pressured to drink? How many days per week do you usually drink alcohol? How did you meet your friends (that you usually go out with)? 3-4 drinks Sometimes 1 Greek life 3-4 drinks Sometimes 2 Roommates 3-4 drinks Never 1 Greek life 5+ drinks Sometimes 3 Athletic team 5+ drinks Never 1 Athletic team 3-4 drinks Never 2 Athletic team 5+ drinks Never 2 Roommates 5+ drinks Never 3 Greek life 1-2 drinks Sometimes 0 Roommates 3-4 drinks Never 1 Greek life 5+ drinks Sometimes 1 Roommates 1-2 drinks Sometimes 0 Roommates 1-2 drinks Sometimes 7 At a party/bar None Most of the time 0 Roommates 1-2 drinks Sometimes 1 Greek life None Sometimes 0 Athletic team None Sometimes 0 Roommates 5+ drinks Always 3 or 4 Athletic team None Never 0 Roommates 3-4 drinks Sometimes 2 nights At a party/bar 5+ drinks Sometimes 3 At a party/bar 5+ drinks Never 3 Athletic team 3-4 drinks Never 1 Greek life 3-4 drinks Never 2 Roommates 3-4 drinks Never 2 Roommates 1-2 drinks Sometimes 0 Roommates None Never 0 Roommates 3-4 drinks Never 0 Roommates 1-2 drinks Almost always 1 Roommates 3-4 drinks Never 2 Athletic team 3-4 drinks