Return Value that exists more than all other values
 

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

Return Value that exists more than all other values
 

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"AdamE" wrote:
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

Return Value that exists more than all other values
 

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


"AdamE" wrote:

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

Return Value that exists more than all other values
 

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)


--
Biff
Microsoft Excel MVP


"AdamE" wrote in message
...
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

Return Value that exists more than all other values
 

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!

"Max" wrote:

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"AdamE" wrote:
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

Return Value that exists more than all other values
 

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)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"AdamE" wrote:
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!

Return Value that exists more than all other values
 

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))))

--
Biff
Microsoft Excel MVP


"Max" wrote in message
...
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)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"AdamE" wrote:
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!

Return Value that exists more than all other values
 

I was just clarifying the OP's statement:
Whenever there are more empty cells than there are values it is returning
the empty cell.

... 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)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"T. Valko" wrote in message
...
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))))

--
Biff
Microsoft Excel MVP

Return Value that exists more than all other values
 

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

"T. Valko" wrote:

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))))

--
Biff
Microsoft Excel MVP


"Max" wrote in message
...
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)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"AdamE" wrote:
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!

Return Value that exists more than all other values
 

"AdamE" wrote
.. 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:
That formula will ignore *empty* cells ..

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---