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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

waterdancn - 12 Apr 2006 22:51 GMT

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

Reply to this Message

daddylonglegs - 12 Apr 2006 23:35 GMT

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+ENTE

Reply to this Message

Peo Sjoblom - 12 Apr 2006 23:36 GMT

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

Signature

Regards,

Peo Sjoblom

http://nwexcelsolutions.com

> 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

Reply to this Message