FREQUENCY Function (2000 SR-1)

Topic

New Reply

I need to evaluate a bunch (100’s) of questionnaires. Each questionnaire has 12 questions and each question has the possible answers of 1, 2, 3, 4, or 5 (a Likert scale). I only want to use one cell to record the reponse to a particular question for each respondent (i.e., I don’t want to use a separate column for each of the possible answers for each question). So, there will be 13 columns, one for the responder ID, and then one column for each of the 12 questions.

When I used to do this in SAS, I simply used a freqency distribution to find out how many 1’s, 2’s, 3’s, 4’s, and 5’s for each question.

I’m sure it can be done with the Excel FREQUENCY function but silly me can’t seem to get it to work! I could sure use some help with this problem. Ideas?

Reply | Quote

Replies

The FREQUENCY function is an array function, so you need to enter it a little differently than regular functions. First, you need to select the whole area where the results of the function will go. For example, if only want to count the 1s, 2s and 3s, you’ll need to select 3 cells. The other different thing about the FREQUENCY function is that when you enter it you need to hold down CTRL+SHIFT+ENTER (or OK instead of enter, if you’re using the function wizard).

I know this sounds a little complicated, but there are good instructions in the HELP files. The article “About array formulas and how to enter them” is very good.

There are two other ways that you can get a frequency table in Excel. One is to use a Pivot Table. The other is to use the Data Analysis Tools and use histogram without the chart.

Reply | Quote

I have no idea how to use the =FREQUENCY() function. The best way to do what you want depends on your data layout. However, see if Frequency Summary 2 in the attachment does what you want; these are array formulas. If it doesn’t fit, perhaps you can post an example of your layout.

Reply | Quote

A more low tech solution would be to use a simple COUNTIF formula. You would, in fact, need five of them at the foot of each of your 12 columns with the questions. The syntax would be something like:

=COUNTIF(A1:A100, 1)
=COUNTIF(A1:A100, 2)
=COUNTIF(A1:A100, 3)
=COUNTIF(A1:A100, 4)
=COUNTIF(A1:A100, 5)

With 100 responders in cells A1 to A100, this would give you the total number of responders per answer. You can then copy this across the remaining 11 columns.

HTH

Reply | Quote