Results 1 to 4 of 4
Thread: FREQUENCY Function (2000 SR1)

20020116, 18:56 #1
 Join Date
 Jan 2001
 Location
 Albuquerque, New Mexico, USA
 Posts
 62
 Thanks
 0
 Thanked 0 Times in 0 Posts
FREQUENCY Function (2000 SR1)
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? <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

20020116, 20:02 #2
 Join Date
 Jun 2001
 Location
 Lawrence, Kansas, USA
 Posts
 202
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: FREQUENCY Function (2000 SR1)
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.

20020116, 20:26 #3
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
Re: FREQUENCY Function (2000 SR1)
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.
John ... I float in liquid gardens
UTC 7±DS

20020116, 22:36 #4
 Join Date
 Nov 2001
 Location
 Vienna, Wien, Austria
 Posts
 5,009
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: FREQUENCY Function (2000 SR1)
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.
HTHGrüße