How do I find the highest of the most repeating values in a list of numbers ie. the max of the modes

Hi,

I have a list of numbers and I need to find the highest of the most
frequent occurrances.

For example:

List 1
1
3
3
4
4

Should return the value 4 because its the highest of the most frequent
occurrances of any number.

List 2
1
3
3
3
4
4

Should return 3 because it's the most frequent occurrance outright.

I've tried using the mode command but it returns the first of the most
frequent occurances but I want the highest of the most frequent
occurrances.

Any help would be greatly appreciated.

Thanks

Ana
0
anasttin (4)
1/6/2011 12:19:03 AM
excel 39879 articles. 2 followers. Follow

2 Replies
520 Views

Similar Articles

[PageSpeed] 46

=max(mode(A1:A10))

Replace the range with required range.

On Wed, 5 Jan 2011 16:19:03 -0800 (PST), Kim <anasttin@gmail.com>
wrote:

>Hi,
>
>I have a list of numbers and I need to find the highest of the most
>frequent occurrances.
>
>For example:
>
>List 1
>1
>3
>3
>4
>4
>
>Should return the value 4 because its the highest of the most frequent
>occurrances of any number.
>
>List 2
>1
>3
>3
>3
>4
>4
>
>Should return 3 because it's the most frequent occurrance outright.
>
>I've tried using the mode command but it returns the first of the most
>frequent occurances but I want the highest of the most frequent
>occurrances.
>
>Any help would be greatly appreciated.
>
>Thanks
>
>Ana
0
1/8/2011 3:58:02 PM
"Kim" <anasttin@gmail.com> wrote in message news:cc273eb4-7232-4269-854b-348c8214f331@c39g2000yqi.googlegroups.com...
> Hi,
> 
> I have a list of numbers and I need to find the highest of the most
> frequent occurrances.
> 
> For example:
> 
> List 1
> 1
> 3
> 3
> 4
> 4
> 
> Should return the value 4 because its the highest of the most frequent
> occurrances of any number.
> 
> List 2
> 1
> 3
> 3
> 3
> 4
> 4
> 
> Should return 3 because it's the most frequent occurrance outright.
> 
> I've tried using the mode command but it returns the first of the most
> frequent occurances but I want the highest of the most frequent
> occurrances.
....

Hi Ana.

The mode command would do what you want 
if the list was sorted highest first.

If sorting directly is not convenient, or not possible,
then the sorting could be done in the formula:
=MODE(LARGE(A2:A6,ROW(A2:A6)-1))
applying to List 1 (in A1 and down) of your example.

Hans T.

0
Hans
1/9/2011 1:23:19 AM
Reply:

Similar Artilces:

count a pair of numbers in row in a table
Hello, my question is: we have the following table: 34 29 13 15 7 15 8 40 11 24 13 6 8 21 38 9 17 23 1 4 22 38 42 37 16 1 18 11 37 41 5 42 18 33 45 9 1 21 41 15 41 1 27 23 42 23 29 7 38 18 42 12 26 34 36 and this one in another sheet 1 2 3 1 2 3 I want to fill the second table with the sum of how many times the numbers if each row and column appear in the same row in the first table. for example: how many time the numbers 2 and 3 appear together in the same row on the first table Nik, Assume t...

Display Field Name as Text Listing if Value True from Checkbox Fields
Hi everyone! I have 40 or so columns in a table using the yes/no data type. Each record in the table represents an audit of a paper form. Those items filled incorrectly or incompletely on the paper form receive a check on an access form during the audit process. I would like to create a feedback form based on the audit record that displays a listing of fields that received the check boxes, or rather, were incomplete or incorrect on paper form that was audited. On the feedback form, I only want to display something like this: "Your form had the following errors: ErrorFieldOneName Erro...

