+ Reply to Thread
Results 1 to 2 of 2

Formula to calculate consective weeks w/same score, reset counter when streak is broken

  1. #1
    Registered User
    Join Date
    08-12-2014
    Location
    Pennsylvania
    MS-Off Ver
    2010
    Posts
    1

    Formula to calculate consective weeks w/same score, reset counter when streak is broken

    I have a table with the date at the top and employee names down the side. For each date range, an employee is assigned a number 1-3 in Column A (for the 1st week; Column I for the 2nd week, etc. as there is other information that will either be calculated with a formula or entered manually in the 7 coloumns between each date range). Column A, I, etc, we will call "score". Column B, J, etc, which we will title "# of Consecutive Weeks". I'm looking for a formula that will compare that week's score to the previous week's score and return the count of how many weeks the employee was at that score consecutively.

    For example, if Employee A has a score of 1 for the first week and a score of 1 for the second week, I need the "# of Consecutive Weeks" for the 2nd week to show 2. If the 3rd week, the employee has a score of 2, "# of Consecutive Weeks" would show 1 but if the employee scored a 1, the "# of Consecutive Weeks" would show 3.

    Each week the employee has two scores based on seperate criteria so each employee has two lines. I also have a spreadsheet that pulls just those scores over with the employee names as headers and the dates down the side. I hoped that putting each set of scores in columns for each employee would provide more solutions.

    I've looked all over forums and search engines looking for something that would work but haven't had luck. Maybe it's simple and I'm making it too complicated? I'm looking to do the calculation without the use of VBA as once the workbook is created, it will be sent for someone else's use that only has basic excel knowledge and multiple people will be using the workbook.

  2. #2
    Valued Forum Contributor
    Join Date
    04-22-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    Office 365 (work) and Excel 2013 (home)
    Posts
    1,167

    Re: Formula to calculate consective weeks w/same score, reset counter when streak is broke

    In J try this: =IF(I2=A2,B2+1,1)
    Please add reputation by clicking on the * if I have helped.
    Please mark the thread SOLVED if your issue has been resolved.
    Thanks, Glenn.

+ 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: 7
    Last Post: 08-03-2013, 09:51 AM
  2. Broken in, In Just Two Weeks
    By Doc Snowfox in forum Hello..Introduce yourself
    Replies: 2
    Last Post: 07-11-2013, 03:31 PM
  3. Counter reset
    By BN-CD in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-13-2007, 04:56 PM
  4. Counter Reset??
    By loh69 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-28-2006, 07:39 AM
  5. [SOLVED] Reset Counter
    By Ed in forum Excel General
    Replies: 3
    Last Post: 04-14-2006, 12:40 PM

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