Excel Formula: Text equivalent to MODE?

[pathius]

Is there a way to reflect the most commonly occurring text string in
non-contiguous array?

For example,

Cells A1:A6 all contain the string ABC
Cells A7:A10 all contain DEF

I want cell B1 to reflect the most commonly occurring value (MODE) ou
of cells A1, A3, A5, A7, and A9 (ABC).

Can this be done without using VBS?

-J

 

[Bernie Deitrick]

JJ,

Here's one worksheet formula method, that requires a couple of helper
columns, so your final formula is in cell D1, not cell B1. The ranges used
are based on your example:

In cell B1, use the formula
=A1

In cell C1, use the formula
=COUNTIF(B:B,B1)

Copy B1:C2, and paste down to C3:C10 (Note that the copy range is TWO rows -
you should end up with alternating formulas and blanks down each column
after the paste.)

In cell D1, use the array formula (entered with Ctrl-Shift-Enter)

=INDIRECT("B" & (C1:C10=MAX(C1:C10))*ROW(C1:C10))

HTH,
Bernie
MS Excel MVP

 

[Harlan Grove]

Here's one worksheet formula method, that requires a couple of helper
columns, . . .

....

Ancillary columns unnecessary. See

http://google.com/[email protected]

 

[Bernie Deitrick]

Harlan,

How should the OP modify that answer to meet their requirement that only A1,
A3, A5, etc. be considered?

Bernie

 

[Harlan Grove]

How should the OP modify that answer to meet their requirement that only A1,
A3, A5, etc. be considered?

...

The original formula in question,

=INDEX(B1:B20,MODE(MATCH(B1:B20,B1:B20,0)))

would need to be adapted to alternating cells using T(OFFSET(..)).

=INDEX(T(OFFSET(A1,{0;2;4;6;8},0)),MODE(MATCH(T(OFFSET(A1,{0;2;4;6;8},0)),
T(OFFSET(A1,{0;2;4;6;8},0)),0)))

C;mon, Bernie, you know how to do this.

 

[pathius]

Thank you both for your answers!
You've been most helpful