The mode function

  • Thread starter Thread starter Doug
  • Start date Start date
D

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)
 
With your data in A1 thru A10,

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

will return Finance
 
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
 
This array formula:

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

will work if there are empty cells in A1:A20, too.
 
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?
 
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)))
 
=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)))
Click to expand...
 
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?
 
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?
 
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?
Click to expand...
 
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
 
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
 
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
 
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.
 
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.
 
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).
 
Back
Top