+ Reply to Thread
Results 1 to 4 of 4

How many times each number shows up?

  1. #1
    Registered User
    Join Date
    02-23-2008
    Posts
    2

    How many times each number shows up?

    I am a new Excel user so bear with me while I learn.

    I have Excel 2007...

    I have a long list of numbers in a column (A1 - A520)
    The number are all between 1 and 100
    Is it possible to see how many time each number is on the list?
    ie. 5 is on the list 22 times

    Ideally I need to find the top 7 number patterns
    ie. 5 - 22
    10 - 20
    12 - 19
    and so on


    Edit to add: I add between 3 and 22 numbers twice a week.

  2. #2
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229
    These formulas might help

    =COUNTA(A1:A450,1)
    returns the number of 1's in A1:A450

    =LARGE(FREQUENCY(A1:A450,A1:A450),1)
    returns the number of times the most frequent entry occurs
    =SUMPRODUCT(--(COUNTIF(A1:A450,A1:A450)=LARGE(FREQUENCY(A1:A450,A1:A450),1)),A1:A450)/LARGE(FREQUENCY(A1:A450,A1:A450),1)
    returns the most frequent entry


    =LARGE(FREQUENCY(A1:A450,A1:A450),2)
    returns the number of times the second most frequent entry occurs
    =SUMPRODUCT(--(COUNTIF(A1:A450,A1:A450)=LARGE(FREQUENCY(A1:A450,A1:A450),2)),A1:A450)/LARGE(FREQUENCY(A1:A450,A1:A450),2)
    returns the second most frequent entry
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  3. #3
    Registered User
    Join Date
    02-23-2008
    Posts
    2
    Thank you.

    I would assume then for the other numbers....

    =LARGE(FREQUENCY(A1:A450,A1:A450),3)
    returns the number of times the third most frequent entry occurs
    =SUMPRODUCT(--(COUNTIF(A1:A450,A1:A450)=LARGE(FREQUENCY(A1:A450, A1:A450),3)),A1:A450)/LARGE(FREQUENCY(A1:A450,A1:A450),3)
    returns the third frequent entry


    =LARGE(FREQUENCY(A1:A450,A1:A450),4)
    returns the number of times the forth most frequent entry occurs
    =SUMPRODUCT(--(COUNTIF(A1:A450,A1:A450)=LARGE(FREQUENCY(A1:A450, A1:A450),4)),A1:A450)/LARGE(FREQUENCY(A1:A450,A1:A450),4)
    returns the forth most frequent entry


    =LARGE(FREQUENCY(A1:A450,A1:A450),5)
    returns the number of times the fifth most frequent entry occurs
    =SUMPRODUCT(--(COUNTIF(A1:A450,A1:A450)=LARGE(FREQUENCY(A1:A450, A1:A450),5)),A1:A450)/LARGE(FREQUENCY(A1:A450,A1:A450),5)
    returns the fifth most frequent entry



    What if multiple numbers come up for any given category? Which will it display?
    ie. 5 - 22 time
    7 - 22 times

  4. #4
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229
    That will be problematic.

    With the data set 10, 10, 10, 20, 20, 20, 33, 33, 44, 55

    The first formula will correctly number the occurances of the most frequent entrys, in this case

    3, 3, 2, 1, 1

    The second formula will return the sum of any tie values.

    30, 30 , 33 , 99, 99

    This is a problem.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1