Frequency questio

M

meegan

I have a column of data and i would like to know what number occur the most
frequently. I dont know how to accomplish this though...could anyone please
help me? Thank you if you can!
 
B

Bob Phillips

=INDEX(rng,MATCH(MAX(COUNTIF(rng,rng)),COUNTIF(rng,rng),0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
M

meegan

where do i insert the formula

Bob Phillips said:
=INDEX(rng,MATCH(MAX(COUNTIF(rng,rng)),COUNTIF(rng,rng),0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
Click to expand...
 
R

RaymundCG

Hi again meegan,

You may enter the formula anywhere w/in the spreadsheet except the column
containing the data.

the your_column previously mentioned in the formula means you have to select
the range containing the data

Hope this helps! :)
 
D

Domenic

Bob's formula...

=INDEX(C1:C57,MATCH(MAX(COUNTIF(C1:C57,C1:C57)),COUNTIF(C1:C57,C1:C57),0)
)

....confirmed with CONTROL+SHIFT+ENTER.

Raymund's formula...

=MODE(C1:C57)

....confirmed with just ENTER.

Note that MODE will return #N/A when no number occurs more than once.
Also, what if there's more than one number occurring most often?
 
R

RaymundCG

Hi!

For multimodal data, we can use the FREQUENCY function instead. As per
example if data is in C1:C57, we can select the output range as D1:D57 then
enter the ff as an array formula (using CTRL+SHIFT+ENTER):

=FREQUENCY(C1:C57,C1:C57)

From there we can determine the number of occurences for each value.

Hope this helps!
 
Top