Mode for Strings

[HWL]

=mode(range) will return the most repetative number in a range.

=INDEX(range,MODE(MATCH(range,range,0))) will return the most repetative string in a range EXCEPT it breaks if there is an empty cell.

Anyone know how to correct this?
Thanks

 

[Domenic]

Try...

=INDEX(Range,MODE(IF(Range<>"",MATCH(Range,Range,0))))

...confirmed with CONTROL+SHIFT+ENTER.

 

[T. Valko]

=mode(range) will return the most repetative number in a range.

=INDEX(range,MODE(MATCH(range,range,0))) will return the most repetative string in a range EXCEPT it breaks if there is an empty cell.

Anyone know how to correct this?
Thanks

Try something like this...

Array entered**:

=INDEX(range,MODE(IF(range<>"",MATCH(range,range,0))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[HWL]

Thanks guys, this works perfectly in a cell. Can this work as a conditional formula? (arrays) Because I am trying to change the cell color if the cell doesn't equal the mode

 

[HWL]

the answer is yes-- it works in a conditional formula. Thanks much. This will be really helpful!

 

[T. Valko]

the answer is yes-- it works in a conditional formula. Thanks much. This will be really helpful!

Good deal. We appreciate the feedback!

 

[HWL]

Is there a way to apply this to filtered rows only? What is the string mode of the filtered rows?

 

[T. Valko]

Is there a way to apply this to filtered rows only? What is the string mode of the filtered rows?

Try this array formula**:

=INDEX(B6:B21,MODE(IF(SUBTOTAL(3,OFFSET(B6,ROW(B6:B21)-ROW(B6),0)),MATCH(B6:B21,B6:B21,0))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Adjust the range to suit.

 

[HWL]

Thanks guys, this works perfectly in a cell. Can this work as a conditional formula? (arrays) Because I am trying to change the cell color if the cell doesn't equal the mode

Try this array formula**:

=INDEX(B6:B21,MODE(IF(SUBTOTAL(3,OFFSET(B6,ROW(B6:B21)-ROW(B6),0)),MATCH(B6:B21,B6:B21,0))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Adjust the range to suit.

This is great, now the same for if it is a value???

 

[T. Valko]

This is great, now the same for if it is a value???

Try this...

Array entered**:

=MODE(IF(SUBTOTAL(3,OFFSET(B6:B21,ROW(B6:B21)-ROW(B6),,1)),B6:B21))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.