Directions for Analysis using Microsoft Excel in Exercise 4

How many community informants were not willing to be interviewed? How many community informants were too young to be interviewed? How many community informants were both old enough to be interviewed and willing to be interviewed?

For this question, you will need to look at "Community Informant Willing to Respond", question A8 in column I. Select the entire column I.

Click on the data menu and chose "sort". A pop-up box will appear asking if you want to expand your selection. Choose the "expand the selection" option. A box entitled "Sort" will appear with "Community Informant Willing to Respond" selected under "Sort by". Make sure to indicate that your list has a header row. Then click "OK".

The entire dataset will now be sorted by "Community Informant Willing to Respond" in ascending order. Scroll to the bottom of the dataset and you can see how many community informants were successfully interviewed by counting the number of rows with "Community Informant Willing to Respond" equal to "1" (see Community Informant Questionnaire). (Look at the left hand dataset column which counts the number of rows; subtract one for the header row.) Now you can also easily count how many community informants were not willing to be interviewed and how many were too young.

Make a table or pie-chart showing the distribution of the different types of community informants interviewed.

Sort by type of community informant; then count how many types there are of each.

Which two types of community informants were interviewed most often in the Dock and Warehouse District? How many interviews were there with each of these two types of people in the Dock and Warehouse District?

For this question, you will need to sort by "Location of Interview" and "Community Informant Code". Select the entire dataset by clicking in the upper right corner. Then go to the data menu and select sort. When the sort pop-up box appears, make sure to indicate that the dataset has a header row.

In the white box under "Sort by", choose "Location of Interview" from the pull-down menu. In the white box under "Then by", choose "Community Informant Code". Then click "OK".

Scroll to the Dock and Warehouse District informants which are now grouped together. (See the Community Informant Questionnaire to determine which code stands for Dock and Warehouse District.) You can now easily determine which types of informants were most frequent in this area.

What was the greatest number of places where people go to meet new sexual partners named by a single informant? What type of community informant named the greatest number of places?

Follow the directions above for sorting by one variable, as you sort by the number of places named inside the PPA. You can then easily determine which value is the greatest and the type of informant who reported this number of sites.

Use the "Site Report" dataset to answer the following questions.

How many unique sites were reported?

Count the number of rows in the dataset; there is one row for every unique site that was reported (don't count header row, though!)

Which site was reported most often? Follow the directions above for sorting by one variable, as you sort by the number of community informants who named each site. You can then easily determine which value is the greatest.

Make a table or pie-chart showing the distribution of the different types of sites reported. Each site should contribute only one number to the table or pie-chart.

To make a table, follow the directions above for sorting by one variable, as you sort by type of site. Then count how many there are of each. The chart in the answer-key was created using the chart function in PowerPoint; instructions are not provided here for making a pie-chart.

Which three types of sites were reported most often? How often were these sites reported, and what percentage of the total sites fell into these three categories?

You will be able to answer this question when the data is sorted by site type.