Second Most Common Value

[Skalp]

I know that I can find the most common value in a group of numbers with MODE
function. But is this possible to find the second most common value in the
same group of numbers. It mustn't be with the same function, I just want to
get 3 most common values from one and the same group of numbers so that i
dont have to delete any numbers. I mean not so that i find the most common
value, delete all those values and then will get second most common value.
No. I want all numbers to stay.

 

[T. Valko]

Try this...

Numbers in the range A2:A11

B2 = mode formula: =MODE(A2:A11)

Enter this array formula** in B3 and copy down as needed:

=MODE(IF(COUNTIF(B$2:B2,A$2:A$11)=0,A$2:A$11+{0,0}))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[Bob Phillips]

=MODE(IF(H1:H7<>MODE(H1:H7),H1:H7))

this is an array formula, so commit it with Shift-Ctlr-Enter

 

[Skalp]

Thank You very much, it works! And with that I can find the third most common
number too! It was really helpful!

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[kyle obryan]

Am I just not putting the information in correctly for this?

=MODE(IF(COUNTIF(L$8:L8,C$1:C$1577)=0,C$1:C$1577+{0,0}))

I have the mode in cell L8 and my data im trying to get the 2nd, 3rd, and 4th instead of just mode is in cells c1:c1577

Thanks in advance

EggHeadCafe - .NET Developer Portal of Choice
http://www.eggheadcafe.com/default.aspx?ref=ng