I have five cells in a range filled with following:
England
Germany
England
Scotland
Wales
I want to return "England" being the country that appears the most. How can
I do this?
I have five cells in a range filled with following:
England
Germany
England
Scotland
Wales
I want to return "England" being the country that appears the most. How can
I do this?
What do you want if there is a tie ?
eg:
England
Germany
England
Scotland
Germany
--
AP
"Phil" <[email protected]> a écrit dans le message de news:
[email protected]...
>I have five cells in a range filled with following:
>
> England
> Germany
> England
> Scotland
> Wales
>
> I want to return "England" being the country that appears the most. How
> can
> I do this?
Hi Phil,
I suggest to take my UDF CountStrings:
Function CountStrings(r As Range) As Variant
'Returns variant with info about strings in range r:
'First row contains count of different strings and count of empty cells
'Subsequent rows show all occurring strings (sorted) and their
frequency.
Dim k As Long
Dim lidx As Long 'index of next empty field in string table
Dim l As Long
Dim rc As Range
ReDim v(0 To r.Count, 0 To 1) As Variant '0: string; 1: frequency
lidx = 1
For Each rc In r
If IsEmpty(rc) Then
v(0, 1) = v(0, 1) + 1
Else
'Search for current cell value in string table
v(lidx, 0) = rc 'initialize search so that value will be found
l = 1
Do While v(l, 0) < v(lidx, 0)
l = l + 1
Loop
If l = lidx Then
lidx = lidx + 1 'Wasn't in. Added.
Else
If v(l, 0) <> rc Then
For k = lidx - 1 To l Step -1
v(k + 1, 0) = v(k, 0)
v(k + 1, 1) = v(k, 1)
Next k
v(l, 0) = rc
v(l, 1) = 0
lidx = lidx + 1
End If
End If
v(l, 1) = v(l, 1) + 1 'increase frequency
End If
Next rc
v(lidx, 0) = ""
v(0, 0) = lidx - 1
CountStrings = v
End Function
Now you can retrieve your max country(ies).
HTH,
Bernd
On Thu, 8 Jun 2006 07:51:02 -0700, Phil <[email protected]> wrote:
>I have five cells in a range filled with following:
>
>England
>Germany
>England
>Scotland
>Wales
>
>I want to return "England" being the country that appears the most. How can
>I do this?
If your list of entries is in a range named rng, then the **array** formula:
=INDEX(rng,MATCH(MAX(COUNTIF(rng,rng)),COUNTIF(rng,rng),0))
will return the most common. If there is a tie, it will only return the first
entry that is most common.
To enter an **array** formula, after typing/pasting the formula into the
formula bar, hold down <ctrl><shift> while hitting <enter>. Excel will place
braces {...} around the formula.
--ron
Thank you. Exactly what I was after.
"Ron Rosenfeld" wrote:
> On Thu, 8 Jun 2006 07:51:02 -0700, Phil <[email protected]> wrote:
>
> >I have five cells in a range filled with following:
> >
> >England
> >Germany
> >England
> >Scotland
> >Wales
> >
> >I want to return "England" being the country that appears the most. How can
> >I do this?
>
> If your list of entries is in a range named rng, then the **array** formula:
>
> =INDEX(rng,MATCH(MAX(COUNTIF(rng,rng)),COUNTIF(rng,rng),0))
>
> will return the most common. If there is a tie, it will only return the first
> entry that is most common.
>
> To enter an **array** formula, after typing/pasting the formula into the
> formula bar, hold down <ctrl><shift> while hitting <enter>. Excel will place
> braces {...} around the formula.
>
>
> --ron
>
On Fri, 9 Jun 2006 02:20:01 -0700, Phil <[email protected]> wrote:
>Thank you. Exactly what I was after.
>
Glad to help. Thanks for the feedback.
--ron
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks