frequency a number repeats in a column

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ken - 07 Mar 2007 19:58 GMT

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.

Reply to this Message

Duke Carey - 07 Mar 2007 20:15 GMT

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))

> 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.

Reply to this Message

Harlan Grove - 07 Mar 2007 20:20 GMT

Ken <K...@discussions.microsoft.com> 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.

Reply to this Message

Ken - 08 Mar 2007 00:52 GMT

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))

> Ken <K...@discussions.microsoft.com> wrote...
> >I have a spreadsheet that is 8 columns by 1000 rows. each cell contains
[quoted text clipped - 14 lines]
> 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.

Reply to this Message

Harlan Grove - 08 Mar 2007 01:12 GMT

Ken <K...@discussions.microsoft.com> wrote...

>I entered the formula as you listed them and I get a #N/A (Data Not
>Available) error. . . .

...
...

>>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.

Reply to this Message

T. Valko - 08 Mar 2007 03:12 GMT

>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(COUNTIF(C$980:C980,rng)=0,rng+{0,0}))),"")

C981 copied down until you get blanks

Biff

> Ken <K...@discussions.microsoft.com> wrote...
>>I entered the formula as you listed them and I get a #N/A (Data Not
[quoted text clipped - 10 lines]
> array formula, it may mean you don't have any other values that appear
> more than once.

Reply to this Message