Second highest frequency

[wilchong via OfficeKB.com]

Dear sir,
I have a series of number from A1:H1 (7,3,7,1,6,8,3,7). I need an Excel
formular to describe the phenomenon of this particular series number: the
frequency.

If I want to to know the highest frequency number, the formula of MODE can
give me the answer which is 7. However, I also need an formular which can
give me answer: 3, because 3 appears two times in the series. The formular
MODE only will give me the highest frequent number, that is 7. So, I don't
know is there any formular can detect the second highest frequent number.

Please advice.
Wilchong

 

[T. Valko]

It depends on exactly what you want. If you want *only* modal frequencies:

Try this:

For the 2nd mode try this array formula** :

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

If there isn't a 2nd mode you'll get a #N/A error.

For all modes and non-modals try this:

A3 = your MODE(...) formula

Enter this array formula** in A4 and copy down until you get #N/A errors:

=MODE(IF(COUNTIF(A$3:A3,A$1:H$1)=0,A$1:H$1+{0;0}))

Also assuming no empty cells within your range A1:H1.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.