search a matrix for values
OK, this is a bit of a tricky problem.... i have a 7*7 matrix in excel, which is full of numbers... i want to search in only half the matrix, where half is from cell1-1, to cell 7-1 (i.e. top left to bottom right). what i want to search is for numbers a certain value, say X. what i then want to do is tablulate these numbers in one column, and then tablue late their position as a reference to the matrix (i.e. their matrix position). how on earth can i do this? Phil I have assumed that the matrix is in A1:G7 In A10 I entered =INDEX($A$1:$H$7,ROW(A1),ROW(A1)) and copied down to A16 In...

HELP-SQL Server 2005 Rollup Query for Distinct Values
Folks, I realize that group by rollup for distinct values in SQL Server 2005 is not supported. Can you please help me in writing the following situation. I have a table which keeps track of an Employee working in multiple offices located in different states. I need to get the count of Employees working in each office by state and/or office code. Please keep in mind that I have simplified the question using this example. My case actually has close to 7 dimensions. Table=WorkPlace State OfficeCode EmployeeId ========================== NJ O1 ...

can I add a link in Publisher to display, "join our email list"?
I am creating a fairly simply website and need to add a button for visitors to join our email list. Is this possible in Publisher? Please post your question in the web group and we will try to help you there: news://msnews.microsoft.com/microsoft.public.publisher.webdesign or http://www.microsoft.com/office/community/en-us/default.mspx?dg=microsoft.public.publisher.webdesign&lang=en&cr=US Always include the version of Publisher you are using, the URL of your site and as much detail as possible. DavidF "mfreecox" <mfreecox@discussions.microsoft.com> wr...

Setting up own block list (e2k3)
hello all, tried to create my own block list for exchange 2003 but i got an error. using telnet on port 25 ##### 220 xion.hybrid.intra Microsoft ESMTP MAIL Service, Version: 6.0.3790.0 ready ... helo 250 xion.hybrid.intra Hello [192.168.0.2] mail from:spam@spam.com 250 2.1.0 spam@spam.com....Sender OK rcpt to:support 421 4.1.0 Dropping connection due to an error on this server Connection to host lost. ##### everything runs fine when i disable my connection filtering rule. someone an idea ? tia ...

Algorithm to combine identical items in a list
I have a rather large worksheet that contains a list of all items in a warehouse inventory. I would like to be able to combine duplicate items as new inventory is added. Each item has (among other fields) an item ID, a color and a condition. I would like to combine only the lines where all of these fields are identical, summing the quantities in each record, and recalculating the cost based on the cost field in each record. Currently, I do this by starting at the top of the list and comparing each record to all of the records below it to the end of the list. This is rather time-cons...

Authentication mode
Hi: Currently I have installed the MS CRM 1.2 Server on a server that host another application(sharepoint portal server & project server), this application requires SQL Authentication for work,but CRM requieres Windows Authentication Mode; at least the CRM implementacion guide document recommends this type of authentication, is there any way for work with both applications on same server ?. Microsoft CRM can work with SQL in mix mode authentication. Frank Lee Workopia, Inc. http://www.workopia.com/Links.htm San Francisco, CA "Rafael Diaz" wrote: > Hi: > >...

Any idea why this is giving me a #Value! Error?
Function FrstLtrs(MyStr As String) As String Dim temp Dim i As Long TmpStr = Split(Trim(MyStr)) 'MsgBox "String" + TmpStr For i = 0 To UBound(TmpStr) If Not (UCase(TmpStr) = "OF") And Not (UCase(TmpStr) = "FOR") And Not (UCase(TmpStr) = "THE") And _ Not (UCase(TmpStr) = "AND") And Not (UCase(TmpStr) = "A") Then If Asc(Left(TmpStr(i), 1)) >= 65 And _ Asc(Left(TmpStr(i), 1)) <= 90 Then FrstLtrs = FrstLtrs & Left(TmpStr(i), 1) End If End If Next End Function Find the mo...

