
Search Excel Forum Posts, Tutorials, Macros, Tips, and More
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 Video Tutorials
Excel Magic Trick #145 p2 MODE IF
 See how to create a formula that will calculate the mode when you have more than 1 criterion. See the MODE and IF functions. MODE for more than one cr ...
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.
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:
=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
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
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.
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:
{=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 all,
quick question:
How would i find the mode of a range of numbers that changes?
For example, I want a simple out put of the Mode for the numbers after Aand D, and E and so on, without having to do it manually. A function perhaps?
I have attached spreadsheet. The 2 left columns are the raw data and the right columns are what i want the outcome (modes) to be. Is there a way I can do this without having to go through this manunally for every one?
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
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!
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 all,
Using the data from one of the other threads:
6 12 18 43 47 52
2 9 21 29 45 50
5 18 33 35 38 44
4 6 17 41 47 49
1 3 4 20 33 50
1 6 42 44 45 51
2 22 28 47 49 52
7 8 21 30 31 38
8 9 14 27 36 39
6 12 25 31 45 47
2 11 16 29 32 33
6 13 17 39 46 50
1 19 21 23 36 48
3 5 33 39 41 50
36 38 39 45 47 49
8 19 24 38 39 47
11 17 20 28 43 45
2 8 12 14 32 50
22 29 32 39 40 41
1 4 24 33 40 50
2 5 13 27 30 32
11 17 22 26 29 43
1 8 11 30 36 51
1 17 27 30 46 52
2 3 7 28 40 46
2 6 21 25 41 50
10 11 14 18 33 40
29 39 40 43 47 49
1 4 8 16 33 44
12 22 26 29 46 48
12 19 27 39 44 46
2 4 7 9 13 17
2 7 8 10 19 38
4 5 10 32 34 39
6 8 9 25 26 49
9 17 22 37 43 47
8 22 28 33 38 52
9 12 14 26 32 40
3 13 20 33 34 39
2 8 11 26 33 52
9 10 26 31 35 37
5 17 28 29 47 49
6 9 25 31 48 52
4 9 28 34 42 49
16 22 23 24 38 46
11 12 20 22 36 51
2 4 12 26 33 39
1 14 25 45 47 51
7 14 17 21 23 27
8 17 34 37 39 45
14 17 21 31 43 45
8 30 33 37 39 47
5 11 13 31 39 49
13 16 24 26 41 44
23 36 38 45 47 48
16 36 37 38 40 46
1 7 15 22 33 39
1 5 9 30 48 52
4 17 26 33 40 44
2 5 17 18 29 35
First I want to find mode of the first numbers of each row... easy right.. MODE(A2:A61)
The hard part for me is finding the mode of the second number, BUT only from all rows that start from the number that "Mode" provided using the first formula.
Hope that makes sense.
Thanks
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?
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
What formula do you use to look at a spread sheet and find the most frequently used number besides a MODE function. The problem I am haveing is I have used a MODE function to find the most frequent number, but now I need to find the second most used number and also the thrid and fourth most used numbers. But if I try and use a MODE function I do not know how to write it to not keep returning the most frequent number over and over???? Can ANYONE show me how to write a formula to not look for a value that has all ready been found in an array as the most frequent, I need to find the 5 most common numbers in an spreadsheet, bottom line...
Thanks
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
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 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
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), "")}
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
Hi,
I dont know why but when I try and use the "Mode" formula for a column of cells, I am not getting the mode result. I am using the mode formula for the entire B column Mode(B:B). This does not work.
When I specify the cell (B2: B54), it works.
This works fine for other statistic formulae like mean, median, etc.... when I select the entire column of data cells.
If I have not made myself clear with the question, please let me know and I will add steps to what I am doing.
Thanks!
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?
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
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
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:
=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)

