excel - determining the highest frequency of inputs

[glg1]

I'm having difficulting with the following:

several ranges of cells:
I need to find the word represented most frequently in each range, and also the highest average of the words.

for example - one range

bob 234
ted 133
sara 245
bob 345
ted 234
ted 123
sara 756


>>highest represented word = ted
>> highest average = Sara (500.5)

I've got thousands of these ranges...

thanks,
glg

 

[KenWright]

Using your values and assuming they are in A1:A7, then this should give you the most frequent:-

=INDEX($A$1:$A$7,MATCH(MIN(1/COUNTIF($A$1:$A$7,$A$1:$A$7)),1/COUNTIF($A$1:$A$7,$A$1:$A$7),0))

array entered using CTRL+SHIFT+ENTER.

If two names appear the most the same number of times, it will return only the first in the list.

Regards
Ken..........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[GlennUK]

And the highest average is:

=MAX((1/COUNTIF(A1:A7,A1:A7)*SUMIF(A1:A7,A1:A7,B1:B7)))

array entered using Ctrl+Shift+Enter instead of Enter.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[glg1]

Thanks ken and Glenn,
works great. My hat is off...
Glg

 

[KenWright]

You're welcome

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------