Find second most common value - MODE

[martinblake02]

Hello,

I have worked out how to find the most common value and ignore blank cells.
I have used the below formula in an array:

=IFERROR(INDEX(January!$E$8:$E$1500,MODE(IF(January!$E$8:$E$1500<>"",MATCH(January!$E$8:$E$1500,January!$E$8:$E$1500,0)))),"")

now I wish to find the second most common value.

is there an easy way to do this?

Really appreciate any help

Thanks
Martin

 

[Domenic]

Let's assume that A2 contains your formula, the one returning the first most common text value. In B2, enter the following formula and confirm with CONTROL+SHIFT+ENTER...

=IFERROR(INDEX(January!$E$8:$E$1500,MODE(IF(January!$E$8:$E$1500<>"",IF(January!$E$8:$E$1500<>A2,MATCH(January!$E$8:$E$1500,January!$E$8:$E$1500,0))))),"")

Hope this helps!