+ Reply to Thread
Results 1 to 4 of 4

Frequency of consecutive negative numbers categorised by frequency

  1. #1
    Registered User
    Join Date
    01-10-2014
    Location
    Stuttgart, Germany
    MS-Off Ver
    Excel 2013
    Posts
    8

    Frequency of consecutive negative numbers categorised by frequency

    Hi,
    I am trying to count the number of consecutive negative numbers in a list. I want to know the frequency of those negative numbers only apearing once, the same for those negatives appearing consecutively 2 times, for those 3 times....and so on.
    eg.
    -5624
    -7455
    -7846
    -6742
    -6868
    8336
    7238
    -4544
    -395
    2425
    5629
    -6894
    -8553
    9720
    12380
    -18341
    24394
    -25962
    24282
    21322
    -18343
    18493
    15352
    For this I would get:
    5 negatives consecutively = 1 time
    2 negatives consecutively = 2 times
    1 negative = 3 times
    Thank you for your help.

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Frequency of consecutive negative numbers categorised by frequency

    Row\Col
    A
    B
    C
    D
    E
    F
    1
    Value
    Run Length
    Qty
    2
    -5624
    0
    5
    D2: {=FREQUENCY(FREQUENCY(IF(A2:A23<0, ROW(A2:A23)), IF(A2:A23>=0, ROW(A2:A23))), C2:C7)}
    3
    -7455
    1
    3
    4
    -7846
    2
    2
    5
    -6742
    3
    0
    6
    -6868
    4
    0
    7
    8336
    5
    1
    8
    7238
    0
    9
    -4544
    10
    -395
    11
    2425
    12
    5629
    13
    -6894
    14
    -8553
    15
    9720
    16
    12380
    17
    -18341
    18
    24394
    19
    -25962
    20
    24282
    21
    21322
    22
    -18343
    23
    18493


    The formula says there are three runs of 1, 2 runs of 2, and 1 run of 5.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    01-10-2014
    Location
    Stuttgart, Germany
    MS-Off Ver
    Excel 2013
    Posts
    8

    Re: Frequency of consecutive negative numbers categorised by frequency

    Thanks a lot!

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Frequency of consecutive negative numbers categorised by frequency

    You're welcome.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. frequency of a set of numbers
    By norms in forum Excel General
    Replies: 4
    Last Post: 11-14-2014, 04:01 AM
  2. [SOLVED] Finding frequency of multiple negative period
    By hotcomhotcom in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-08-2014, 05:13 AM
  3. Replies: 3
    Last Post: 02-11-2014, 05:36 PM
  4. [SOLVED] Consecutive Employee Vacation Days using sumif array with frequency and column functions
    By ciaran01 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-12-2013, 10:24 AM
  5. Frequency of Negative Numbers
    By carhw in forum Excel General
    Replies: 2
    Last Post: 04-04-2012, 02:49 PM
  6. histograms - frequency and relative frequency?
    By confusedstudent in forum Excel General
    Replies: 2
    Last Post: 02-08-2006, 04:25 AM
  7. Frequency of Numbers
    By shane24 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-09-2005, 04:18 PM

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