counting frequencies from an excel data sheet

rabbitdrop | 20:11 Fri 23rd Nov 2007 | Computers

8 Answers

hello,
is anyone any good with excel. if so do you know how I can count frequencies from it. I have a data sheet with individual people score, which were either 0,1,2, or 3. I want to count the number of people who scored 0, the number who scored 2, etc. It would be nice to have it in a chart but just a table would do. hope that makes sense. basically I just want to be able to look at it and say things like: most people scored 2 for this question..that sort of thing. Of course I could just physically count them up on a tally sheet but I'm sure there must be some quick and reliable way of doing it.
thanks

Using word art on each page

Playing a sound in Powerpoint 2002

Answers

1 to 8 of 8

Best Answer

No best answer has yet been selected by rabbitdrop. Once a best answer has been selected, it will be shown here.

For more on marking an answer as the "Best Answer", please visit our FAQ.

Katy May

You can use the 'COUNTIF' function. Assuming that you have a column with numbers in it to reflect the scores you would go to an empty cell and type in the following:

=COUNTIF(beginning of range:end of range ,"number of word to count")

So if I had entered the scores in Row B, cell numbers 2 - 50, in order to calculate how many cells contained the value 1, I would type:

=COUNTIF(B2:B50,"1")
I'd then do the same in the next free cell, but replace the 1 with 2 etc

There's probably a more efficient way to do it but i'm not brilliant with excel.

Hope that's helpful

K

Katy May

Sorry - in the formaula is should have said 'number OR word to count' not 'number of word to count'. Maybe I should set up a spreadsheet to keep track of the glasses of wine I'm drinking!

Katy May

Sorry - in the formula it should have said 'number OR word to count' not 'number of word to count'. Maybe I should set up a spreadsheet to keep track of the glasses of wine I'm drinking!

rabbitdrop

Question Author

Thanks katy, gonna try it now....

rabbitdrop

Question Author

katy thanks for your help. I tried and nothing happened. I'm sue it's me not following your instructions right. Could you clarify for me please. What I did is;
I went to an empty cell elsewhere on the spread sheet then typed in COUNTIF(D2:D70,"1")
Then I pressed enter and .... nothing happened!
the formula just stayed in the box
what did I do wrong

rabbitdrop

Question Author

Ok did it! I missed out the = before countif

thanks Katy, very helpful

riskman

just for fun:
you could amend your formula to
="1 = " &COUNTIF($B$1:$B$50,"1")
this would then display as 1 = 5, 2 = 10 etc.
for the chart just pick up Katy May's results (single column) and 'Insert' - 'Chart' and follow the instructions
Enjoy...

rabbitdrop

Question Author

Ok will try that. thanks for your help!

1 to 8 of 8

Do you know the answer?