Mode Function Question 


Mode Function Question  Excel 
View Answers 
I want to use a formula to detect which name is appearing most in a selected range of cells. The Mode formula gives this result but for numbers. I was thinking of the probability of having to combine the LEN function with the MODE function. Can anyone help me??
Similar Excel Tutorials
MODE()  Find Most Repeated Value in a List in Excel
The MODE() function in Excel allows you to quickly find the most repeated or frequently occuring value within a lis ...
The MODE() function in Excel allows you to quickly find the most repeated or frequently occuring value within a lis ...
Prevent Charts from Printing in Excel
This is how you prevent a chart from appearing when you print from Excel. This is a great feature when you want to ...
This is how you prevent a chart from appearing when you print from Excel. This is a great feature when you want to ...
Select Cells in Excel using Macros and VBA
This is actually a very easy thing to do and only requires a couple lines of code. Below I will show you how to sel ...
This is actually a very easy thing to do and only requires a couple lines of code. Below I will show you how to sel ...
How to Find and Understand Excel Functions
In this tutorial I am going to show you how to find and understand new Excel Functions. If you go to the Formulas t ...
In this tutorial I am going to show you how to find and understand new Excel Functions. If you go to the Formulas t ...
Helpful Excel Macros
Determine if a Cell Contains a Function in Excel  Great for Conditional Formatting and Validation  UDF
 Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return
 Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return
Print Preview Display for Specific Worksheets in Excel
 This free Excel macro allows you to display the print preview mode or window in Excel for a specified Excel worksheet.
 This free Excel macro allows you to display the print preview mode or window in Excel for a specified Excel worksheet.
Print Preview Screen Display for The Entire Workbook in Excel
 This free Excel macro allows you to quickly and easily display the print preview window or mode for the entire Excel wor
 This free Excel macro allows you to quickly and easily display the print preview window or mode for the entire Excel wor
Print Preview Screen Display for The Current or Selected Worksheets in Excel
 This free Excel macro allows you to display the print preview screen or window for the current or selected worksheets in
 This free Excel macro allows you to display the print preview screen or window for the current or selected worksheets in
Calculate the Future Value (FV) of Compound Interest in Excel  UDF Macro
 This UDF (user defined function) calculates the Future Value of Compound Interest in Excel. The mathematical formula th
 This UDF (user defined function) calculates the Future Value of Compound Interest in Excel. The mathematical formula th
