Return Value that exists more than all other values

[Guest]

Good Afternoon,

I'm having trouble trying to write a function that returns the value in a
given row that exists more than all other values in that same range. Please
see the below example.

A1 = Apple
B1 = Banana
C1 = Orange
D1 = Apple

E1 (Formula) = This cell should return Apple because Apple is listed in more
cells than all other fruits.

Thank you,
Adam

 

[Guest]

One way

Assuming data in row1
Put in A2, array-enter the formula by pressing CTRL+SHIFT+ENTER:
=INDEX(1:1,MATCH(MAX(COUNTIF(1:1,1:1)),COUNTIF(1:1,1:1),0))

In the event of any ties in the max occurences,
only the "leftmost" item in row1 will be returned

 

[Guest]

=INDEX(A1:J1,MODE(MATCH(A1:J1&"",A1:J1&"",0)))

 

[T. Valko]

Another one:

Array entered**:

=INDEX(rng,MODE(MATCH(rng,rng,0)))

Will return #N/A if there are empty cells within the rng.

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

 

[Guest]

Hi Max,

This works great! However, there is one issue I am running into. Whenever
there are more empty cells than there are values it is returning the empty
cell. How should this function be modified to ignore blank cells?

Thank you for your help, it is much appreciated!

 

[Guest]

I just re-tried it again here, with values & blank cells interspersed in row1.
The earlier expression returns correctly, it ignores blank cells ?

(Ensure the cells are really blank, clear these with the delete key)

 

[T. Valko]

That formula will ignore *empty* cells but will not ignore cells with
formula blanks (""). Also, it will evaluate empty cells and cells that
contain formula blanks ("") as being the same. For example, if you had 5
apples, 1 formula blank and 5 empty cells the formula would return the
formula blank as the mode value.

Try this (array entered):

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

 

[Max]

I was just clarifying the OP's statement:
... and I did have this line added for emphasis in the response:

(Ensure the cells are really blank, clear these with the delete key)

Once a cell contains a formula, of course it's no longer empty
(yeah, I know that)

---

 

[Guest]

Thank you Biff,

The additional "if" function fixed my problem as the cells are in fact
empty. You guys are awesome! Thank you all so much for your help.

Sincerely,
Adam

 

[Max]

.. The additional "if" function fixed my problem
as the cells are in fact empty.

I'm puzzled by your assertion

... as the cells are in fact empty.

As stated in my response, the earlier array expression:
=INDEX(1:1,MATCH(MAX(COUNTIF(1:1,1:1)),COUNTIF(1:1,1:1),0))
will return correctly, it ignores blank cells

Even Biff himself acknowledges that in his interjection opener:

---