The mode function

[Doug]

I looked up the capability of the mode function and it said that the only
time it should return an #N/A value is when there are not two names, or
numbers in a range. I am not sure why but for the given stock market sectors,
the function below always returns a #N/A when it should return in this case
the name finance. Any suggestions?

ComputerandTechnology
BasicMaterials
Retail-Wholesale
Medical
Finance
Finance
IndustrialProducts
Utilities
Medical
Finance
=MODE(S832:S841)

 

[Gary''s Student]

Can't be used on text.

 

[Doug]

What would qualify as a name? Would it work if I take out the spaces?

 

[Gary''s Student]

With your data in A1 thru A10,

=INDEX(A1:A10,MODE(MATCH(A1:A10,A1:A10,0)))

will return Finance

 

[Dave Peterson]

This was posted by Peo Sjoblom:

=INDEX(A1:A20,MODE(MATCH(A1:A20,A1:A20,0)))

or

=INDEX(A1:A20,MATCH(MAX(COUNTIF(A1:A20,A1:A20)),COUNTIF(A1:A20,A1:A20),0))

both entered with ctrl + shift & enter, the first one is shorter but returns
error if there are blank cells within the range

adapt to fit your range

 

[Dave Peterson]

This array formula:

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

will work if there are empty cells in A1:A20, too.

 

[Doug]

That worked but when I put it in my table that has a bunch of #N/A values it
returns #N/A even though I filtered them out it still picks up on them. Is
there a way to omit the #N/A's from the formula?

 

[Doug]

I can't figure out how to put the ISNA in this formula to avoid getting a
#N/A value
This works accept when there is an #N/A in the range.
=INDEX(S3:S1000,MODE(MATCH(S3:S1000,S3:S1000,0)))

 

[Dave Peterson]

=INDEX(S3:S1000,MODE(MATCH(IF(ISNA(S3:S1000),"",S3:S1000),
IF(ISNA(S3:S1000),"",S3:S1000),0)))

I can't figure out how to put the ISNA in this formula to avoid getting a
#N/A value
This works accept when there is an #N/A in the range.
=INDEX(S3:S1000,MODE(MATCH(S3:S1000,S3:S1000,0)))

 

[Doug]

Can't figure out why it still returns a #N/A for the answer.
The N/A's are hidden because they are filtered out of the table, but the
mode function keeps saying they are there...
Any more suggestions?

 

[Doug]

Can't figure out why it still returns a #N/A for the answer.
The N/A's are hidden because they are filtered out of the table, but the
mode function keeps saying they are there...
Any more suggestions?

 

[Dave Peterson]

Maybe:
If the data set contains no duplicate data points, MODE returns the #N/A error
value.
(from xl2003's help)

Maybe you can try to get it working on a smaller range and see the conditions
where it breaks.

Can't figure out why it still returns a #N/A for the answer.
The N/A's are hidden because they are filtered out of the table, but the
mode function keeps saying they are there...
Any more suggestions?

 

[Bernd P]

Hello,

Array-enter
=ISERROR(MODE(MATCH(A1:A10,A1:A10,0)))

This will return TRUE if and only if no name is being repeated.

Regards,
Bernd

 

[Bernd P]

Another one which works with blank cells:
=AND(NOT(OR(ISBLANK(A1:A10))),ISERROR(MODE(MATCH(A1:A10,A1:A10,0))))

[array-entered]

Regards,
Bernd

 

[Doug]

These are vlookup data. There are multiple fields with the same name. Is it
possible that it won't work with a vlookup?

I tried all the example that were given to me and it always says "true"

I am about to give up on this one....
Any more ideas?

Consumer Staples
Finance
Utilities
Oils-Energy
Utilities
Computer and Technology
Computer and Technology
Consumer Staples
Consumer Staples

 

[Dave Peterson]

Share a snippet of your data (with errors).
Share the formula you tried for that snippet of data.
Share the results that you got with that data and that formula.

 

[Doug]

This is being real funny to deal with.
The column in my table returns N/A for the formula below because it is not
blocking out the N/A values for some reason. I also have the N/A's filtered
in the table so they are not showing, but it still picks up on them.
=INDEX(S3:S1000,MODE(MATCH(IF(ISNA(S3:S1000),"",S3:S1000),IF(ISNA(S3:S1000),"",S3:S1000),0)))

I tested this below the table and it works without the N/A values.
Oils-Energy
Oils-Energy
Finance
Oils-Energy
Computer and Technology
Oils-Energy
Oils-Energy
Retail-Wholesale

=INDEX(S1006:S1013,MODE(MATCH(IF(ISNA(S1006:S1013),"",S1006:S1013),
IF(ISNA(S1006:S1013),"",S1006:S1013),0)))

Returns Oils-Energy as it should since there are no N/A values in this list.

 

[Dave Peterson]

This kind of formula won't ignore hidden cells.

There really aren't that many formulas that do (=subtotal() is the one that
jumps to mind). And that only ignores rows hidden by autofilter (or in xl2003
cells in rows that have been hidden manually).