Similar Topics
I have a long list of figures of which I use the mode function to isolate the numbers occurring the most in the series, I have a requirement to use mode function 2 times to isolate the top 2 modal figures.
I would like to run the mode function on series C2:F1982, obtaining the modal point from this series, I would then like to exclude the result and run the mode function again to get the second most frequently occurring number.
Right now I am running mode function and then deleting the first result to get the second modal point but it's becoming too time consuming to replace all those deleted figures once I am done.
The mode in this example would be 135 since it occurs the most (3 times) but excluding this result of 135 we get 98 (which occurs 2 times), hence the mode figure would be 135, the second place runner up would be 98.
How can I perform the mode and secondary mode function with excel automatically?
98
135
147
135
98
135
thanks,
D.
I would like to run the mode function on series C2:F1982, obtaining the modal point from this series, I would then like to exclude the result and run the mode function again to get the second most frequently occurring number.
Right now I am running mode function and then deleting the first result to get the second modal point but it's becoming too time consuming to replace all those deleted figures once I am done.
The mode in this example would be 135 since it occurs the most (3 times) but excluding this result of 135 we get 98 (which occurs 2 times), hence the mode figure would be 135, the second place runner up would be 98.
How can I perform the mode and secondary mode function with excel automatically?
98
135
147
135
98
135
thanks,
D.
Hello, I have a spreadsheet with columns holding around 3,000 values each. I would like to use the MODE function to determine the most frequently occurring value in each column. My research has told me that Excel 2003 limits the number of values for MODE to 30. Is this correct in all uses of MODE, or only if I actually place 30 cell references in the formula (i.e., =MODE(A2, A3, A4, etc.)
If I use a mode formula like:
Code:
will it return a valid (reliable) value?
If not, how can I work around this?
Thanks in advance for helping this newbie!
If I use a mode formula like:
Code:
=MODE(A2..A2650)
will it return a valid (reliable) value?
If not, how can I work around this?
Thanks in advance for helping this newbie!
hi
not sure where to post this...?
im trying to create a program for year 7 students to teach them about mode mean, median and range
i've enclosed the file....
the problem is i have 510 numbers being generated randomly using the following code:
=rand()*10
the numbers generated are between 110 thats working fine....
the only thing is wen i type the mode function for a cell
=mode(a1:e2)
i get the ## error or n/a
its as if the mode cannot idnetify the random numberS?
could ne one take a look cus i cant explain it good..
thanx
not sure where to post this...?
im trying to create a program for year 7 students to teach them about mode mean, median and range
i've enclosed the file....
the problem is i have 510 numbers being generated randomly using the following code:
=rand()*10
the numbers generated are between 110 thats working fine....
the only thing is wen i type the mode function for a cell
=mode(a1:e2)
i get the ## error or n/a
its as if the mode cannot idnetify the random numberS?
could ne one take a look cus i cant explain it good..
thanx
hi
not sure where to post this...?
im trying to create a program for year 7 students to teach them about mode mean, median and range
i've enclosed the file....
the problem is i have 510 numbers being generated randomly using the following code:
=rand()*10
the numbers generated are between 110 thats working fine....
the only thing is wen i type the mode function for a cell
=mode(a1:e2)
i get the ## error or n/a
its as if the mode cannot idnetify the random numberS?
could ne one take a look cus i cant explain it good..
thanx
not sure where to post this...?
im trying to create a program for year 7 students to teach them about mode mean, median and range
i've enclosed the file....
the problem is i have 510 numbers being generated randomly using the following code:
=rand()*10
the numbers generated are between 110 thats working fine....
the only thing is wen i type the mode function for a cell
=mode(a1:e2)
i get the ## error or n/a
its as if the mode cannot idnetify the random numberS?
could ne one take a look cus i cant explain it good..
thanx
Hi,
This is a question regarding Excelisfun's video posted at youtube;
Excel Magic Trick 536: List All Modes in Multimodal Data Set MODE.MULT Excel 2010 Function
http://www.youtube.com/watch?v=ewF8E982bfs
The trick introduced in this video by using iferror function to remove #N/A doesn't work for me. And I downloaded the orginal file, which, to my surprise, has this #N/A message, too. I also tried this following solution,
{=IF(ISNA(MODE.MULT(A6:A23)),"",MODE.MULT(A6:A23))}
It still doesn't work.
Please help and this is an Excel 2010 function. Mr. Excelisfun recommends me to post this question here.
More, if there is only 1 mode in the array, the MODE.MULT will repeat the mode for n times. n = the number of the cells covered by the array formula. Here is an example,
19 5 4 16 20 15 5 14 16 15 5
=MODE.MULT(A1:A11) = {5,5,5} (if I copy this formula to B1:B3)
Thanks a lot.
This is a question regarding Excelisfun's video posted at youtube;
Excel Magic Trick 536: List All Modes in Multimodal Data Set MODE.MULT Excel 2010 Function
http://www.youtube.com/watch?v=ewF8E982bfs
The trick introduced in this video by using iferror function to remove #N/A doesn't work for me. And I downloaded the orginal file, which, to my surprise, has this #N/A message, too. I also tried this following solution,
{=IF(ISNA(MODE.MULT(A6:A23)),"",MODE.MULT(A6:A23))}
It still doesn't work.
Please help and this is an Excel 2010 function. Mr. Excelisfun recommends me to post this question here.
More, if there is only 1 mode in the array, the MODE.MULT will repeat the mode for n times. n = the number of the cells covered by the array formula. Here is an example,
19 5 4 16 20 15 5 14 16 15 5
=MODE.MULT(A1:A11) = {5,5,5} (if I copy this formula to B1:B3)
Thanks a lot.
Not sure if this is a General forum question or a VBA problem, but since im not having any luck in the general forum I will try here.
I am trying to create a mode formula that returns the mode for a specific range when all criteria is satisfied. So basically I have a long list of data I want the formula to search through all the criteria columns and then calculate the mode for only those corresponding data rows in column N that satisfy my criteria.
I have tried various if formulas and just cant do it!! Anyone got any ideas?
Code:
The idea would be to use the conditional formula below BUT I don't want the calculation to return the mode for the entire range I only want the mode for the numbers that meet my conditions.
for example say I have data in in the range X1:X100 and in that rows 24, 33,34 and 99 have satisfied my criteria I then need the formula to return the mode for the range X24, X33, X34 and X99.
I am trying to create a mode formula that returns the mode for a specific range when all criteria is satisfied. So basically I have a long list of data I want the formula to search through all the criteria columns and then calculate the mode for only those corresponding data rows in column N that satisfy my criteria.
I have tried various if formulas and just cant do it!! Anyone got any ideas?
Code:
{=Mode(if(and(B5:B8219=B5, and(C5:C8219=C5, and(D5:D8219=D5, and(E5:E8219=E5)))) N5:N8219), "")}
The idea would be to use the conditional formula below BUT I don't want the calculation to return the mode for the entire range I only want the mode for the numbers that meet my conditions.
for example say I have data in in the range X1:X100 and in that rows 24, 33,34 and 99 have satisfied my criteria I then need the formula to return the mode for the range X24, X33, X34 and X99.
Hello,
I have a large list of tenants down the page. Across the page I have 12 months of numbers related to their square footage. The Mode Function by itself works nicely when I am building out a table off of a data extract. The issue is how can I be certain the formula is picking up the right range? How can I make it more dynamic where it says Search this tenant on this data set. When you find it do a MODE on this range of cells?
Regards,
Anthony
In this old thread (which the software did not let me continue) Yogi mentions 2nd mode and third mode etc.
http://www.mrexcel.com/archive2/70100/81213.htm
In the above thread, Yogi has a nice formula for finding the "other mode", when the other mode is a tie. I am wondering  is there a simple formula
to find the 2nd (or 3rd) most frequent number. For example the (AFAIK nonexistent) formula or function would act like this:
1
1
1
3
3
2
"modelarge(range,2)" should return 3
http://www.mrexcel.com/archive2/70100/81213.htm
In the above thread, Yogi has a nice formula for finding the "other mode", when the other mode is a tie. I am wondering  is there a simple formula
to find the 2nd (or 3rd) most frequent number. For example the (AFAIK nonexistent) formula or function would act like this:
1
1
1
3
3
2
"modelarge(range,2)" should return 3
Hello!
I have a list with numbers and i can return the first and the next mode number with this formulas
=mode(A1:A11) and {=mode(if(A1:A11<>3;A1:A11))} as an array.(if 3 is the first mode number)
The problem is that my list needs to have a range form A11:A2000 including blank cells and the array formula above returns a zero because of the blank cells.
Is there a way to make the array formula work despite the fact of having blank cells?
I have attached a small example of my data
Thank you very much!
I have a list with numbers and i can return the first and the next mode number with this formulas
=mode(A1:A11) and {=mode(if(A1:A11<>3;A1:A11))} as an array.(if 3 is the first mode number)
The problem is that my list needs to have a range form A11:A2000 including blank cells and the array formula above returns a zero because of the blank cells.
Is there a way to make the array formula work despite the fact of having blank cells?
I have attached a small example of my data
Thank you very much!
When I use the Mode Function in Excel, I may get only one answer. For example, if I have a sequence of numbers like 1 5 5 8 8 9, the mode function only returns 5 as the most frequent number. Does it now show all the numbers that are most frequent if they are frequent the same amount?
Hi,
I searched the site for this question but didn't find it.
I'm using the mode function to find the most frequent value, but it only returns a value that repeats at least 3 times.
The problem is if i have 50 values and "x" repeats 3 times it's not really a valid result for me because it doesn't represent at least 50% of the sample.
How can i use MODE and IF in a way that if "x" represents at least 50% of the sample it returns the mode, if not it returns the average?
I searched the site for this question but didn't find it.
I'm using the mode function to find the most frequent value, but it only returns a value that repeats at least 3 times.
The problem is if i have 50 values and "x" repeats 3 times it's not really a valid result for me because it doesn't represent at least 50% of the sample.
How can i use MODE and IF in a way that if "x" represents at least 50% of the sample it returns the mode, if not it returns the average?
I have a column of data with 100 entries in it. I used the mode function to return the most frequent number. However, in the data, there happens to two sets of numbers with the same number entries, i.e. 6 occurrences of the number 15 and 6 occurrences of 31. However, the mode function will return just the first one, "15," and not return the second one, "31," also. How do I write the function so that it will return both?
Formula Result
=mode(B$3:B$103) 15
Thank you in advance
Bill
Hi,
I have a Mode() function in A11 referencing A1:A10. When a tie occurs (i.e., "A4=2, A6=2 while A8=5, A9=5"), I need the LAST Mode() result from the list (in this case "5")... Any ideas on how to do this?
Thanx,
Jeff
I have a Mode() function in A11 referencing A1:A10. When a tie occurs (i.e., "A4=2, A6=2 while A8=5, A9=5"), I need the LAST Mode() result from the list (in this case "5")... Any ideas on how to do this?
Thanx,
Jeff
Hi
I have a list of numbers that I want to find the MODE in:
0
0
1
2
0
0
3
3
2
2
2
0
0
But I want to be able to ignore the Zeros
How do i go about doing this so the mode function gives me the #2 only
Thank you
I have a list of numbers that I want to find the MODE in:
0
0
1
2
0
0
3
3
2
2
2
0
0
But I want to be able to ignore the Zeros
How do i go about doing this so the mode function gives me the #2 only
Thank you
HI
I want to calculate the mode of a column with =mode(B:B) I get the error #num! I assume it is looking at all the blank cells in the column, but I will want to continually add numbers to the column and get the mode.
How do get the formula to ignore blank cells, I have tried different ways when calculating averages which work but they don't with mode?
Any ideas please?
I want to calculate the mode of a column with =mode(B:B) I get the error #num! I assume it is looking at all the blank cells in the column, but I will want to continually add numbers to the column and get the mode.
How do get the formula to ignore blank cells, I have tried different ways when calculating averages which work but they don't with mode?
Any ideas please?
I am trying to find the MODE, or most common letter in this case, within a range (G4:U80) based on criteria in G5:U81.
right now this is my formula an array
{=MODE(IF(C2:C78="SMITH",IF(G2:U78=1,IF(G3:U79="A",IF(ISNUMBER(G4:U80),G4:U80)))))}
it is returning #N/A
is there a better way to find what I am looking for without MODE? I thought MODE will only find a numerical value and when using text it needs to be something else.
many thanks
right now this is my formula an array
{=MODE(IF(C2:C78="SMITH",IF(G2:U78=1,IF(G3:U79="A",IF(ISNUMBER(G4:U80),G4:U80)))))}
it is returning #N/A
is there a better way to find what I am looking for without MODE? I thought MODE will only find a numerical value and when using text it needs to be something else.
many thanks
I am trying to create a mode formula that returns the mode for a specific range when all criteria is satisfied. So basically I have a long list of data I want the formula to search through all the criteria columns and then calculate the mode for only those corresponding data rows in column N that satisfy my criteria.
I have tried various if formulas and just cant do it!! Anyone got any ideas?
Code:
I have tried various if formulas and just cant do it!! Anyone got any ideas?
Code:
{=Mode(if(and(B5:B8219=B5, and(C5:C8219=C5, and(D5:D8219=D5, and(E5:E8219=E5)))) N5:N8219), "")}
I have pulled the mode from the sheet called Daily for cells B5:B33
=MODE(Daily!B5:B33)
I need the second and third most occurring figures, Im not sure if it matters but they are times
I tried this but I don't seem to be getting it right for the 2nd mode
=MODE(IF(Daily!B5:B33<>MODE(Daily!B5:B33),Daily!B5:B33,""))
Any help would be greatly appreciated or steering me into the right direction
Greetings all.
I am a very new user to Excel and I would appreciate any help I could get.
I have a list of integers (0 < n > 200) and I need to be able to tell which integer comes up most frequently. I know to use the MODE function for that.
But I also need to be able to tell which integer is 2nd most frequent and then 3rd most frequent, etc.
The MODE example only helps me with the most frequent number.
Can anyone help me with this?
Regards,
Mike
I am a very new user to Excel and I would appreciate any help I could get.
I have a list of integers (0 < n > 200) and I need to be able to tell which integer comes up most frequently. I know to use the MODE function for that.
But I also need to be able to tell which integer is 2nd most frequent and then 3rd most frequent, etc.
The MODE example only helps me with the most frequent number.
Can anyone help me with this?
Regards,
Mike
Coworker of mine wanted me to develop a "VLOOKUP Button" much like the SUM Button that enters the SUM function in the selected cell and leaves it in edit mode for the user to adjust the range.
To be honest, I didn't think this was a useful idea, but it did get me thinking, how do you create a macro that can begin to enter a function into a cell and leave it in edit mode? I haven't a clue so I thought I'd post the interesting question here.
Ideas?
To be honest, I didn't think this was a useful idea, but it did get me thinking, how do you create a macro that can begin to enter a function into a cell and leave it in edit mode? I haven't a clue so I thought I'd post the interesting question here.
Ideas?
Hi everyone 
I appreciate some insight on the formula mode.sngl: I am using this to look at some columns of numbers and I have found that lets say I have 10 columns of numbers and there are two repeating sets of numbers (2's) and (5's). Each one of these repeats three times  the way the formula reads it should return a 2 as the mode. What mine is doing is what ever number appears first in the list of numbers is the one excel is picking for the mode....example: 1,1,5,5,5,2,2,2,3,7 mode = 5 when it is supposed to return the lowest mode of multiple modes which should be 2 in this case. If I change the order of the numbers and put a 2 in the list ahead of the 5's and both repeating three times then the mode chooses 2 as the mode....What is going on here??
Thank you in advance 
Chris
Okay, this seems like a stupid question, but i'm stumped. I'm trying to use the mode function for set of data, like so =MODE(B2:B30). Sounds simple enough. Instead of returning the expected result, I get either 0 or #N/A. The data in the cells are basic number to two decimal points. I checked the cell format and it is a number format. Anyone have any ideas?
thanks
thanks
I had question regarding some code that acw posted regarding obtaining the 1st, 2nd, 3rd etc. MODE within a VBA UDF function. While this is a great function, I am wondering if there is a way to add functionality.
Specifically, I was wondering if anyone knows whether it is possible to pass a string of values to this function within a UDF; let me elaborate.
I currently have a function that does the following (since i do not have a method of posting an image at the moment),lets assume this is the worksheet, and my udf is called GetValues).
Column/Row
A  B

11,2  2,3
23  9,1
322,3 2,4
41  2
In my sheet, I have cells with numbers, some with 1, or some with more than 1 number within the cell (as shown above).
If I run my UDF, GetValues(A1:A4) it would basically pull out all the numbers into an array, which would be joined to output the value (assuming I use the join function with "," as the delimeter):
1,2,3,22,3,1
Here is the code for my function:

Function GetValues(mKa As Range)
Dim StackIt()
Dim i As Integer
i = 0
For Each mKa In mKa
ReDim Preserve StackIt(i)
StackIt(i) = Trim(mKa.Cells.Value)
i = i + 1
Next mKa
GetValues = Join(StackIt, ",")
End Function

Here is the function from a post by acw to obtain first, second, third mode etc.
MODER() Function from a post by acw:

Function moder(x As Range)
Dim modes As New Collection
For Each ce In x
If WorksheetFunction.CountIf(x, ce) = WorksheetFunction.CountIf(x, WorksheetFunction.Mode(x)) Then
On Error Resume Next
modes.Add Item:=ce, Key:=Str(ce)
End If
Next ce
For i = 1 To modes.Count
moder = moder & "," & modes(i)
Next i
moder = WorksheetFunction.Substitute(moder, ",", "", 1)
End Function

What I would like to be able to do, is take my joined array value(s), and pass it within my GetValues function to this MODER function; yielding a final output of 1,3 (the 2 modes obtained for the values A1:A4 that I put together with my GetValues function).
I have done an exhaustive search for trying to figure this out, however I have hit a dead end, so I am finally putting my first question out there on a coding board. If anyone could help I would greatly appreciate it. Thanks!
Chris
Specifically, I was wondering if anyone knows whether it is possible to pass a string of values to this function within a UDF; let me elaborate.
I currently have a function that does the following (since i do not have a method of posting an image at the moment),lets assume this is the worksheet, and my udf is called GetValues).
Column/Row
A  B

