Mode Function Question

=MODE(A2..A2650)

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?

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 non-existent) 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 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), "")}

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

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!

Co-worker 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
-----------
1|1,2 | 2,3
2|3 | 9,1
3|22,3| 2,4
4|1 | 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)

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

1-2
1-3
2-0
2-0
1-1
2-1

the most frequent of the above would be 2-0 but I cant find a function that will do this

Thanks in advance