+ Reply to Thread
Results 1 to 9 of 9

Calculate which person has 2 or more X in a row

  1. #1
    Registered User
    Join Date
    03-14-2011
    Location
    Netherlands
    MS-Off Ver
    Excel 2013
    Posts
    9

    Calculate which person has 2 or more X in a row

    Hello

    Please see attached example excel file which i made because that will make things a lot more clear.

    I would like excel to calculate which person has for example 2 red crosses in a row, 3 red crosses in a row, 4 red crosses in a row.

    So by the column People with 2X's in a row there should be John, Mark, Nathalie
    In the column People with 3 X's in a row there should be John, Mark, Nathalie
    In the column People with 4 X's in a row there should be John, Mark

    I cannot get much clearer than this and i think the attached will make things more clear. I tried searching google but could not find what i was looking for.
    Attached Files Attached Files

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Calculate which person has 2 or more X in a row

    You could be more clear, 2 Xs in a row appears that Jane also qualifies.
    AND do you mean two (or three or four) in a row in that there are 3 x's in all of ROW 2 or ROW 4 or do you mean that there are 2 back to back, for instance in consecutive columns like column D followed by column E?
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    Re: Calculate which person has 2 or more X in a row

    copy the red cell/white cross to J2 down
    =COUNTIF($B2:$F2,J2) in K2 down
    =IF($K2=2,$A2,"") in M2 down (similar incremented formula in N2 and O2 down
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    03-14-2011
    Location
    Netherlands
    MS-Off Ver
    Excel 2013
    Posts
    9

    Re: Calculate which person has 2 or more X in a row

    Thank you so much all for your replies. However please know that the cells are now counted. But i would like to have them counted when they are consecutive. For example 3 in a row. So let say person a has first week a white X then second week green and then week 3 4 5 white X again it should be counted as 3 (3 consecutive white X)

  5. #5
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Calculate which person has 2 or more X in a row

    Please try at
    J2
    =MAX(FREQUENCY(IF($B2:$I2="X",COLUMN($B2:$I2)),IF($B2:$I2<>"X",COLUMN($B2:$I2))))

    M2
    =IFERROR(INDEX($A:$A,SMALL(IF($J$2:$J$5>COLUMNS($M2:M2),ROW($J$2:$J$5)),ROWS(M$2:M2))),"")

    **Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

    or M2
    =IFERROR(INDEX($A:$A,AGGREGATE(15,6,ROW($J$2:$J$5)/($J$2:$J$5>COLUMNS($M2:M2)),ROWS(M$2:M2))),"")
    normal enter
    Attached Files Attached Files
    Last edited by Bo_Ry; 05-01-2020 at 06:14 AM.

  6. #6
    Registered User
    Join Date
    03-14-2011
    Location
    Netherlands
    MS-Off Ver
    Excel 2013
    Posts
    9

    Re: Calculate which person has 2 or more X in a row

    Thank you so much. Sorry for not getting back earlier.

    I have one more thing and i hope it is not to much but is it possible to show the names who only have 4 in a row till date of today. So not counting the 4 in a row that happened in the past but only the ones who have 4 in a row until date of today.

  7. #7
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,423

    Re: Calculate which person has 2 or more X in a row

    In column K: =COUNTIFS(INDEX(B2:G2,MATCH(TODAY()-21,B$1:I$1)):INDEX(B2:G2,MATCH(TODAY(),B$1:I$1)),"x")
    In column Q: =IFERROR(INDEX(A$2:A$5,AGGREGATE(15,6,(ROW(A$2:A$5)-ROW(A$1))/(K$2:K$5>=4),ROWS(A$1:A1))),"")
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  8. #8
    Registered User
    Join Date
    03-14-2011
    Location
    Netherlands
    MS-Off Ver
    Excel 2013
    Posts
    9

    Re: Calculate which person has 2 or more X in a row

    Please know that you count is not correct when looking at Mark. We should only count consecutive cells which are red till the date of today. So in Mark his case this should be 0. Because after a green checkbox the count for consecutive cells should start over. Right now Mark has 4 red X and your formula counts 3.

    Thank you for your reply we are slowly getting there.

  9. #9
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,423

    Re: Calculate which person has 2 or more X in a row

    The values in column K were just there to help with the request "to show the names who only have 4 in a row till date of today" which are displayed in column Q. If you would like them to show a current count then modify the formula in cell K2 so that it reads:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.

+ 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. Calculate age a person is turning this year
    By nickpavlov in forum Excel General
    Replies: 4
    Last Post: 10-19-2017, 12:06 PM
  2. [SOLVED] Using Two Dates to Calculate Person's Age
    By Leathermyth in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-20-2014, 12:19 PM
  3. Calculate hours a person is scheduled to work.
    By Radical_Magic63 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-23-2012, 10:29 AM
  4. Calculate total adjustments for each sales person
    By Adama in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 09-03-2012, 07:46 AM
  5. [SOLVED] Calculate the age of a person on their next birthday date
    By sanjeevi888 in forum Excel General
    Replies: 2
    Last Post: 08-11-2012, 01:16 PM
  6. Calculate the age of a person on their next birthday
    By avidcat in forum Excel General
    Replies: 6
    Last Post: 06-09-2011, 04:44 AM
  7. [SOLVED] How do I calculate the age of a person?
    By Doug Mc in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 05-02-2005, 06:06 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