+ Reply to Thread
Results 1 to 10 of 10

How to find the maximum occurrence of number in a range of cells

  1. 03-24-2015, 07:57 AM #1
    arunabha
    arunabha is offline
    Registered User
    Join Date
    01-05-2015
    Location
    Silchar
    MS-Off Ver
    Windows 7
    Posts
    21

    How to find the maximum occurrence of number in a range of cells

    Dear Sir,

    In a column I have 10 cells with values ranging from 0 to 4. Now, on the last cell I need to figure out which value from 0 to 4 has the highest number of occurrence in the entire range.

    Please advice .


    Thank you
    Reply With QuoteRegister To Reply
  2. 03-24-2015, 08:03 AM #2
    NeedForExcel
    NeedForExcel is offline
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,879

    Re: How to find the maximum occurrence of number in a range of cells

    Assuming your data is in range I1:I10

    Use this and commit using Ctrl+Shift+Enter

    Please Login or Register  to view this content.
    Cheers!
    Deep Dave
    Reply With QuoteRegister To Reply
  3. 03-24-2015, 08:55 AM #3
    XOR LX
    XOR LX is offline
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: How to find the maximum occurrence of number in a range of cells

    Hi.

    =MODE(I1:I10)

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/
    Reply With QuoteRegister To Reply
  4. 03-24-2015, 09:00 AM #4
    arunabha
    arunabha is offline
    Registered User
    Join Date
    01-05-2015
    Location
    Silchar
    MS-Off Ver
    Windows 7
    Posts
    21

    Re: How to find the maximum occurrence of number in a range of cells

    Dear Sir,

    Please find the excel sheet for your reference. In the cells B2:B8 the maximum occurrence is no.2.
    Hence,in cell B9, it's showing no.2. That means B9 is showing which no. has the maximum frequency in the range B2:B8.

    Please provide a solution to do the same.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 03-24-2015, 09:08 AM #5
    XOR LX
    XOR LX is offline
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: How to find the maximum occurrence of number in a range of cells

    So what was wrong with my solution?

    Regards
    Reply With QuoteRegister To Reply
  6. 03-24-2015, 09:12 AM #6
    arunabha
    arunabha is offline
    Registered User
    Join Date
    01-05-2015
    Location
    Silchar
    MS-Off Ver
    Windows 7
    Posts
    21

    Re: How to find the maximum occurrence of number in a range of cells

    Dear Sir,

    It is all good to go. The Mode() is working fine. it's due to the timing of my posting and yours.

    Thanks for the reply.
    Reply With QuoteRegister To Reply
  7. 03-24-2015, 09:15 AM #7
    XOR LX
    XOR LX is offline
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: How to find the maximum occurrence of number in a range of cells

    Ok, good. Glad to help.

    Regards
    Reply With QuoteRegister To Reply
  8. 03-24-2015, 11:39 PM #8
    NeedForExcel
    NeedForExcel is offline
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,879

    Re: How to find the maximum occurrence of number in a range of cells

    Haha! How could I just ignore the Mode.. So many Array formulas recently have made me miss out on the easiest solution it seems..
    Reply With QuoteRegister To Reply
  9. 03-25-2015, 03:44 AM #9
    XOR LX
    XOR LX is offline
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: How to find the maximum occurrence of number in a range of cells

    Quote Originally Posted by NeedForExcel View Post
    Haha! How could I just ignore the Mode.. So many Array formulas recently have made me miss out on the easiest solution it seems..
    I was quite surprised to see your original post, and had to double-check that I wasn't missing something!

    Still, it seems that you have managed to generate an algorithm for finding the mode, which isn't without interest. And you never know: Microsoft might one day decide to get rid of the MODE function.

    Cheers
    Reply With QuoteRegister To Reply
  10. 03-25-2015, 05:28 AM #10
    NeedForExcel
    NeedForExcel is offline
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,879

    Re: How to find the maximum occurrence of number in a range of cells

    Quote Originally Posted by XOR LX View Post
    I was quite surprised to see your original post, and had to double-check that I wasn't missing something!

    Still, it seems that you have managed to generate an algorithm for finding the mode, which isn't without interest. And you never know: Microsoft might one day decide to get rid of the MODE function.

    Cheers
    True! You never know what can happen with softwares...
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Find First Occurrence Of A Value In A Range Of Cells And Return The Leftmost Column Value
    By jaelex in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-23-2015, 07:17 AM
  2. [SOLVED] Find Duplicates and Add Number of Occurrence
    By Jonathan78 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-16-2013, 12:15 PM
  3. [SOLVED] How to find maximum value in a range of cells.
    By yeshwant_sur in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-08-2012, 12:37 PM
  4. Find the row number of the 2nd occurrence of a string in a range
    By seaottr in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-03-2011, 06:52 PM
  5. Finding the maximum occurrence of text over non-consecutive cells
    By raehippychick in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-15-2009, 09:04 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1