Extract n th. most frequently occurring item from a database

{ 2 Comments }

Assume a database in range D5:D11 with the following values – Black, Black, Purple, Purple, Grey, Grey, Grey.  To extract the most frequently occurring item, one may use the following formula

=INDEX($D$5:$D$11,MODE(MATCH($D$5:$D$11,$D$5:$D$11,0)))

To extract the second most frequently occurring item, try the following:

1. Enter 2 in cell C16:
2. In cell D16, enter the following array formula (Ctrl+Shift+Enter)

=LOOKUP(2,1/(LARGE(FREQUENCY(IF(INDEX(COUNTIF($D$5:$D$10,$D$5:$D$10),0),MATCH($D$5:$D$10,$D$5:$D$10,0)),ROW($D$5:$D$10)-ROW($D$4))+(ROW($D$5:$D$11)/10000),C16)=FREQUENCY(IF(INDEX(COUNTIF($D$5:$D$10,$D$5:$D$10),0),MATCH($D$5:$D$10,$D$5:$D$10,0)),ROW($D$5:$D$10)-ROW($D$4))+(ROW($D$5:$D$11)/10000)),$D$5:$D$10)

To extract the n th most frequently occurring item, enter that n th number in cell C16.

Leave a Comment

Your email address will not be published.

*