Subsequent MODE

[Mark]

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

 

[T. Valko]

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)

 

[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







- Show quoted text -

Thank you very much, It worked like a charm

 

[T. Valko]

Thank you very much, It worked like a charm

You're welcome. Thanks for the feedback!