Analyze Survey Results with the FREQUENCY Function

Surveys that ask you to rate something on a scale of 1-5 or 1-7 or 1-10 are extremely common. If you need to summarize the results of such a survey quickly, it is very simple to use Excel's FREQUENCY(data_array,bins_array) function to do this.
Below is an example where the ratings are integers between 1 and 5. To use the frequency function, follow these 3 steps:

1) Select cells A1:A5

2) Enter the following formula in cell A1
=FREQUENCY({1,1,1,1,2,2,2,2,2,3,3,4,4,5},{1,2,3,4,5})

3) Press Ctrl+Shift+Enter (because the frequency formula is an array formula)

Of course, you could also reference a range of cells for the data_array. Rather than using up another 5 cells for defining the bins_array, just include the values for the bins using curly braces {}. The data_array doesn't necessarily have to be integer values, but surveys usually involve multiple-choice. Personally, I would like to see more online surveys use a slider-bar for the ratings. That way, people like me who can't decide between a 4 or a 5 could choose 4.72 and still use a mouse. :)

One VERY cool way to use the frequency function is inside of the SparkBars() formula that is part of the Bissantz SparkMaker add-in. Here is a link to the article:
Using Sparklines for Survey Results

No comments: