| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
The mode function
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) -- Thank you! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
The mode function
Can't be used on text.
-- Gary''s Student - gsnu200908 "Doug" wrote: 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) -- Thank you! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
The mode function
What would qualify as a name? Would it work if I take out the spaces?
-- Thank you! "Gary''s Student" wrote: Can't be used on text. -- Gary''s Student - gsnu200908 "Doug" wrote: 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) -- Thank you! |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
The mode function
With your data in A1 thru A10,
=INDEX(A1:A10,MODE(MATCH(A1:A10,A1:A10,0))) will return Finance -- Gary''s Student - gsnu200908 "Doug" wrote: What would qualify as a name? Would it work if I take out the spaces? -- Thank you! "Gary''s Student" wrote: Can't be used on text. -- Gary''s Student - gsnu200908 "Doug" wrote: 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) -- Thank you! |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
The mode function
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)),CO UNTIF(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 Doug wrote: 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) -- Thank you! -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
The mode function
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 wrote: 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) -- Thank you! -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
The mode function
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? -- Thank you! "Dave Peterson" wrote: 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 wrote: 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) -- Thank you! -- Dave Peterson . |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
The mode function
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))) -- Thank you! "Dave Peterson" wrote: 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 wrote: 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) -- Thank you! -- Dave Peterson . |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
The mode function
=INDEX(S3:S1000,MODE(MATCH(IF(ISNA(S3:S1000),"",S3 :S1000),
IF(ISNA(S3:S1000),"",S3:S1000),0))) Doug wrote: 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))) -- Thank you! "Dave Peterson" wrote: 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 wrote: 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) -- Thank you! -- Dave Peterson . -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
The mode function
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? -- Thank you! "Dave Peterson" wrote: =INDEX(S3:S1000,MODE(MATCH(IF(ISNA(S3:S1000),"",S3 :S1000), IF(ISNA(S3:S1000),"",S3:S1000),0))) Doug wrote: 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))) -- Thank you! "Dave Peterson" wrote: 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 wrote: 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) -- Thank you! -- Dave Peterson . -- Dave Peterson . |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
The mode function
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? -- Thank you! "Dave Peterson" wrote: =INDEX(S3:S1000,MODE(MATCH(IF(ISNA(S3:S1000),"",S3 :S1000), IF(ISNA(S3:S1000),"",S3:S1000),0))) Doug wrote: 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))) -- Thank you! "Dave Peterson" wrote: 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 wrote: 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) -- Thank you! -- Dave Peterson . -- Dave Peterson . |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
The mode function
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. Doug wrote: 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? -- Thank you! "Dave Peterson" wrote: =INDEX(S3:S1000,MODE(MATCH(IF(ISNA(S3:S1000),"",S3 :S1000), IF(ISNA(S3:S1000),"",S3:S1000),0))) Doug wrote: 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))) -- Thank you! "Dave Peterson" wrote: 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 wrote: 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) -- Thank you! -- Dave Peterson . -- Dave Peterson . -- Dave Peterson |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
The mode function
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 |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
The mode function
Another one which works with blank cells:
=AND(NOT(OR(ISBLANK(A1:A10))),ISERROR(MODE(MATCH(A 1:A10,A1:A10,0)))) [array-entered] Regards, Bernd |
|
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
The mode function
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 -- Thank you! "Dave Peterson" wrote: 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. Doug wrote: 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? -- Thank you! "Dave Peterson" wrote: =INDEX(S3:S1000,MODE(MATCH(IF(ISNA(S3:S1000),"",S3 :S1000), IF(ISNA(S3:S1000),"",S3:S1000),0))) Doug wrote: 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))) -- Thank you! "Dave Peterson" wrote: 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 wrote: 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) -- Thank you! -- Dave Peterson . -- Dave Peterson . -- Dave Peterson . |
|
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
The mode function
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 wrote: 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 -- Thank you! "Dave Peterson" wrote: 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. Doug wrote: 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? -- Thank you! "Dave Peterson" wrote: =INDEX(S3:S1000,MODE(MATCH(IF(ISNA(S3:S1000),"",S3 :S1000), IF(ISNA(S3:S1000),"",S3:S1000),0))) Doug wrote: 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))) -- Thank you! "Dave Peterson" wrote: 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 wrote: 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) -- Thank you! -- Dave Peterson . -- Dave Peterson . -- Dave Peterson . -- Dave Peterson |
|
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
The mode function
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. -- Thank you! "Dave Peterson" wrote: 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 wrote: 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 -- Thank you! "Dave Peterson" wrote: 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. Doug wrote: 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? -- Thank you! "Dave Peterson" wrote: =INDEX(S3:S1000,MODE(MATCH(IF(ISNA(S3:S1000),"",S3 :S1000), IF(ISNA(S3:S1000),"",S3:S1000),0))) Doug wrote: 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))) -- Thank you! "Dave Peterson" wrote: 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 wrote: 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) -- Thank you! -- Dave Peterson . -- Dave Peterson . -- Dave Peterson . -- Dave Peterson . |
|
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
The mode function
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). Doug wrote: 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. -- Thank you! "Dave Peterson" wrote: 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 wrote: 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 -- Thank you! "Dave Peterson" wrote: 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. Doug wrote: 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? -- Thank you! "Dave Peterson" wrote: =INDEX(S3:S1000,MODE(MATCH(IF(ISNA(S3:S1000),"",S3 :S1000), IF(ISNA(S3:S1000),"",S3:S1000),0))) Doug wrote: 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))) -- Thank you! "Dave Peterson" wrote: 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 wrote: 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) -- Thank you! -- Dave Peterson . -- Dave Peterson . -- Dave Peterson . -- Dave Peterson . -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Mode Function or MODE Function -- Can't get it to work!! | Excel Discussion (Misc queries) | |||
| Mkae Mode function return "" instead of #N/A when there is no Mode | Excel Discussion (Misc queries) | |||
| Mkae Mode function return "" instead of #N/A when there is no Mode | Excel Discussion (Misc queries) | |||
| MODE Function | Excel Worksheet Functions | |||
| Mode Function | Excel Programming | |||
Linear Mode