Restricting number of viewings?
Without using macros, is there a way to allow someone to view an application only once or twice, after which point they could no longer view the spreadsheet (for example, if I want to send a worksheet sample that I don't want to be used permenantly). I don't think so, you can restrict access with macros but this can be overcome by opening the workbook with macros disabled. You can also write code to prevent a workbook being opened unless macros are enabled, but this is overcome quite easily by anyone with a little knowledge of Excel and VBA. In short, Excel is not designed for securit...

Page numbering 12-04-09
I have taken the trouble to review all the related previous posts but do not see the answer to meet my needs. I have a 7 page document which consists of a front and back cover 2 pages each and so far 3 pages of report. Page 2 is followed by a section break next page while page 5 is followed by a section break next page. In addition I also put a section break at the end of the document which I should not need. Numbering starts on page 3 as page 1 of Y which seems to work fine. However, page 4 and 5 do not display page numbers while page 6 displays page 1 of 6 and page 7 i...

how to trend a value every 5 minutes
I have a valve linked to excell and would like to chart it to a line graph every 5 minutes Andrew, 1) Format cell A1 to Time (13:30) in the menu. 2) Format cell B1 to Custom (hh:mm;@) in the menu. 3) In cell A2, type"=SUM(A1+$B$1) This will add the start time to whatever value you have in cell B2, right now you want 5 minutes. 4) Copy and Paste A2 down the column until you have enough data. 5) If you decide to change the interval, all you ahve to do is change the time interval in cell B2. "andrew" wrote: > I have a valve linked to excell and would like to ch...

Match formula to match values in multiple columns
Hi all, does any friend know that how can I make below formula work MATCH(A2,$K$2:$M$30,0) I am not sure I have understood correectly. Please go through the below example With data as below if you need to retrive the name of the 1st Rank holder from London. D2 = 1 D3 = London In D4 apply the below formula =INDEX($B$2:$B$9,MATCH(1,($A$2:$A$9=D2)*($C$2:$C$9=D3),0)) Please note that this is an array formula. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar'...

Different background color depending on x-axis value
Hi! Is there any way to have the background in a chart have differen colors depending on x-axis values? Like x-axis value 1-10 => blue background x-axis value 10-20 => red background Thank -- RealRave ----------------------------------------------------------------------- RealRaven's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3705 View this thread: http://www.excelforum.com/showthread.php?threadid=56778 Here are a few of the ways you can get custom background fills for your charts" http://PeltierTech.com/Excel/Charts/ChartIndex.html#Backgro...

