Attend Excelapalooza
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 5 123 ... LastLast
Results 1 to 10 of 41

Thread: Calculating streaks when some cells are blank

  1. #1
    New Member
    Join Date
    Aug 2017
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Calculating streaks when some cells are blank

    I am trying to calculate winning and losing streaks, but some of my cells do not contain information because a team might not play on that current day. How can I have a formula that will over look a blank cell, but still have it continue a streak?

    Example

    Cell A1. W
    A2. L
    A3. W
    A4.
    A5. W
    A6. W

    I should have a streak of 3 wins, but every time I try a formula it will only calculate it as 2 wins. How do I have a formula overlook the blank cell?

    And then I need it to calculate the longest streak as well.


    Any and all help would be greatly appreciated

    Thank you,

    Tim

  2. #2
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    14,577
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Calculating streaks when some cells are blank

    Welcome to Mr Excel forum

    To get the longest streak try this array formula
    =MAX(FREQUENCY(IF(A1:A6="W",ROW(A1:A6)),IF(A1:A6="L",ROW(A1:A6))))
    Ctrl+Shift+Enter

    Hope this helps

    M.
    Last edited by Marcelo Branco; Aug 10th, 2017 at 02:32 PM.

  3. #3
    New Member
    Join Date
    Aug 2017
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Calculating streaks when some cells are blank

    Thank you!

    Do you have an answer to how I can calculate a streak incorporating blank cells?

  4. #4
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    14,577
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Calculating streaks when some cells are blank

    Not sure i understand what you want. Could you provide an example?

    M.

  5. #5
    New Member
    Join Date
    Aug 2017
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Calculating streaks when some cells are blank

    If you look at column L you will see blanks because the team did not play during that time.

  6. #6
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    14,577
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Calculating streaks when some cells are blank

    I can see this, but i don't understand what you mean by "streak incorporating blank cells".
    Considering your data sample above what would be the desired/expected result?

    M.

  7. #7
    New Member
    Join Date
    Aug 2017
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Calculating streaks when some cells are blank

    I want it to be able to skip a blank cell and continue the streak. As of right now it counts blank cells as 0 and the streak ends when in all actuality no game was played that day.

  8. #8
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    45,230
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Calculating streaks when some cells are blank

    Marcelo's formula seems to work for me. Based on the example you posted in your first post, his formula returns 3, which appears correct to me.
    What does it return for you?
    If it returns something other than 3, are you sure that A4 is really blank?
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  9. #9
    New Member
    Join Date
    Aug 2017
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Calculating streaks when some cells are blank

    Technically A4 is not blank. W or L is determined by the score a a formula calculates if a W or an L is placed in that cell. Sorry I guess I should have included that before.

  10. #10
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    45,230
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Calculating streaks when some cells are blank

    Technically A4 is not blank. W or L is determined by the score a a formula calculates if a W or an L is placed in that cell. Sorry I guess I should have included that before.
    Please be sure to not leave out any important details!
    People often have tendency to oversimplify things, and then find out that the proposed solutions don't work for them (because we are missing some key information).

    So what exactly would be in the cell, other than W or L?
    What is the exact formula?
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •