most frequently occurring value

[Pivotrend]

hello


how do i find out the most frequently occurring value, digit, or numbe
in a set of rows & columns ?
then i need to find out the second most occurring value then the 3rd

then find the least frequently occurring value
then the second least occurring value then the 3rd


thanx..

 

[Niek Otten]

Look in HELP for the FREUQUENCY() function

 

[Niek Otten]

<FREUQUENCY()>

make that

FREQUENCY()

 

[Bob Phillips]

Assuming that the numbers are in A2:A200

B2: = A1
B3: enter

=IF(ISERROR(MATCH(0,COUNTIF(B$2:B2,$A$2:$A$200&""),0)),"",
INDEX(IF(ISBLANK($A$2:$A$200),"",$A$2:$A$200),MATCH(0,COUNTIF(B$2:B2,$A$2:$A
$200&""),0)))

as an array formula, so commit with Ctrl-Shift-Enter

Copy this as far down column B as you think you might have unique values in
column A

Select C2:Cn, where n is the number of uniques previously estimate (I have
estimated down to 10 for this exercise), and then in the formula bar enter
this formula

=IF($B$2:$B$10<>"",FREQUENCY($A$2:$A$200,$B$2:$B$10),"")

again an array formula, committed with Ctrl-Shift-Enter
--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)