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
MODE is for numbers. Here is what you can do
Let's say your range is in A1:A10
1. In B1, put =COUNTIF($A$1:$A$10, A1)
2. Copy B1 to B2:B10
(now Column B has the frequency of each value)
3. In an empty cell, put =INDEX(A1:A10, MATCH( MAX(B1:B10),B1:B10))
That will find the max frequency from column B and then look up the corresponding value from column A. If there is a tie, it will pick one of the maximum values.
Thanx for the reply
unfortunately couldnt get your formula to work
Last edited by arminius; 02-19-2007 at 06:27 PM.
Assuming you have no blanks in your range you can use a single formula to find the "mode" text value
=INDEX(A1:A6,MODE(MATCH(A1:A6,A1:A6,0)))
comfirmed with CTRL+SHIFT+ENTER
note: gives N/A if all values are different, if there is a tie formula will return the one that occurs first
Brilliant
thanks a lot daddylonglegs works perfect
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks