+ Reply to Thread
Results 1 to 5 of 5

Find the cell reference of consecutive largest negative/positive numbers

  1. #1
    Registered User
    Join Date
    06-09-2021
    Location
    pakistan
    MS-Off Ver
    2016
    Posts
    3

    Find the cell reference of consecutive largest negative/positive numbers

    Dear Members

    I am trying a make formulas in the sheet attached following is my sheet's data
    I have different positive and negative numbers in column B of the the sheet
    In column C values are cumulative from column B

    Now What i want to do is

    1. Maximum number of consecutive +ve Numbers (Streak) in Column B
    2. Sum of Max Consecutive +ve numbers
    3. Cell reference of starting maximum Positive consecutive numbers (Streak) in column B whose numbers are calculated in column "F"
    4. Maximum number of consecutive -ve Numbers (Streak) in Column B
    5. Sum of Max Consecutive -ve numbers
    6. Cell reference of starting maximum Positive consecutive numbers (Streak) in column B whose numbers are calculated in column "I"

    any body who have solution to this please help me
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,519

    Re: Find the cell reference of consecutive largest negative/positive numbers

    Welcome to the forum.

    This reads rather more like a brief than a request for help, so to reassure us that you are not just expecting us to give you a solution to all 6 issues, tell us or show us what you have tried in each case, and explain why it isn't quite working for you.

    The aim here is to coach you. It's not a case of you giving us a list of things you want doing and expecting us to sort it out for you. The nature of the forum is meant to be one of collaboration.
    Last edited by AliGW; 06-09-2021 at 03:14 AM.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  3. #3
    Registered User
    Join Date
    06-09-2021
    Location
    pakistan
    MS-Off Ver
    2016
    Posts
    3

    Re: Find the cell reference of consecutive largest negative/positive numbers

    Thank you AliGW for response

    i have tried

    =MAX(FREQUENCY(IF(B2:B2001>0,ROW(B2:B2001)),IF(B2:B2001<=0,ROW(B2:B2001)))) Ctrl+Shift+enter for 1st & 4th Case
    =SUM(OFFSET(A1,-1+MATCH(TRUE,COUNTIF(OFFSET(A1,ROW(INDIRECT("1:"&ROWS(A1:A11)-B2+1))-1,0,B2,1),"<0")=B2,0),0,B2,1)) Ctrl+Shift+enter for 2nd & 4th Case
    and both worked but i have no idea of getting the cell reference of or sr numbers of these values as i asked in sr 3 & 6 in post

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,823

    Re: Find the cell reference of consecutive largest negative/positive numbers

    In F2

    =MAX(FREQUENCY(IF($B$2:$B$2001>0,ROW($B$2:$B$2001)),IF($B$2:$B$2001<=0,ROW($B$2:$B$2001))))

    with Ctrl+Shift+Enter

    in G2

    =SUM(OFFSET(B2,MATCH(MAX(I2:I2001),I2:I2001,0)-$F$2,0,MAX(I2:I2001)))

    in H2

    ="B" &MATCH($F$2,$I$1:$I$2001,0)-$F$2+1

    in I2 and copy down ("Helper" column)

    =IF(B2>0,I1+1,0)

    Similar for negative values
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    06-09-2021
    Location
    pakistan
    MS-Off Ver
    2016
    Posts
    3

    Re: Find the cell reference of consecutive largest negative/positive numbers

    Thanks John Topley, this was what i was looking for.
    Have a great day Thanks

+ 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. Replies: 14
    Last Post: 08-20-2018, 03:44 PM
  2. [SOLVED] Find consecutive 15 positive or negative numbers in a column
    By Sarangsood in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-19-2013, 05:26 AM
  3. [SOLVED] Count Consecutive Positive/Negative numbers
    By Taislin in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-16-2013, 01:02 PM
  4. Find Largest consecutive occurrence of negative values
    By Rubber in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-28-2013, 11:36 AM
  5. Sum of time for consecutive positive/ negative numbers
    By DexterG in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-25-2012, 04:03 AM
  6. Replies: 4
    Last Post: 06-14-2012, 06:53 AM
  7. Replies: 1
    Last Post: 11-20-2008, 01:52 AM

Tags for this Thread

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