how to count if the value falls between a date range
example 1/1/2005 1/2/2005 = 2 2/1/2005 2/2/2005 = 2 I am trying to get the ount if the value is between 1/12005 and 1/3/2005 and similarly 2/1/2005 and 2/3/2005 =countif(a1:a10,">="&date(2005,1,1)) - countif(a1:a10,">"&date(2005,1,3)) (I'm not sure what between means--include the end dates or not???) joe wrote: > > example > 1/1/2005 > 1/2/2005 = 2 > 2/1/2005 > 2/2/2005 = 2 > > I am trying to get the ount if the value is between 1/12005 and 1/3/2005 and > similarly 2/1/2005 and 2/3/2005 -- Dave Peterson Dave, ...

PO not listed for Invoicing
We have a Drop-ship PO with status Released and Revision 0. We can view it under All Purchase Order Transaction window. But when we try to invoice it from eith View->Purchasing Lists window or POP window, it's not listed under the Vendor name. Any help is appreciated. Any chance already assigned to an Invoice Batch? -- Mick "ThongD" wrote: > We have a Drop-ship PO with status Released and Revision 0. We can view it > under All Purchase Order Transaction window. But when we try to invoice it > from eith View->Purchasing Lists window or POP window, it&...

How can I go to a specific page number in a large document
How do I go to a specific page number in a large word document. Read the replies to your earlier identical post -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>>< ><<> <>>< <>><<> "yisaajao" <yisaajao@discussions.microsoft.com> wrote in message news:CFC4289D-FC93-4C1B-951B-DB08CED833...

How do I turn the pivot chart into a list with all cells filled?
I have the pivot chart and would like to copy and paste it so that all fields are filled Select the pivot table. Choose Edit>Copy Select the cell where you'd like to paste the copy Choose Edit>Paste Special Select Values, click OK There are instructions here for filling the blanks: http://www.contextures.com/xlDataEntry02.html gianna wrote: > I have the pivot chart and would like to copy and paste it so that all fields > are filled -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html ...

what causes the recently used file list option to be unavailable .
Tools / Options / Recently used file list is greyed out - How do I correct this ? John You don't say which version so try searching the knowledge base http://support.microsoft.com/default.aspx With a search string of MRU Disabled in Excel -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS "JohnPrice" <JohnPrice@discussions.microsoft.com> wrote in message news:ECA43906-EC33-4B92-8580-39923E449B9C@microsoft.com... > Tools / Options / Recently used file list is greyed out - How do I correct > this ? Hi! So...

Printing in Landscape mode
This is a multi-part message in MIME format. ------=_NextPart_000_000B_01C8C04C.CC21E500 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi, Is there a code snippet in which I can set the printer print-mode to = Landscape? Ideally, I want to set it in the MFC CView::OnPreparePrinting() method = call. If you have any suggestions... please help. King ------=_NextPart_000_000B_01C8C04C.CC21E500 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD H...

SPAM LISTING
Is there somewhere, on the internet, a list of SPAM sender that I can use to put filtering on my Exchange 2003 server? I have started to enter theme manualy but it's a long process!!!! Thanks! Jean-Seb I am using NoSPAMProxy. It has a cool monitor where I can blacklist something with one click. "John Smith" <john.smith@aol.com> schrieb im Newsbeitrag news:j5Whc.33230$CO3.1123537@news20.bellglobal.com... > Is there somewhere, on the internet, a list of SPAM sender that I can use to > put filtering on my Exchange 2003 server? I have started to enter theme > m...

maling list
how do i create a mailing list, or transfer one from yahoo contacts "omtradelaw" <omtradelaw@discussions.microsoft.com> wrote in message news:1EA2BADE-2D73-498E-AC3A-A3A05DADDA1D@microsoft.com... > how do i create a mailing list, Contacts - New - Distribution List > or transfer one from yahoo contacts Get the Yahoo Intellisynch. ...

How To find identical values
Hi I have a sheet with in which i like to find identical values and report in how many times does this value appear. A B C D x y z x =3 r x r y =1 f f z z =2 r =1 f =2 I have tried several functions but no luck, can anyone tell me how to do this? Thanks in advance marcgijs...@hotmail.com wrote: > Hi > I have a sheet with in which i like to find identical values and report > in how many times does this value appear. > A B C D > x y z x =3 > r x r y =1 > f f z z =2 > r =1 &g...

Find the n-th occurance of a sustring within a string
One can use SEARCH() or FIND() to find a string within another. And, if you wanted to find the first few, you can nest the calls and it probably works ok. But, this has its limits. Lets say you wanted to find the 10th occurance of a particular string within a string, now the formula gets really hairy, if you nest. Any clever ideas out there to find the n-th occurance of a string within another? Thanks, tom Hi Tom, Suppose the string you want to search is in A1, the substring you want to find is in A2 and the substring instance you want to find is in A3, then the following formul...

Timesheet Pseudo Tied Mode
I'm hoping that I might have a colleague out there who could help me offline figure out how to install this open source utility (unsupported by Microsoft but apparently works OK for some of their clients) that streamlines the Timesheet process (automatically synchronizes the data into My Tasks whenever a timesheet is saved). I'm not sure where to start and may need someone to bounce questions off of - especially if you've been successful with it. Thanks, Andy Novak UNT Hi Anovak, We installed it (still using the version 1.3 based on event handler) and we're usi...