Trying to find out which numbers occure most often

[yitbos]

I’m giving exams to students and want to see if the students are missin
the same questions in every class. I have a simple spreadsheet tha
has me enter the question number into the column each time a questio
is missed. The MODE function can tell me which number is occurrin
most often. However, I need to see if there is any other numbers tha
are occurring often as well.

Is there a command that will allow me to look at the second mos
entered number? For example, if I have 5,3,3,2,3,4,3,4,1,3,4, my mod
would be 3 as that’s the number that repeats the most. However,
happens 3 times and that is a number I need to be concerned with a
well.

Hopefully someone has a suggestion for this. Thanks to all of you fo
reviewing my question and providing feedback.

Joh

 

[Freemini]

One way you could do this is with a result table for each question
using the countif function.

i.e.
A B C
1 Q No Count Rank
2 1
3 2
4 3
5 4
6 5

In B2 type the formula =countif($A10:$A$20,A2) where A10:A20 is you
input data. Copy this to B3, B4 etc

In C2 type the formula =RANK(A2,$B$2:$B$6) this will give you th
position of each question in the count.

Hope this helps

Mik

 

[Ron Rosenfeld]

I’m giving exams to students and want to see if the students are missing
the same questions in every class. I have a simple spreadsheet that
has me enter the question number into the column each time a question
is missed. The MODE function can tell me which number is occurring
most often. However, I need to see if there is any other numbers that
are occurring often as well.

Is there a command that will allow me to look at the second most
entered number? For example, if I have 5,3,3,2,3,4,3,4,1,3,4, my mode
would be 3 as that’s the number that repeats the most. However, 4
happens 3 times and that is a number I need to be concerned with as
well.

Hopefully someone has a suggestion for this. Thanks to all of you for
reviewing my question and providing feedback.

John

Well, you could get the second most entered number with the *array-entered*
formula:

=MODE(IF(rng<>MODE(rng),rng,""))

To *array-enter*, hit <enter> while holding down <ctrl><shift>. XL will place
braces {...} around the formula.


--ron

 

[Earl Kiosterud]

John,

A pivot table, using the question number column both in the row (or column)
section (which means group by that column) of the pivot table, and also in
the data area, set to COUNT, will give you counts of all the questions
missed. Then sort them descending. Perfect for this application.