getting mode of a column of strings

Posted by jason on December 21, 1999 9:08 AM

i have a large column of text strings that I am not able to sort. what (if any) formula can I use to count the most common string in a given column?

also, i have an equally large column of year dates. if i wanted to break the data up into decades, what formula could be used to say to keep a running total of entries between say, 1960 and 1969?

thanks,

Jason

Posted by Chris on December 21, 1999 10:30 AM

Jason,

This is a way to get the mode in VBA (if it's a tie, the first in the list wins):

Function TextMode(oRange As Range)
oMax = 0
For Each cell In oRange
oCount = Application.WorksheetFunction.CountIf(oRange, cell.Value)
If oCount > oMax Then oMax = oCount: TextMode = cell.Value
Next cell
End Function

To get a running total for a set of dates, you could use an array formula. It sounds like all you really need if a conditional sum with 2 criteria (a high and a low). MrExcel has some stuff on array formulas if you need help (or repost).

HTH,
Chris