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.
Extract n th. most frequently occurring item from a database
{ 2 Comments }