+ Reply to Thread
Results 1 to 9 of 9

return the number of cells with the most consecutive specific values.

  1. #1
    Registered User
    Join Date
    08-07-2014
    Location
    South Africa
    MS-Off Ver
    2010
    Posts
    24

    return the number of cells with the most consecutive specific values.

    I have a range of say B1:Z2.
    In row B1:Z1 I have dates in the format dd-mmm-yy.
    In row B2:Z2 I indicate presence of a person by "P". This is at infrequent appearance i.e. p,p,p,blank,blank,blank,p,p,p,p,p,blank,blank,p,p,blank,p,p,p, etc.
    I am trying to find or workout a formula that would find the most repetitive Ps in row B2:Z2 and give me the sum thereof. I.e, from the above example it should be 5.
    It does not have to count specifically P. Something that counts the most non-blank sequence of cells in the range should also do.
    Would somebody who understands excel better than I please be so kind to assist or give me guidance.
    Thank you kindly.

  2. #2
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,939

    Re: return the number of cells with the most consecutive specific values.

    =MAX(FREQUENCY((B2:Z2="p")*COLUMN(B2:Z2),(B2:Z2<>"p")*COLUMN(B2:Z2)))
    Try this formula
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  3. #3
    Forum Expert boopathiraja's Avatar
    Join Date
    07-12-2013
    Location
    Coimbatore,TamilNadu, India
    MS-Off Ver
    Excel 2007, 2013, 2016, 365
    Posts
    1,455

    Re: return the number of cells with the most consecutive specific values.

    Hi,

    May be with helper rows, check out the attachment
    Attached Files Attached Files
    Click just below left if it helps, Boo?ath?

  4. #4
    Registered User
    Join Date
    08-07-2014
    Location
    South Africa
    MS-Off Ver
    2010
    Posts
    24

    Re: return the number of cells with the most consecutive specific values.

    nflsales
    Thanks for the response.
    I tested it by copying the formula into the sample sheet attached, but it did not return what I was looking for.
    You will see it returned a value of 13.
    I am looking for a solution to find the longest sequence of Ps i.e.it should find that the longest sequence in in range G2 to L2 and return 6 (the number of Ps in this sequence).
    Thank you kindly
    Attached Files Attached Files

  5. #5
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: return the number of cells with the most consecutive specific values.

    You should really be using an array formula** version of nflsales' formula:

    =MAX(FREQUENCY(IF(B2:Z2="p",COLUMN(B2:Z2)),IF(B2:Z2<>"p",COLUMN(B2:Z2))))

    @nflsales The problem with that non-array version is that the Boolean FALSEs from the first test are included in the FREQUENCY (i.e. as zeroes). Sometimes you'll get away with it (if the largest string of "p"s is larger than the number of cells which don't contain a "p"), but in general it's not very rigorous.

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  6. #6
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,939

    Re: return the number of cells with the most consecutive specific values.

    {=MAX(FREQUENCY(IF(B2:Z2="P",COLUMN(B2:Z2)),IF(B2:Z2<>"P",COLUMN(B2:Z2))))}
    Sorry, try this array formula

  7. #7
    Registered User
    Join Date
    08-07-2014
    Location
    South Africa
    MS-Off Ver
    2010
    Posts
    24

    Re: return the number of cells with the most consecutive specific values.

    Shucks, you guys just make me feel more inadequate with every response.
    I tried that array formula in my sample sheet and it returns #VALUE!

  8. #8
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,939

    Re: return the number of cells with the most consecutive specific values.

    See the attached file
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    08-07-2014
    Location
    South Africa
    MS-Off Ver
    2010
    Posts
    24

    Re: return the number of cells with the most consecutive specific values.

    Guys, its solved. I left out the {}.
    Much appreciated

+ 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: 17
    Last Post: 06-27-2014, 04:25 PM
  2. Replies: 3
    Last Post: 01-27-2014, 07:17 AM
  3. Return specific values from one workbook to another, in specific cells
    By Bobbie12 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-20-2013, 03:33 PM
  4. [SOLVED] Macro to generate a random number between 2 specific values into specific cells.
    By Nerfmagnet in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-10-2013, 11:45 AM
  5. Return Consecutive Values
    By Sam via OfficeKB.com in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 09-06-2005, 02:05 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