Re: $$Excel-Macros$$ How do I find the highest of the most repeating values in a list of numbers ie. the max of the modes
Hi All, Have a look in the attached excel and you can find the solution. The formula is given below for your reference. Adapt the Cell Range A3:A8 to your desired Range. =IF(COUNTIF(A3:A8,LARGE(A3:A8,1))=COUNTIF(A3:A8,MODE(A3:A8)),LARGE(A3:A8,1),MODE(A3:A8)) *Suggestions For All* * * 1) Always try to avoid the usage of the helper column(s). 2) Never go for array formula when the same can be achieved by normal one. 3) Please don't go for VBA (Macro) solution for simple things when excel holds lot of functions to do the work as expected (Like the above). --- Ms.Exl.Learner --- On Thu, Jan 6, 2011 at 6:17 AM, Kim anast...@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 Kim -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts -- -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel Solution (9-Jan-2011).xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ How do I find the highest of the most repeating values in a list of numbers ie. the max of the modes
Hi Kim, If you Excel 2010, then the job would be much easier. =Max(Mode.Mult(A1:A10)) This function is not available in Excel 2003. Cheers Valli On Jan 6, 11:16 am, Kim anast...@gmail.com wrote: Thanks so much Ashish but the problem is in the temporary column. I really need to do it in one cell only without introducing any new data. Any idea on how I can achieve that? Again, thanks for your help. Kim On Jan 6, 4:07 pm, ashish koul koul.ash...@gmail.com wrote: check the attachment see if it helps On Thu, Jan 6, 2011 at 6:17 AM, Kim anast...@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 Kim -- ------ Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials athttp://www.excel-macros.blogspot.com 4. Learn VBA Macros athttp://www.quickvba.blogspot.com 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall;... -- *Regards* * * *Ashish Koul* *akoul*.*blogspot*.com http://akoul.blogspot.com/ *akoul*.wordpress.com http://akoul.wordpress.com/ My Linkedin Profile http://in.linkedin.com/pub/ashish-koul/10/400/830 P Before printing, think about the environment. sample datat.xls 24KViewDownload- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
Re: $$Excel-Macros$$ How do I find the highest of the most repeating values in a list of numbers ie. the max of the modes
Do you care if it is done with a macro? Option Explicit Public Dict_Vals Sub CheckVals() Dim KeyVals, R, MaxVal, MaxCnt, RowCnt Set Dict_Vals = CreateObject(Scripting.Dictionary) Dict_Vals.RemoveAll RowCnt = Application.WorksheetFunction.CountA(Range(A1:A65000)) For R = 1 To RowCnt If (Dict_Vals.exists(Sheets(Sheet1).Cells(R, A).Value)) Then Dict_Vals.Item(Cells(R, A).Value) = Dict_Vals.Item(Cells(R, A).Value) + 1 Else Dict_Vals.Add (Cells(R, A).Value), 1 End If Next R MaxVal = 0 MaxCnt = 0 KeyVals = Dict_Vals.keys For R = 0 To UBound(KeyVals) If Dict_Vals.Item(KeyVals(R)) MaxCnt Then MaxVal = KeyVals(R) MaxCnt = Dict_Vals.Item(KeyVals(R)) Debug.Print VAL: MaxVal Cnt: MaxCnt ElseIf Dict_Vals.Item(KeyVals(R)) = MaxCnt Then If KeyVals(R) MaxVal Then MaxVal = KeyVals(R) MaxCnt = Dict_Vals.Item(KeyVals(R)) End If End If Next R MsgBox VAL: MaxVal Chr(13) Cnt: MaxCnt End Sub Paul From: Kim anast...@gmail.com To: MS EXCEL AND VBA MACROS excel-macros@googlegroups.com Sent: Thu, January 6, 2011 1:16:15 AM Subject: Re: $$Excel-Macros$$ How do I find the highest of the most repeating values in a list of numbers ie. the max of the modes Thanks so much Ashish but the problem is in the temporary column. I really need to do it in one cell only without introducing any new data. Any idea on how I can achieve that? Again, thanks for your help. Kim On Jan 6, 4:07 pm, ashish koul koul.ash...@gmail.com wrote: check the attachment see if it helps On Thu, Jan 6, 2011 at 6:17 AM, Kim anast...@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 Kim -- ------ Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials athttp://www.excel-macros.blogspot.com 4. Learn VBA Macros athttp://www.quickvba.blogspot.com 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall;... -- *Regards* * * *Ashish Koul* *akoul*.*blogspot*.com http://akoul.blogspot.com/ *akoul*.wordpress.com http://akoul.wordpress.com/ My Linkedin Profile http://in.linkedin.com/pub/ashish-koul/10/400/830 P Before printing, think about the environment. sample datat.xls 24KViewDownload- Hide quoted text - - Show quoted text - -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
Re: $$Excel-Macros$$ How do I find the highest of the most repeating values in a list of numbers ie. the max of the modes
thanks Ashish, really appreciated.
From: ashish koul koul.ash...@gmail.com
To: excel-macros@googlegroups.com
Sent: Thu, 6 January, 2011 11:50:17 AM
Subject: Re: $$Excel-Macros$$ How do I find the highest of the most repeating
values in a list of numbers ie. the max of the modes
its array formula so u have to press ctrl +shift +enter then {} it will come
automatically
for offset check help =
MAX(IF(F2:F9=MAX(F2:F9),ROW(F2:F9),)) it will return the maximum row no
which
is having max value in range f2:f9
On Thu, Jan 6, 2011 at 11:15 AM, hanumant shinde hanumant_5...@yahoo.co.in
wrote:
Hi Ashish,
can you pls explain this.
{=OFFSET(E1,MAX(IF(F2:F9=MAX(F2:F9),ROW(F2:F9),))-1,0)}
Also,why have u used {}. when to use them. if do F2 i get Value error. why is
it
so.
so many que :( can you please explain.
From: ashish koul koul.ash...@gmail.com
To: excel-macros@googlegroups.com
Sent: Thu, 6 January, 2011 10:37:35 AM
Subject: Re: $$Excel-Macros$$ How do I find the highest of the most repeating
values in a list of numbers ie. the max of the modes
check the attachment see if it helps
On Thu, Jan 6, 2011 at 6:17 AM, Kim anast...@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
Kim
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
--
Regards
Ashish Koul
akoul.blogspot.com
akoul.wordpress.com
My Linkedin Profile
PBefore printing, think about the environment.
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
--
Regards
Ashish Koul
akoul.blogspot.com
akoul.wordpress.com
My Linkedin Profile
PBefore printing, think about the environment.
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at
$$Excel-Macros$$ 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 Kim -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
Re: $$Excel-Macros$$ How do I find the highest of the most repeating values in a list of numbers ie. the max of the modes
check the attachment see if it helps On Thu, Jan 6, 2011 at 6:17 AM, Kim anast...@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 Kim -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts -- *Regards* * * *Ashish Koul* *akoul*.*blogspot*.com http://akoul.blogspot.com/ *akoul*.wordpress.com http://akoul.wordpress.com/ My Linkedin Profile http://in.linkedin.com/pub/ashish-koul/10/400/830 P Before printing, think about the environment. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts sample datat.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ How do I find the highest of the most repeating values in a list of numbers ie. the max of the modes
Hi Ashish,
can you pls explain this.
{=OFFSET(E1,MAX(IF(F2:F9=MAX(F2:F9),ROW(F2:F9),))-1,0)}
Also,why have u used {}. when to use them. if do F2 i get Value error. why is
it
so.
so many que :( can you please explain.
From: ashish koul koul.ash...@gmail.com
To: excel-macros@googlegroups.com
Sent: Thu, 6 January, 2011 10:37:35 AM
Subject: Re: $$Excel-Macros$$ How do I find the highest of the most repeating
values in a list of numbers ie. the max of the modes
check the attachment see if it helps
On Thu, Jan 6, 2011 at 6:17 AM, Kim anast...@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
Kim
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
--
Regards
Ashish Koul
akoul.blogspot.com
akoul.wordpress.com
My Linkedin Profile
PBefore printing, think about the environment.
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
Re: $$Excel-Macros$$ How do I find the highest of the most repeating values in a list of numbers ie. the max of the modes
Thanks so much Ashish but the problem is in the temporary column. I really need to do it in one cell only without introducing any new data. Any idea on how I can achieve that? Again, thanks for your help. Kim On Jan 6, 4:07 pm, ashish koul koul.ash...@gmail.com wrote: check the attachment see if it helps On Thu, Jan 6, 2011 at 6:17 AM, Kim anast...@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 Kim -- ------ Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials athttp://www.excel-macros.blogspot.com 4. Learn VBA Macros athttp://www.quickvba.blogspot.com 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall;... -- *Regards* * * *Ashish Koul* *akoul*.*blogspot*.com http://akoul.blogspot.com/ *akoul*.wordpress.com http://akoul.wordpress.com/ My Linkedin Profile http://in.linkedin.com/pub/ashish-koul/10/400/830 P Before printing, think about the environment. sample datat.xls 24KViewDownload- Hide quoted text - - Show quoted text - -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
