I still cant get the six most common numbers to come up
 

when using:

C3:H204 = named range = rng

Enter this formula in A3:

=MODE(rng)

Enter this array formula** in A4 and copy down a total of 5 cells (or as
needed):

=IF(ROWS($1:2)<=COUNT(1/FREQUENCY(rng,rng)),MODE(IF(COUNTIF(A$3:A3,rng)=0, rng)),"")

Enter this formula in B3 and copy down as needed:

=COUNTIF(rng,A3)

The formula,

=IF(ROWS($1:2)<=COUNT(1/FREQUENCY(rng,rng)),MODE(IF(COUNTIF(A$3:A3,rng)=0, rng)),"")

It does work for the next number, but for the third number the formula shows
up in the cell even though I press (ctrl shft entr) not the numer. What am I
doing wrong???

I still cant get the six most common numbers to come up
 

Are you drag copying the formula down the column? You don't need to manually
type the formula in every cell.

Type the formula in the first cell and enter it as an array.

Now, "grab" the fill handle with your mouse. The fill handle is that little
black square on the bottom right side of the selected cell. Hover your mouse
over that little black square and the cursor will change from a fat + sign
to a skinny + sign. When the cursor is a skinny + sign left click and hold
then drag down the column. When you release the mouse button the formula (or
whatever) will be copied to those cells.

It sounds like that 3rd cell is formatted as TEXT but drag copying will also
copy the format of the source cell so it should be ok.

Biff

"tht" wrote in message
...
when using:

C3:H204 = named range = rng

Enter this formula in A3:

=MODE(rng)

Enter this array formula** in A4 and copy down a total of 5 cells (or as
needed):

=IF(ROWS($1:2)<=COUNT(1/FREQUENCY(rng,rng)),MODE(IF(COUNTIF(A$3:A3,rng)=0, rng)),"")

Enter this formula in B3 and copy down as needed:

=COUNTIF(rng,A3)

The formula,

=IF(ROWS($1:2)<=COUNT(1/FREQUENCY(rng,rng)),MODE(IF(COUNTIF(A$3:A3,rng)=0, rng)),"")

It does work for the next number, but for the third number the formula
shows
up in the cell even though I press (ctrl shft entr) not the numer. What
am I
doing wrong???