Friday, November 30, 2012

Converting Survey Range Data into Averages

Conventional and unconventional uses
It’s one of those things I know I was never taught.  I, like I’m sure many others, became dependent on research software tools to calculate the averages of range questions for me.  As I’ve discovered, we can’t always rely on such crutches, especially if you are conducting primary studies or analyses.
The great thing that I’ve discovered is anything that has a numerical range can produce an average metric.  Here are the six steps to calculating range averages:
1)      Collect data from ranges measured.
Note: of those that responded or qualify for the analysis.  See Exhibit A for an income range example and a coupon usage example.
See steps 1a-1c for the next step on unconventional ranges.
2)      Assign values for the lowest and highest ends of each range.
Note on the last range captured: Some companies (research included) are ultra conservative with the assignment to the highest end of the last range and opt to use the same value represented in the lowest end.  I think this is too conservative.  Others arbitrarily choose a high end value and one that is possibly too high which can skew results higher than may actually exist.  I opt for replicating the same range variance used in the previous (completed) range.  In Exhibit B, I matched the size of the last range with the previous range used.  This is still conservative, but not ultra conservative.  Whichever method you choose, just be sure to maintain the same method you analyses remain consistent.
3)      Calculate the average for each assigned range.
See Exhibit C for updates in averages.
4)      Calculate the total occurrences for each range.
[(Average) X (Respondents, households, or adults)]
See Exhibit D for updates in total occurrences.
5)      Sum the total household count and total occurrence columns.
6)      And calculate the average.
[(Total occurrences) ÷ (Total respondents, households, or adults)]
See Exhibit E for updates the Steps 5 and 6.
*For oddly defined or unconventional ranges:
1a) Identify the widest measurement period available (WMP).
Example:  if days, weeks, and months are used, months will be your common and widest measurement period; if hours and days are used, you’ll use days as the WMP.
1b) Convert the ranges into the common WMP.
Example:  3 or more times per week = 12 or more times per month.
1c) Conversion may not be perfect.
All the ranges may not be perfect, but if you maintain the same method spanning all your analyses and trending, the context will be consistent and should help in identifying shifts, trends and differences in your analysis.