Barrier Data Analysis

GETTING STARTED

 

For this activity, you are going to analyze hypothetical data from a Barriers to Research Utilization by Nurses survey using descriptive statistics. This data has been provided for you, and step-by-step instructions on doing the analysis in excel are provided below.

 

BACKGROUND

 

When collecting online survey data, the responses are generally textual data.  Look at the Data_Raw worksheet to see the survey results.  This raw data must be converted to numerical data for analysis.

 

To accomplish this, a Codebook is created that contains the data and metadata (the information for each variable with coding instructions). For this activity, the Barriers Codebook has already been created for you.  Review the Metadata worksheet to see an overview of the variables in this survey (column A), variable type (column B), level of measurement (column C), coding instructions (Response Categories, column D), and the original Survey Question (column E).

 

Look to see how the data have been numerically coded for you on the Data_Coded worksheet. This was done by copying the Data_Raw worksheet, then using Find and Replace to code the responses numerically using the MetaData worksheet information. For example, in column D (Race), White was replaced with 1, African American was replaced with 2, Asian was replaced with 3 and Other was replaced with 4.

 

Now the original data has been coded, you want to retain the coded data to preserve it for additional analyses or if errors are made.

 

Note: These instructions use right-click to access the right-click menu. If unsure how to right-click on your computer, reach out to Student Success Center, IT services, or your course instructor. For example, Ctrl/click acts as a right-click on a Mac.

 

 

CREATE YOUR OWN DATA FILE COPY

 

This is where the real fun starts for this activity!

 

You will first copy the Data-Coded worksheet to a working file that you can use for this activity.

 

Desktop Excel Web Excel
Copy the Data_Coded worksheet by right-clicking on the Data_Coded worksheet, selecting Move or Copy (see below) and checking the Create a Copy box.

 

 

Copy the Data_Coded worksheet by right-clicking on the Data_Coded worksheet, selecting Duplicate (see below).

 

 

   

 

 

Rename this copied worksheet Desc_Stats by right-clicking on the tab of the copied worksheet and selecting Rename and typing the new name.

Right-click again, choose Tab Color, and choose a different color than red (red is a reminder to not use these worksheets).

 

Next, click on the Desc_Stats worksheet and write a formula to total the scores on the Barriers Survey for each participant. The variable you will use for this is the Tot_Scores continuous variable in column AK. In column AK1, type the heading Tot_Scores. Next, enter this formula into cell AK2:  =Sum(H2:AJ2) directly in the cell or the formula bar at the top, as shown.  This formula will sum all the cells in Row 2 from Column H through Column AJ, which include all the “Q” items on the survey.

 

Make sure you see the formula bar displayed in this screenshot.  If it is not there, click on the Formulas menu and check Show Formulas.

 

 

 

To avoid having to write this formula 75 more times, Autofill the formula by holding your mouse over the small black square in the bottom right corner of cell AK2. When your mouse pointer turns into a plus sign, click and pull the plus sign down through row 77. This autofills the formula and calculates the total score, while, adjusting the formula for each row.

 

 

DESCRIBING THE DATA

 

Continuous Variable Analysis

 

For continuous variables (“Yrs_RN”, and “Tot_Scores”) you will compute measures of central tendency (Mean/Average, Median, and Mode) and measures of dispersion (standard deviation, minimum, maximum, and range). See the Continuous Summary (Example) worksheet in the Codebook for an explanation of the statistical tests you will use to analyze continuous variables, what it measures, and a sentence explaining the results.

 

The following table contains the formulas for these statistical tests used to describe your continuous data.

 

Row Stat Yrs_RN Tot_Scores
78 MEAN =AVERAGE(E2:E77) =AVERAGE(AK2:AK77)
79 MEDIAN =MEDIAN(E2:E77) =MEDIAN(AK2:AK77)
80 MODE =MODE(E2:E77) =MODE(AK2:AK77)
81 STANDARD DEVIATION =STDEV(E2:E77) =STDEV(AK2:AK77)
82 MINIMUM =MIN(E2:E77) =MIN(AK2:AK77)
83 MAXIMUM =MAX(E2:E77) =MAX(AK2:AK77)
84 RANGE =E83-E82 =AK83-AK82
85 SUM =SUM(E2:E77) =SUM(AK2:AK77)
86 COUNT OF RECORDS (N) =COUNT(E2:E77) =Count(AK2:AK77)

 

 

 

 

Click on the Desc_Stats worksheet.Enter the formulas shown above at the bottom of the columns of data for Yrs_RN and Tot_Scores in the rows indicated in the table above and then hit Enter. Remember all cell references must be enclosed with parentheses. Transfer your results to the Summary Yrs_RN and SummaryTot_Scores worksheets.

 

Desktop Excel Web Excel
To confirm that your formulas are correct, view the formulas in your worksheet by using the instructions below.

 

Hiding and protecting formulas is currently not supported in Excel for the web.

 

To see the formula, click on the cell (such as E78, and view the formula on the top row.

 

 

 

 

 

 

Next, write a summary interpretation for the variable analysis you did on Yrs_RN and Tot_Scores on the Summary_YrsRN and Summary_TotScores worksheets.  Your summary should be similar to the one on the Continuous Summary (Example) worksheet where the variable Miles driven to work was analyzed (this is not in your dataset; it is just an example).

 

 

 

Great Job!

 

This completes the first two pieces of evidence for this CPE.  Now you need to capture screenshots of both of these worksheets and save them for uploading to your e-portfolio.

 

To capture screenshots:

  • Mac:Press Shift+Command+4. Drag the crosshairs to select the area of the screen you want to capture. After you release your mouse or trackpad button, find the screenshot on your desktop.  Rename your screenshot to Summary Yrs_RN and Summary Tot_Scores so you remember what screenshots to upload to your e-portfolio.
  • PC:Follow the “Use Snipping Tool to capture screenshots directions” to use the Microsoft Snipping Tool to capture, save, and share an image of all or part of your PC screen. The Snipping Tool is included in Windows Vista and later. Save your screenshots and name them BarChart Summary Yrs_RN and Summary Tot_Scores so you remember what screenshots to upload to your e-portfolio

 

Categorical-Nominal Variable Analysis

 

The best approach for analyzing categorical-nominal variables is to compute “frequencies” (counts) for each type of response.

 

Your Codebook indicates the coding values for each response, which you will need for your formula. For example, to determine how many respondents were in the first age group (1=19-29 years), you will use the COUNTIF function to COUNT the cells in a range IF it has the number 1 in it.

 

To do this, click on the Desc_Stats worksheet, then click in cell B78 and enter this formula in the cell or formula bar:  =COUNTIF(B$2:B$77,1). Hit Enter immediately after entering this formula. The dollar signs next to the row references will make it possible to autofill this formula down by keeping rows 2 through 77 constant and simply changing the response code for each response type. For example, note on your Metadata worksheet that Age_Group has three categories, so you will need to autofill this formula through rows 79 and 80 and change the IF condition to =COUNTIF(B$2:B$77,2) in row 79 and =COUNTIF(B$2:B$77,3) in row 80. If you don’t use the $ in front of the row number, Excel will automatically adjust the formula to the next row, which is incorrect.  There is NO dollar sign in front of the B, as you will want Excel to auto-adjust across columns, but not rows.

 

Note: You will format the numbers in the cells in the next section, which will fix percent and/or decimal points.

 

 

 

 

Next, you should Autofill your formulas across columns C and D. You will need to skip column E and copy/paste this formula into cell F78 and autofill to column G.

 

Desktop Excel Web Excel
When you are done autofilling down and across, click on the Formula Menu and check Show Formulas. Your formulas should match the ones shown below for your categorical-nominal variables:

 

 

When you are done autofilling down and across, click on each cell check the formulas. Your formulas should match the ones shown below for your categorical-nominal variables:

 

 

 

 

 

 

 

 

Because these are counts, format your categorical-nominal results as whole numbers, without decimal places or percent.

 

Desktop Excel Web Excel
First, select all cells you want to format by selecting cells B78 through D78 and then pressing the CTRL key (PC) or Command key (Mac) while selecting cells F78 through G78. (The CTRL key (PC) or Command key (Mac) is used when selecting cells that are not contiguous.  The Shift Key is used when all cells are next to each other.)

 

PC: On your Home menu, click on Format > Format cells.

 

 

Mac: Right click while in the highlighted cell block, click Format cells, and then select Number Format.

 

 

In the Format Cells dialog box, click Number and set Decimal to “0”.

 

For your continuous variables you should make sure there is only one decimal point.

 

First, select the first set of cells you want to format by selecting cells B78 through D81. (The CTRL/Shift keys do not work in Web Excel

 

Right click while in that cell block, and select Number Format.

 

In the Number Format dialog box, click Number and set Decimal to “0”.

 

For your continuous variables you should make sure there is only one decimal point.

 

 

Click the ‘Show Formulas’ again to see the numbers (instead of the formulas).

 

Select the second set of cells you want to format by selecting cells F78 through G80.

Right click, Select Number Format.

 

In the Number Format dialog box, click Number and set Decimal to “0”.

 

 

 

Analyze the frequencies of your categorical-nominal demographic data (e.g., age, sex, race, practice setting, and educational level).

 

After completing your analysis of the categorical-nominal demographic variables, click on the Summary_Demographics worksheet and write a summary description of the demographics of this sample population. (Hint: Select the meta-data worksheet to be reminded of how the data is coded.  E.g., Age_Grp is coded as 1=19-39 yrs, 2=40-59 yrs; 3=60 and older).

 

 

 

Great Job!

 

This completes the next piece of evidence for this CPE. Now you need to capture a screenshot of this worksheet, name the file to be Summary_Demographics and save the screenshot for uploading to your e-portfolio.

 

 

Categorical-Ordinal Variable Analysis

 

When analyzing Categorical-Ordinal data, the MODE is the best choice, as it indicates the value that was reported most often for each of the Barriers Survey questions.

 

To do this, click on the Desc_Stats worksheet, then in cell H78 enter this formula =MODE(H2:H77) and hit Enter. Click back in cell H78 and Autofill this formula across all “Q” questions ending in cell AJ78. Your formulas should look like the ones below:

 

After you have determined the Mode for each question on the survey, write a summary interpretation of the questions that scored high (4) and those that scored low (1) on the Summary_Responses worksheet.

 

High scores indicate that the items are barriers to research utilization to a great extent and low scores indicate that to no extent is the item a barrier. For example, if the mode is 4 for Q1, you would conclude that the majority of respondents determined that nurses did not utilize research To great extent due to, “Research reports/articles are not readily available”.

 

Great Job!

 

This completes the next piece of evidence for this CPE. Now you need to capture a screenshot of this worksheet, name the file to be Summary_Responses and save the screenshot for uploading to your e-portfolio.

 

 

Pivot Tables

 

Your next analysis involves creating a matrix with counts of respondents using the Pivot Table function. For this activity you will look at the educational level of nurses by work setting and the age of participants by race category.

 

You will use your Data_Raw worksheet for your Pivot Tables, so the correct labels will appear as column and row headings. Select the part of your worksheet that you will pull your fields from, which can be done quickly by clicking in cell A1, pressing your shift-key and clicking on G77.

 

 

Next, select Insert > PivotTable.

Under Choose the data that you want to analyze, confirm that the correct worksheet and range is showing in the Select a table or range box and that the New Worksheet radio button is checked, which will place your pivot table on a new worksheet. Select OK.

This is similar to how your new Worksheet will look:

In the Pivot Tables Field box on the right, click on the field name and drag these to the boxes at the bottom. If you check the box next to the field, instead of dragging and dropping the fields, it will automatically place it, which is not always correct. Once you drag and drop, a mark will occur in the checkbox next to the field.  Drag Rec_ID to the Values box, Age_Grp to the Columns box, and Race to the Rows box.

 

You will have to change the function in the values box, as you need to Count the number of participants in each group, not Sum.

PC: Click on the small arrow next to Sum of Rec ID and select Value Field Settings.

From the Value Field Settings box, select Count from the Summarize value field by box. Click OK.

 

Mac:  To do this, click on the small i next to Sum of Rec ID and select Summarize by and Count, then click OK.

 

 

Pivot_Age

 

The output for Age by Race is shown below.  Right-click and Rename your worksheet Pivot_Age-Race.  Right-click again, choose Tab Color, and choose a different color than red (red is a reminder to not use these worksheets).

Below the table, create a box by selecting several cells and then clicking on Merge and Center on your Home tab.

In this box you created, include a Summary Interpretation of the findings from your pivot table.

When complete, capture a screenshot of this pivot table, name the file to be Pivot Table Age Group by Race and save the screenshot for uploading to your e-portfolio.

Terrific!  You have one more Pivot Table to go!

Follow the same steps to create a Pivot Table and Summary Interpretation for Education Level by Work Setting.  Choose the appropriate fields and have the Pivot Table count the number of records (Rec_ID) to determine how many are in each category.

If you put Ed_Level as the rows and Setting as columns, your Pivot Table should look like this:

 

If you put Settings as the rows and Ed_Level as columns, your Pivot Table would look like this:

Below the table, create a box by selecting several cells and then clicking on Merge and Center on your Home tab. In this box, include a Summary Interpretation of the findings from your pivot table.

Rename your worksheet Pivot_EdxWrk.  Right-click again, choose Tab Color, and choose a different color than red (red is a reminder to not use these worksheets).

When complete, capture a screenshot of this pivot table and interpretation, name the file to be Pivot Table Education Level by Work Setting and save the screenshot for uploading to your e-portfolio.

Conclusion

Congratulations!  Using these instructions, you have just analyzed a dataset!  You saw the raw data, saw how it was coded into numbers for analysis, then you conducted the analysis yourself by using descriptive statistics and pivot tables.

This activity used hypothetical data from a Barriers to Research Utilization by Nurses survey, but we hope that you can see how this same technique can be used for any kind of survey that you conduct in the future. Save these instructions for later use in your professional career.

 

Follow the instructions in your CPE record to upload the screenshots you saved during this activity, and then continue to Phase 3: Data Visualization.