I know that I can use the MODE function to get the most occuring value
in a range; but is there a way to list the next 9 most occuring values
in that range?

Thanks in advance
Mark

Try these:

Assume your data is in the range A2:A100

Enter this formula in C2:

=MODE(A2:A100)

Enter this array formula** in C3 and copy down as needed:

=MODE(IF(COUNTIF(C$2:C2,A$2:A$100)=0,A$2:A$100))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Mark" wrote in message
...
I know that I can use the MODE function to get the most occuring value
in a range; but is there a way to list the next 9 most occuring values
in that range?

Thanks in advance
Mark

On Nov 16, 12:46 pm, "T. Valko" wrote:
Try these:

Assume your data is in the range A2:A100

Enter this formula in C2:

=MODE(A2:A100)

Enter this array formula** in C3 and copy down as needed:

=MODE(IF(COUNTIF(C$2:C2,A$2:A$100)=0,A$2:A$100))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP

"Mark" wrote in message

...



I know that I can use the MODE function to get the most occuring value
in a range; but is there a way to list the next 9 most occuring values
in that range?

Thanks in advance
Mark- Hide quoted text -

- Show quoted text -

Thank you very much, It worked like a charm

"Mark" wrote in message
...
On Nov 16, 12:46 pm, "T. Valko" wrote:
Try these:

Assume your data is in the range A2:A100

Enter this formula in C2:

=MODE(A2:A100)

Enter this array formula** in C3 and copy down as needed:

=MODE(IF(COUNTIF(C$2:C2,A$2:A$100)=0,A$2:A$100))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP

"Mark" wrote in message

...



I know that I can use the MODE function to get the most occuring value
in a range; but is there a way to list the next 9 most occuring values
in that range?

Thanks in advance
Mark- Hide quoted text -

- Show quoted text -

Thank you very much, It worked like a charm

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP