Help with a Worksheet Function please

[ptman]

Hi ... I have been using the MODE function to determine the most frequently occurring number in an array. I would like to know if there is a Function, perhaps a variation of MODE, for determining the 2nd most frequently occurring number, and the 3rd most, etc. Any help on this would be greatly appreciated ... Thanks in advance.

 

[Frank Kabel]

Hi
using a procedure Harlan Grove posted some days ago you might try the
following:
Assumption: your data is in A1:A100
1. Put the following formula in B1
=MODE(A1:A100)

2. Now enter the following array formula entered with CTRL+SHIFT+ENTER)
in C1:
=MODE(IF(COUNTIF($B1:B1,A1:A100)=0,A1:A100))
this gets you the second most common. Copy this formula to the right
(D1:X1) and you'll get the next most common

 

[ptman]

Hello Frank, Thank you for your solution ... worked just fine .... much appreciated!