Using Mode in Excel, need more than 1st most frequent number.

What formula can I use to get 1st, 2nd and 3rd number most occuring in a
column? I am looking to count ballots for 1st, 2nd and 3rd place win of a
contest.
I use Excel 2000

If your numbers are in A1:A100 then in C2

=MODE(A1:A100)

in C3 copied down

=MODE(IF(ISNA(MATCH(A$1:A$100,C$2:C2,0)),A$1:A$100))

confirmed with CTRL+SHIFT+ENTER

First is

=MODE(A2:A15)

assume you put that in C2

in C3

=MODE(IF($A$2:$A$15<>C2,$A$2:$A$15))

entered with ctrl + shift & enter

in C4

=MODE(IF(($A$2:$A$15<>C2)*($A$2:$A$15<>C3),$A$2:$A$15))

also entered with ctrl + shift & enter

note that if there are ties the first occurring tie will be selected

you can also use histogram and autofilter

--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com


"waterdancn" <[email protected]> wrote in message
news:[email protected]...
> What formula can I use to get 1st, 2nd and 3rd number most occuring in a
> column? I am looking to count ballots for 1st, 2nd and 3rd place win of a
> contest.
> I use Excel 2000