I have a spreadsheet that is 8 columns by 1000 rows. each cell contains a
number from 1 to 100. I need to find out which numbers in each column repeat
the most often, then the next most frequent.

I thought that the frequency command would do it but I can't make it work on
my data.

Use Mode() to find the most frequent #
Use the array formula (committed with Ctrl-Shift-Enter) of

=MODE(IF(range of numbers<first mode formula,range of numbers))




"Ken" wrote:

I have a spreadsheet that is 8 columns by 1000 rows. each cell contains a
number from 1 to 100. I need to find out which numbers in each column repeat
the most often, then the next most frequent.

I thought that the frequency command would do it but I can't make it work on
my data.

Ken wrote...
I have a spreadsheet that is 8 columns by 1000 rows. each cell contains
a number from 1 to 100. I need to find out which numbers in each column
repeat the most often, then the next most frequent.
....

If your data were in A3:H1002, and you wanted the most frequent values
in row 1005 (so most frequent in A3:A1002 in A1005, etc.), next most
frequent values in row 1006, etc., try these formulas.

A1005:
=MODE(A$3:A$1002)

A1006 [array formula]:
=MODE(IF(COUNTIF(A$1005:A1005,A$3:A$1002)=0,A$3:A$ 1002))

Fill A1005:A1006 right into B1005:H1006. If you want 3rd most frequent
values in row 1007, select A1006:H1006 and fill down into A1007:H1007.

Thanks Harlan,
I entered the formula as you listed them and I get a #N/A (Data Not
Available) error. My data fields run from C2:H975 I modified the ones you
listed to reflect the cells that I have in my array. Any suggestions?
C980 =MODE(C$2:C$975)
C981 =MODE(IF(COUNTIF(C$980:C980,C$2:C$975)=0,C$2:C$975 ))

"Harlan Grove" wrote:

Ken wrote...
I have a spreadsheet that is 8 columns by 1000 rows. each cell contains
a number from 1 to 100. I need to find out which numbers in each column
repeat the most often, then the next most frequent.
....

If your data were in A3:H1002, and you wanted the most frequent values
in row 1005 (so most frequent in A3:A1002 in A1005, etc.), next most
frequent values in row 1006, etc., try these formulas.

A1005:
=MODE(A$3:A$1002)

A1006 [array formula]:
=MODE(IF(COUNTIF(A$1005:A1005,A$3:A$1002)=0,A$3:A$ 1002))

Fill A1005:A1006 right into B1005:H1006. If you want 3rd most frequent
values in row 1007, select A1006:H1006 and fill down into A1007:H1007.

Ken wrote...
I entered the formula as you listed them and I get a #N/A (Data Not
Available) error. . . .
....
"Harlan Grove" wrote:
....
A1006 [array formula]:
=MODE(IF(COUNTIF(A$1005:A1005,A$3:A$1002)=0,A$3: A$1002))
....

I did say array formula. That means you need to type the formula, then
hold down [Shift] and [Ctrl] keys before pressing the [Enter] key to
enter the formula. If the formula returns #N/A when entered as an
array formula, it may mean you don't have any other values that appear
more than once.

If the formula returns #N/A when entered as an
array formula, it may mean you don't have any other
values that appear more than once.

If you want the numbers that only appear once to also be listed:

C980 =MODE(rng)

C981 array entered

=IF(ROWS($1:2)<=COUNT(1/FREQUENCY(rng,rng)),MODE(IF(ISNUMBER(rng),IF(COUNT IF(C$980:C980,rng)=0,rng+{0,0}))),"")

C981 copied down until you get blanks

Biff

"Harlan Grove" wrote in message
oups.com...
Ken wrote...
I entered the formula as you listed them and I get a #N/A (Data Not
Available) error. . . .
...
"Harlan Grove" wrote:
...
A1006 [array formula]:
=MODE(IF(COUNTIF(A$1005:A1005,A$3:A$1002)=0,A$3 :A$1002))
...

I did say array formula. That means you need to type the formula, then
hold down [Shift] and [Ctrl] keys before pressing the [Enter] key to
enter the formula. If the formula returns #N/A when entered as an
array formula, it may mean you don't have any other values that appear
more than once.