Find the MODE of a column

**Virtual_Jase** · 10-04-2012, 03:45 AM

Hi All,

Just joined the forums yesterday, and I was wondering if you guys could help me out with a couple of questions on Excel 2010....

1) Is it possible to find the MODE value from a column of text?
2) With regards to the above, can I find the top 5 values within a column?

Any help you could provide would be greatly appreciated.

Many thanks in advance

Cheers

Jason

**Bob Phillips** · 10-04-2012, 04:03 AM

Use these array formula

C1:=INDEX(A2:A20,MODE(MATCH(A2:A20,A2:A20,0)))
C2: =INDEX($A$2:$A$20,MODE(IF(COUNTIF(F$1:F1,$A$2:$A$20)=0,MATCH($A$2:$A$20,$A$2:$A$20,0))))

copy C2 down to C5

**JBeaucaire** · 10-04-2012, 04:05 AM

You could use a PIVOT TABLE on that column of values and set the table to provide a COUNT.

Example: http://screencast.com/t/utOKgOveCQ

**Fotis1991** · 10-04-2012, 04:19 AM

Good morning.

@ Bob

In c2>=INDEX($A$2:$A$20,MODE(IF(COUNTIF(c$1:c1,$A$2:$A$20)=0,MATCH($A$2:$A$20,$A$2:$A$20,0)))) ??

Nice work!

**Virtual_Jase** · 10-04-2012, 04:23 AM

Thanks for that Bob, C1 worked a treat!

C2 however no joy. I'll bullet point a little more detail. The cells I am basing this on range from C3:C80, so I changed the formulae in C1 to reflect this, works fine. I believe I changed the correct cells in C2 as well, but not being a pro with Excel, I could have this wrong...!

**Fotis1991** · 10-04-2012, 04:56 AM

So use first Bob's formula in d1 and this one to d2 and copy down,as you want.

=IFERROR(INDEX($c$3:$c$80,MODE(IF(COUNTIF(d$1:d1,$c$3:$c$80)=0,MATCH($c$3:$c$80,$c$3:$c$80,0))));"")

Array Formula.(control+Shift+enter)