real time survey_long method

The long method for surveying in real time Yuqi Wang, Innovation Network AEA 2015, Chicago, IL

Overview

Figure 1 is an example of what a finished spreadsheet for collecting and analyzing real time survey data could look like. This document shows the step-by-step process for organizing a spreadsheet in advance to collect and analyze real time survey data. Although this document uses only one survey question as an example for how to collect and analyze data, the process can be replicated for any number of survey questions.

Example dataset used

This process is divided into three main sections: organizing the spreadsheet for data entry, organizing the spreadsheet for data analysis, and organizing the spreadsheet to create graphs. Each section also contains subsections with steps for entering excel formulas that will help you expedite the data collection and analysis process.

The sample survey question we are going to use throughout this document asks for an opinion about Organization X’s funding: “There is sufficient funding to support Organization X’s programs”, and the responses for survey participants to choose from is a Likert scale: “Strongly disagree”, “Disagree”, “Neither disagree nor agree”, “Agree”, and “Strongly agree”. We are going to suppose that the number of anticipated survey participants is 10.

Organizing the spreadsheet for data entry

1. Create two columns in your excel sheet to keep track of the number of survey respondents (column A), and the question number and the question (column B). Create a third column if your closed ended responses are qualitative (like a Likert scale) and you want to be able to quantify the responses (e.g. 1, 2, 3, 4, 5), such as “Strongly disagree”=1, “Disagree”=2, “Neither disagree nor agree”=3, “Agree”=4, “Strongly agree”=5.

1

Figure 1Figure 1Figure 1Figure 1Figure 1Figure 1Figure 1Figure 1


Quantifying qualitative responses is helpful when you want to know what the average survey respondent’s or group’s opinion is for a specific question. In Figure 2 this third column used to quantify responses is titled “Q-revised” (column C).

2. If you know the exact number of survey respondents you’ll have, you can begin to populate the first column.

If you don’t know the exact number of survey respondents, estimate how many respondents you will get, and populate the first column up to that estimated number. Make sure to leave enough blank rows in each of your columns in case there are additional respondents.

Using data validation to create a drop-down menu3. Data entry in real time can be stressful, and having to manually enter or type data is time consuming

and may increase chances of human error. To reduce the chance of an error, make sure to prepopulate all of the answer choices for each question in your survey so that during data entry all you do is choose the correct answer instead of typing it. The easiest way to do this is by creating a drop down menu of all possible answer choices for each question in your survey.

To create a drop down menu, insert a row at the top of your spreadsheet, and enter all possible answer choices for your question. Using our example, the Likert scale is entered in the first row (Figure 3). Once you have done so, click on the “Data” tab at the top of the excel spreadsheet. Find the icon that says “Data Validation”, then click on the data validation option in the drop down menu.

2



A task menu called “Data Validation” will pop up (Figure 4). Under “Allow”, select “List” and under “Source”, highlight the responses you entered in the top row along with an additional blank cell. The blank cell is there so that during data entry you can differentiate between cells that already have an answer, and ones that do not. Click “OK” when you’re done. Repeat step 3 if you want to create more drop-down menus with different answer responses for other questions.

4. The cells you highlighted should now show an arrow box when you click on them again – this signifies your cell is formatted as a drop down menu (Figure 5). Select and copy the blank cell that you just highlighted in your top row, and paste it in to each of the cells in column B where you need to have the drop-down menu.

3

Figure 4


Figure 4Figure 4Figure 4Figure 4Figure 4Figure 4Figure 4


You can always hide the top row of scale choices afterwards to avoid distraction. Select the entire row, right click, and choose “Hide.”

Quantifying qualitative responses5. Quantifying qualitative responses is helpful when you want to know what the average survey

respondent’s or group’s opinion is for a specific question. In the example dataset we are trying to quantify “Strongly disagree” to a 1, “Disagree” to a 2, “Neither disagree nor agree” to a 3, “Agree” to a 4, and “Strongly agree” to a 5.