11,2  2,3
23  9,1
322,3 2,4
41  2
In my sheet, I have cells with numbers, some with 1, or some with more than 1 number within the cell (as shown above).
If I run my UDF, GetValues(A1:A4) it would basically pull out all the numbers into an array, which would be joined to output the value (assuming I use the join function with "," as the delimeter):
1,2,3,22,3,1
Here is the code for my function:

Function GetValues(mKa As Range)
Dim StackIt()
Dim i As Integer
i = 0
For Each mKa In mKa
ReDim Preserve StackIt(i)
StackIt(i) = Trim(mKa.Cells.Value)
i = i + 1
Next mKa
GetValues = Join(StackIt, ",")
End Function

Here is the function from a post by acw to obtain first, second, third mode etc.
MODER() Function from a post by acw:

Function moder(x As Range)
Dim modes As New Collection
For Each ce In x
If WorksheetFunction.CountIf(x, ce) = WorksheetFunction.CountIf(x, WorksheetFunction.Mode(x)) Then
On Error Resume Next
modes.Add Item:=ce, Key:=Str(ce)
End If
Next ce
For i = 1 To modes.Count
moder = moder & "," & modes(i)
Next i
moder = WorksheetFunction.Substitute(moder, ",", "", 1)
End Function

What I would like to be able to do, is take my joined array value(s), and pass it within my GetValues function to this MODER function; yielding a final output of 1,3 (the 2 modes obtained for the values A1:A4 that I put together with my GetValues function).
I have done an exhaustive search for trying to figure this out, however I have hit a dead end, so I am finally putting my first question out there on a coding board. If anyone could help I would greatly appreciate it. Thanks!
Chris
I have a spreadsheet with a bunch of numbers on it from cells c1:c1577. I would like to find the Mode, 2nd most common, 3rd, 4th etc. I have searched around a ton for this and can only seem to get the mode. I read somewhere that if i put the mode in cell L8 and then used the following forumla down from L9 that it should work. But it doesnt seem to be working i get an error. Can someone help me get this to work or show me another way.
Code:
Here is a copy of what i was trying to follow so you know where i got that idea.
Code:
Code:
=MODE(IF(COUNTIF(L$8:L8,C$1:C$1577)=0,C$1:C$1577+{0,0}))
Here is a copy of what i was trying to follow so you know where i got that idea.
Code:
Try this... Numbers in the range A2:A11 B2 = mode formula: =MODE(A2:A11) Enter this array formula** in B3 and copy down as needed: =MODE(IF(COUNTIF(B$2:B2,A$2:A$11)=0,A$2:A$11+{0,0})) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER)
Hi
New to the forum so not sure if this is the right place to ask
I have a list of results of which I need to find the most frequently occuring result, tried the mode function but this just comes up #N/A
Any help would be much appreciated.
Below is a small sample of the results
12
13
20
20
11
21
the most frequent of the above would be 20 but I cant find a function that will do this
Thanks in advance
New to the forum so not sure if this is the right place to ask
I have a list of results of which I need to find the most frequently occuring result, tried the mode function but this just comes up #N/A
Any help would be much appreciated.
Below is a small sample of the results
12
13
20
20
11
21
the most frequent of the above would be 20 but I cant find a function that will do this
Thanks in advance