Displaying Second Most Occurring Number in a Range

[Mike]

I have a list of miscellaneous account numbers in a column. I used the MODE
function to list the most frequently occurring account. I am trying to
figure out how to display the second and possibly third most occurring
accounts as well. Any help would be greatly appreciated.

 

[Bob Phillips]

=MODE(IF(A1:A20<>MODE(A1:A20),A1:A20,""))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Mike]

Thanks for your help. I entered the information exactly as below except the
cell range and the result is 0 in the cell instead of the account number that
occurs second most. My cell range is C15:C659 which I subtituted for the
A1:A20 below which I assume is correct. I pressed Ctrl-Shift-Enter. Not
sure where to go from here.

 

[Bob Phillips]

I suspect there are too many blanks there.

See if this version works

=MODE(IF(IF(C15:C659<>"",C15:C659)<>MODE(IF(C15:C659<>"",C15:C659) ),IF(C15:C659<>"",C15:C659),""))

again array-entered.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Bob Phillips]

If that works, try this version as well


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Bob Phillips]

If that works, try this version as well

=MODE(IF(C15:C659<>MODE(C15:C659),IF(C15:C659<>"",C15:C659),""))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Mike]

Thanks Bob. It worked great!
--
Mike

If that works, try this version as well

=MODE(IF(C15:C659<>MODE(C15:C659),IF(C15:C659<>"",C15:C659),""))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Bob Phillips]

Which one, or all of them?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Mike]

=MODE(IF(IF(C15:C659<>"",C15:C659)<>MODE(IF(C15:C659<>"",C15:C659)
),IF(C15:C659<>"",C15:C659),""))

 

[RagDyeR]

Since you mentioned the possibility of perhaps also needing the *3rd* most
occurring number,
try this non-array formula, which will give you *whatever* occurrence
number that you enter in say A1:

=INDEX(C15:C659,MATCH(LARGE(FREQUENCY(C15:C659,C15:C659),A1),FREQUENCY(C15:C659,C15:C659),0))

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

=MODE(IF(IF(C15:C659<>"",C15:C659)<>MODE(IF(C15:C659<>"",C15:C659)
),IF(C15:C659<>"",C15:C659),""))

 

[Harlan Grove]

If that works, try this version as well

=MODE(IF(C15:C659<>MODE(C15:C659),IF(C15:C659<>"",C15:C659),""))

....

Maybe

=MODE(IF(C15:C659<>"",IF(C15:C659<>MODE(C15:C659),C15:C659)))

would be slightly more efficient.

 

[Harlan Grove]

Since you mentioned the possibility of perhaps also needing the *3rd* most
occurring number, try this non-array formula, which will give you *whatever*
occurrence number that you enter in say A1:

=INDEX(C15:C659,MATCH(LARGE(FREQUENCY(C15:C659,C15:C659),A1),FREQUENCY(C15:C659,C15:C659),0))

If the OP might want the 3rd most frequently occurring, he might also
want the 4th most frequently occurring, etc. If so, FAR MORE EFFICIENT
to use *ONE* FREQUENCY call stored in some other range of 1 column by
645 rows.

Unfortunately, this approach won't work when different values appear
the same number of times because the MATCH call will always return the
index of the first of them, NEVER the index of the second or
subsequent. For example, with the following in a range named D,

1
2
3
1
2
1
1
2
3
2

and another, single cell named range n evaluating to 1, the formula

=INDEX(d,MATCH(LARGE(FREQUENCY(d,d),n),FREQUENCY(d,d),0))

returns 1, but it still returns 1 when you change n to 2. When you
change n to 3, it returns 3.

If the OP wants to the nth most frequently occurring value, there's no
ROBUST way to do this that doesn't require referring to ALL the more
frequently occurring values. That so, better to create an ordered list
of those values. With the source data in a range named d and the
results beginning in cell E1,

E1: =MODE(d)

E2: =MODE(IF(d<>"",IF(d<>MODE(d),d)))

E3: =MODE(IF(d<>"",IF(COUNTIF(E$1:E2,d)=0,d)))

E2 and E3 are array formulas. Fill E3 down as far as needed.

 

[Mike]

The last series of functions you listed seems to work best. Thank you all
for all your assistance. I really appreciate it.