To quantify one qualitative response, we use an IF command: =IF([logical test], [value if true]). Because we don’t know what the survey respondents’ answers are going to be ahead of time, this formula will make sure that each cell will be able to quantify the response into the correct number no matter what the response is. To do this all we need to do is string the IF commands of all possible answer choices for a particular question together with a plus sign (+):

=IF([excel cell]="strongly disagree",1)+ IF( [excel cell]="disagree",2)+IF([excel cell]="neither disagree nor agree",3)+IF([excel cell]="gree",4)+IF([excel cell]="strongly agree",5)

Locate the third column you created for quantifying qualitative responses. In the example this third column is column C. In the first blank cell of column C, copy and paste the above formula into the cell (Figure 6). Replace [excel cell] with the cell in column B that will contain the qualitative response you are trying to quantify.

4



6. Once you have the hang of creating that formula, copy and paste it into the rest of the cells in column C. You will know if the formula works if you choose an answer from the drop-down menu in the second column, and its corresponding number shows in the third column.

Organizing the spreadsheet for data analysis

7. Scroll down in your excel sheet to a blank area where you can create a new section to keep track of the number of people who selected a particular answer choice for each question. Enter the header “Frequency counts” in a new row underneath column A. In column B type all answer choices available to your survey respondents for the particular question (Figure 7).

Scroll further down the sheet if you are quantifying your qualitative responses. Again in column A in a new row, enter the header “Data summary”, and in column B enter “Total number of responses”, “Minimum number”, “Maximum number”, and “Average” (Figure 7).

Entering formulas for frequency

8. To calculate the frequency of a particular answer choice to a question, we need to use the COUNTIF formula: =COUNTIF(range, criteria).

5



In the green outlined cell (C17) in Figure 8, the formula to count the frequency of “1” is: =COUNTIF($C$4:$C$13,1). This formula is telling excel to search, and count all of the cells from C4 to C13 that contains the number 1. The dollar signs in front of the C and 4 tells Excel to lock in the column (C) and row (4) to keep them from moving when you copy and paste the formula into other cells.

There may be situations where you want to know frequency counts without quantifying your answer responses. In this case, you can use the formula shown in Figure 9: =COUNTIF([range of cells you want excel to search in], “answer choice”).

Entering formulas for data summary

9. The data summary section is created using information from the frequency and survey data section. The functions you can use are pretty straight forward: SUM to calculate total number of responses, MIN to calculate minimum, MAX to calculate maximum, and AVERAGE to calculate average

6



The range of cells we need to calculate the sum is all of the cells under our frequency section (Figure 10).

For those who quantified their qualitative responses, the range of cells we need to calculate minimum, maximum, and average is all the cells in column C we created earlier that quantified our qualitative responses (Figure 11).

7

=MAX($C$4:$C$13)

Figure 10


Organizing the spreadsheet to create graphs

10. If you want to take your skills one step further, you can create charts in advance. The charts will then populate itself with the information you want as you enter the data.

To create a chart showing your data summary or question frequency, all you need to do is highlight the cells in the data summary or frequency section, and insert the desired chart type. Then you can format the chart size and colors to your preference.

Another way to display your data is to graph the percentage of people who responded to each of the answer responses in a particular question. To do this, find a blank area under “Data summary”. Create a new section titled “Charts” where you can populate percentages for each answer response. You can use this formula to obtain percentages: =[the frequency of a particular answer response]/[total responses] (Figure 12).

For example, to calculate the percentage of people who responded “Strongly disagreed” (B35 in Figure 12) the formula would be: =[frequency of people who responded “Strongly disagree”]/[total number of responses]. Repeat this formula for the rest of the answer choices. When you are complete, highlight your formulas and insert the desired chart type. Lastly, play around with the size and color of the chart until you are satisfied.

8



Happy evaluating!

9

real time survey_long method

Data & Analytics