X Most Frequent Numbers

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an array variably wide (most often 5 columns) and variably long (as few as 10 rows, all dependent on the value of another cell, which I can work in at a later time). I need the X most frequent numbers to be displayed at the bottom of the array, X being equal to the number of columns, and each number in its on column, starting from the left. This array may be subject to changes in width and height during use, so the solution must be readily adjustable (this I can arrange)
For the sake of simplicity, lets suppose that the array in reference is 5 columns wide and 25 rows long, with the uppermost/leftmost cell in the array being A1. I need a formula in each of the 5 columns on the 26th row to display the 5 most frequent numbers occurring in the array. All cells below the 26th row can be used for excess programming, but no others

If I'm vague at any point here, let me know. I'll return shortly to see what has come of this..

Jeremy N.
 
LupusMan said:
I have an array variably wide (most often 5 columns) and variably
long (as few as 10 rows, all dependent on the value of another
cell, which I can work in at a later time). I need the X most
frequent numbers to be displayed at the bottom of the array, X
being equal to the number of columns, and each number in its on
column, starting from the left. This array may be subject to
changes in width and height during use, so the solution must be
readily adjustable (this I can arrange).
Click to expand...
....

For my own simplicity, I'll assume you're using a defined names to refer to
the variable size range, and that that name is DRng. You can find the most
common number in DRng using the MODE function, so the most common number is
given by

=MODE(DRng)

If that formula were in cell A31, then the next most common number would be
given by the following *array* formula entered in cell B31.

=MODE(IF(COUNTIF($A31:A31,DRng)=0,DRng))

Select B31 and fill right into C31:E31 to get the next 3 most common
numbers.
 
The formulas you gave me return the same number in every cell they occupy. What is the reason for this? Is there a working solution

Thank
Jeremy N.
 
LupusMan said:
The formulas you gave me return the same number in every cell they occupy.
What is the reason for this? Is there a working solution?
Click to expand...

Did you enter the second formula as an *ARRAY* formula? You need to type in
the formula, then hold down [Ctrl] and [Shift] keys before pressing [Enter]
in order to enter the formula as an array formula. It doesn't work if
entered as an ordinary formula.
 
Back
Top