+ Reply to Thread
Results 1 to 18 of 18

Count the number of times an A appears 5 or more times consecutively

  1. #1
    Registered User
    Join Date
    09-30-2013
    Location
    Boston
    MS-Off Ver
    Excel 2007
    Posts
    7

    Count the number of times an A appears 5 or more times consecutively

    I am trying to count the number of times an A appears in a column 5 or more times in a row, the question I have is there are cells which have an "X" in them and I want it to ignore the X value when considering if an A appears consecutively. This is an attendance sheet so the "X" values are for non work days i.e. Sat/Sun. So if someone is out on Friday and then out Mon-Thursday it should count the "A"'s as 5 consecutive values.

    16 17 18 19 20 21 22 23 24 25 26
    P P P P A X X A A A A

    result for count of cnsecutive "A" should be 5.

    Here is the formula I have, but it does not exclude the X's

    {=SUM(IF(FREQUENCY(IF($F6:$LE6="A",COLUMN($F6:$LE6)),IF($F6:$LE6<>"A",COLUMN($F6:$LE6)))>=5,FREQUENCY(IF($F6:$LE6="A",COLUMN($F6:$LE6)),IF($F6:$LE6<>"A",COLUMN($F6:$LE6))),0))}
    Attached Files Attached Files
    Last edited by CCook310; 09-30-2013 at 05:28 PM. Reason: Moved from Intro sub forum

  2. #2
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Count the number of times an A appears 5 or more times consecutively

    I didn't look at your file.

    Try something like this:

    =SUM(IF(FREQUENCY(IF(F6:LE6="A",COLUMN(F6:LE6)),IF(F6:LE6<>"X",IF(F6:LE6<>"A",COLUMN(F6:LE6))))>=5,1))

    Still array entered.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Registered User
    Join Date
    09-30-2013
    Location
    Boston
    MS-Off Ver
    Excel 2007
    Posts
    7

    Red face Re: Count the number of times an A appears 5 or more times consecutively

    Biff,
    Thank you for the reply. The problem I am now having is that it does not count any additional "A"'s it only counts the one instance of 5 or more...when I enter 2 more groups of 5 absences it still only counts 1?

    10-1-2013 9-11-31 AM.png

  4. #4
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Count the number of times an A appears 5 or more times consecutively

    I'm not sure I understand what you want to count.

    You said:

    I want it to ignore the X value when considering if an A appears consecutively.
    So, in your screencap if you ignore the X's then there is just a single streak of 5 or more consecutive A's.

    AAXXAAAAAXXAAAAAXXAAAAA

    Ignoring the X's, that would be counted as 17 consecutive A's.

  5. #5
    Registered User
    Join Date
    09-30-2013
    Location
    Boston
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Count the number of times an A appears 5 or more times consecutively

    it would be 17 consecutive, but if I put them in groups of 5 then I would acutally hve 15 days. I am not concerned about the other 2 days on the thursday and friday , I want to count them in groups of 5. the idea is that when someone is absent for 5 consecutive days a notice needs to be sent, then when they have 10 (an occurance of 5 more consecutive at any point) another notice is sent, and so on...the ignoring X's is to count a friday and then a monday-thursday absence that should be 5 consecutive school days out.

  6. #6
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Count the number of times an A appears 5 or more times consecutively

    Sorry, I don't understand.

  7. #7
    Registered User
    Join Date
    09-30-2013
    Location
    Boston
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Count the number of times an A appears 5 or more times consecutively

    In the example attached the student has 18 days absent, but I only care about an occurance of 5 consecutive days so while I am counting overall days, I am not concerned with the 2 absences on the 2nd and 3rd, but I am looking at the ones where a student i out on a friday and then m-th or wed-friday and then again on mon-tues. I hope this helps
    10-2-2013 9-33-04 AM.png

  8. #8
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Count the number of times an A appears 5 or more times consecutively

    Sorry, still not registering.

    Maybe a fresh set of eyes will see it.

  9. #9
    Registered User
    Join Date
    09-30-2013
    Location
    Boston
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Count the number of times an A appears 5 or more times consecutively

    No worries. thank you for your help

  10. #10
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Count the number of times an A appears 5 or more times consecutively

    I posted a link to this thread in the "Call in the Cavalry" subforum.

    Maybe someone will see what I'm not seeing.

    Good luck!

  11. #11
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Count the number of times an A appears 5 or more times consecutively

    Can't you just use the formula you have in post one and use a substitute function to take out the X's where neccessary?

    e.g.
    {=SUM(IF(FREQUENCY(IF(SUBSTITUTE($F6:$LE6,"X","")="A", and so on

    Edit: just looked at it again. No you can't...

  12. #12
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Count the number of times an A appears 5 or more times consecutively

    Had another go and essentially based on Tony's solution:
    =SUM(IF(FREQUENCY(IF(F6:LE6="A",COLUMN(F6:LE6)),IF(F6:LE6<>"X",IF(F6:LE6<>"A",COLUMN(F6:LE6))))>=5,ROUNDDOWN(FREQUENCY(IF(F6:LE6="A",COLUMN(F6:LE6)),IF(F6:LE6<>"X",IF(F6:LE6<>"A",COLUMN(F6:LE6))))/5,0)))

  13. #13
    Registered User
    Join Date
    09-30-2013
    Location
    Boston
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Count the number of times an A appears 5 or more times consecutively

    This worked perfect!! Thank you so much

  14. #14
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,164

    Re: Count the number of times an A appears 5 or more times consecutively

    Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

    In future, to mark your thread as Solved, you can do the following -
    Select Thread Tools-> Mark thread as Solved.

    Incase your issue is not solved, you can undo it as follows -
    Select Thread Tools-> Mark thread as Unsolved.

    Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  15. #15
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Count the number of times an A appears 5 or more times consecutively

    You could avoid repeating the FREQUENCY function by using this version

    =SUM(INT(FREQUENCY(IF(F6:LE6="A",COLUMN(F6:LE6)),IF(F6:LE6<>"X",IF(F6:LE6<>"A",COLUMN(F6:LE6))))/5))

    confirmed with CTRL+SHIFT+ENTER
    Audere est facere

  16. #16
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Count the number of times an A appears 5 or more times consecutively

    Looks like you solved it but can you explain to me the logic starting from the ROUNDDOWN function?

    I understand the "mechanics" of the formula but I don't understand what the OP wanted to do. If I understand the formula logic then I'll be able to understand what the OP wanted.

  17. #17
    Registered User
    Join Date
    09-30-2013
    Location
    Boston
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Count the number of times an A appears 5 or more times consecutively

    I wanted to be able to count the number of time a student is absent 5 consecutive days, The A indicated an absence and the X's were non school days. I placed conditional formatting to tell me when a student had an occurance of 5 consecutive days, then if they had another consecutive 5 day regardless of when in the year it would cause the flag to change to red because each occurance required a different notice. Please let me know if this helped explain what my original need was, I would be more than happy to elaborate more.

  18. #18
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Count the number of times an A appears 5 or more times consecutively

    OK, if we consider this sample:

    AAXXAAAAAXXAAAAAXXAAAAA

    Then the result you expect is 3?

  19. #19
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Count the number of times an A appears 5 or more times consecutively

    @Tony,

    Aye, that's my understanding. Thinking about it I could probably just do:
    =SUM(ROUNDDOWN(FREQUENCY(IF(F6:LE6="A",COLUMN(F6:LE6)),IF(F6:LE6<>"X",IF(F6:LE6<>"A",COLUMN(F6:LE6))))/5,0))

    -I used your formula which will give the numbers of consecutive A's as an array, dividing these by 5 and rounding down would then give the number of times A appears 5 times in a row in each group of consecutive A's. Then the sum of these is the OP's target.

+ 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. Count number of times 3,appears
    By ORIELSON in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-06-2013, 08:58 AM
  2. Count number a times a specific value appears consecutively.
    By kww0027 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-03-2013, 06:17 PM
  3. Count number of times n/s appears
    By hugrl in forum Excel General
    Replies: 2
    Last Post: 11-21-2009, 08:57 AM
  4. Count Number of Times Something appears
    By Mark B in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-29-2005, 04:40 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