G

#### Guest

set. I know MODE will give most frequent value but I need second, third,

fourth, and fifth most frequent values.

You are using an out of date browser. It may not display this or other websites correctly.

You should upgrade or use an alternative browser.

You should upgrade or use an alternative browser.

G

set. I know MODE will give most frequent value but I need second, third,

fourth, and fifth most frequent values.

P

http://tinyurl.com/pa35w

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007

Northwest Excel Solutions

www.nwexcelsolutions.com

(Remove ^^ from email)

R

E

can either buy a third party library or roll your own

try the excel programming group for help

here's an old post that will set you in the right direction

http://www.exceljockeys.com/forums/frequncy-of-unique-values-in-array-t12564.htm

it's was written to count letter frequency in a string but i

conceptual the same, and easily adapted for, determine the frequenc

of unique numbers in cells

B

Is this the unsolvable problem?

Having tried the examples in the links, Bernie's doesn't handle duplicates,

Peo's will only work for the 2nd mode, RD's doesn't handle duplicates, Ron's

doesn't handle duplicates, my attempt also didn't handle duplicates.

Harlan's does handle the duplicates but it needs a trap, otherwise, after

the last unique value is returned the formula starts "randomly" repeating

until it's been copied to more rows than the indexed range. (at which point

it errors as it should)

Harlan's formula with a trap:

=IF(ROWS($1:1)<=SUMPRODUCT((rng<>"")/COUNTIF(rng,rng&"")),INDEX(rng,MATCH(LARGE(FREQUENCY(rng,rng)+(1-ROW(INDIRECT("1:"&(ROWS(rng)+1)))/(ROWS(rng)+1)),ROW(A1)),FREQUENCY(rng,rng)+(1-ROW(INDIRECT("1:"&(ROWS(rng)+1)))/(ROWS(rng)+1)),0)),"")

See the link regarding some strange behavior of

SUMPRODUCT((rng<>"")/COUNTIF(rng,rng&"")):

http://tinyurl.com/jaen9

Biff

B

A1:A20 = numbers (there is at least 1 mode)

Formula in B1:

=MODE(A1:A20)

Formula entered in B2 as an array using the key combination of

CTRL,SHIFT,ENTER (not just ENTER):

=MODE(IF(COUNTIF(B$1:B1,A$1:A$20)=0,A$1:A$20+{0,0}))

Copy down until you get #N/A errors.

Biff

- Joined
- Mar 21, 2017

- Messages
- 1

- Reaction score
- 0

Well I have 7 columns x 3056 rows = 21,392 values

I just applied:

=MODE(A1:G3056)

Gave me '12' as #1 with 484 occurrences, then I deleted all the 12's with Replace (Ctrl+H) and showed me the #2 XD

I just need it the 6 most repeated... took over 5mins instead of 15mins reading formulas on the internet and watching videos in youtube