Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    Albuquerque, New Mexico, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    FREQUENCY Function (2000 SR-1)

    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>

  2. #2
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Lawrence, Kansas, USA
    Posts
    202
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FREQUENCY Function (2000 SR-1)

    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.

  3. #3
    Uranium Lounger
    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 SR-1)

    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.
    Attached Files Attached Files
    -John ... I float in liquid gardens
    UTC -7DS

  4. #4
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FREQUENCY Function (2000 SR-1)

    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
    Gre

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •