formula help, PLEASE!!!

[SRT]

What formula do you use to look at a spread sheet and find the most
frequently used number besides a MODE function. The problem I am
haveing is I have used a MODE function to find the most frequent
number, but now I need to find the second most used number and also the
thrid and fourth most used numbers. But if I try and use a MODE function
I do not know how to write it to not keep returning the most frequent
number over and over???? Can ANYONE show me how to write a formula to
not look for a value that has all ready been found in an array as the
most frequent, I need to find the 5 most common numbers in an
spreadsheet, bottom line...

Thanks

 

[Don]

Try a Pivot Table.

With your range (include a header cell we will call mydata) drag mydata into
both the "row" and "data" sections of the layout. Make sure the mydata in
the "data" section is set to "Count of mydata". If not double click mydata
and select count. The pivot tabl;e will give you a list and the number of
times each appears in the range.


Don Pistulka

 

[Ragdyer]

This *array* formula will return the 2nd most used number:

=MODE(IF(A1:A100<>MODE(A1:A100),A1:A100))
--
Array formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead of
the regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.

NOW, beyond that is out of my league, but then ... there's always Harlan.

Here's his formula for finding the nth most occurring number:

I set this formula up to find the *third* most used number in the A1:A100
range:

=INDEX(A1:A100,MATCH(LARGE(FREQUENCY(A1:A100,A1:A100)+(1-ROW(INDIRECT("1:"&(
ROWS(A1:A100)+1)))/(ROWS(A1:A100)+1)),3),FREQUENCY(A1:A100,A1:A100)+(1-ROW(I
NDIRECT("1:"&(ROWS(A1:A100)+1)))/(ROWS(A1:A100)+1)),0))

Look for the "3", and substitute whatever number you're looking for.

BTW, this *does* work, so if you have a problem with it , lookout for word
wrap.

 

[Don]

p.s.

If you highlight the count column and sort it desending, the most common
will be on top, the second most common will be next, the third next, etc.

Don Pistulka

 

[Guest]

What formula do you use to look at a spread sheet and find the most
frequently used number besides a MODE function. The problem I am
haveing is I have used a MODE function to find the most frequent
number, but now I need to find the second most used number and also the
thrid and fourth most used numbers. But if I try and use a MODE function
I do not know how to write it to not keep returning the most frequent
number over and over???? Can ANYONE show me how to write a formula to
not look for a value that has all ready been found in an array as the
most frequent, I need to find the 5 most common numbers in an
spreadsheet, bottom line...

Thanks

--------------------

If you're trying to find more than just the #1 and #2 most frequent numbers,
there is a Histogram function provided with Excel. As I recall you have to
install the Analysis Tookpak add-in that came with Excel.

This can be set up to provide how many times each number in your data set occurs.

To invoke it click on Tools>DataAnalysis and pick Histogram from the list. The
Excel help system will describe the Histogram function and its options.

Good luck...

Bill

 

[Biff]

Hi RD!

I don't have anything better but one thing I ran across when I tried Harlans
formula was that if you replace the 3 with ROW(1:1) and drag down to
increment, after all the unique values have been returned according to their
nth "mode", then the formula starts randomly repeating. You won't get an
error return until you copy to enough cells that exceds the range size.

Biff

 

[RagDyeR]

Also, using your revision makes it very obvious that ties don't fare too
well with the formula.

The second, third and/or fourth ... etc. tie is never returned.

--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

Hi RD!

I don't have anything better but one thing I ran across when I tried Harlans
formula was that if you replace the 3 with ROW(1:1) and drag down to
increment, after all the unique values have been returned according to their
nth "mode", then the formula starts randomly repeating. You won't get an
error return until you copy to enough cells that exceds the range size.

Biff