+ Reply to Thread
Results 1 to 6 of 6

Days in the Green formula

  1. #1
    Registered User
    Join Date
    01-15-2015
    Location
    California
    MS-Off Ver
    2010
    Posts
    96

    Days in the Green formula

    Our company sends report twice daily if turn around time is in the Red or the Green. The attached sheet shows the status at the AM time, and the PM Time. I need a formula to count how many days in a row the department was in Green status, but only using the PM date line.
    Last edited by Alphabex; 07-25-2017 at 06:43 PM.

  2. #2
    Forum Contributor
    Join Date
    07-28-2012
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    104

    Re: Days in the Green formula

    In C17, enter the formula:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Copy that formula to the right for the next three cells.

    This will tell you the total number of days, not necessarily consecutive. For consecutive without a red, you would find the last row that had a Red, then count how many distinct days after it had a green. I can figure out that formula if you need it.
    Last edited by SlipEternal; 07-24-2017 at 04:57 PM.

  3. #3
    Registered User
    Join Date
    01-15-2015
    Location
    California
    MS-Off Ver
    2010
    Posts
    96

    Re: Days in the Green formula

    I appreciate the quick response, however, I need it to return the number of Green days consecutively. this gives me a total count. Once it hits a red, it goes back to zero.

    Quote Originally Posted by SlipEternal View Post
    In C17, enter the formula:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Copy that formula to the right for the next three cells.

  4. #4
    Forum Contributor
    Join Date
    07-28-2012
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    104

    Re: Days in the Green formula

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    What this does is, if there are no Red entries, it means that every entry is a consecutive Green, so it just counts the Green entries associated with PM. If there are red entries, C4:C15="Red" creates an array of values that are either TRUE or FALSE. It is TRUE if the value is "Red" and FALSE otherwise. By dividing 1 by this array, it creates a new array (every entry is either 1 or #DIV/0! where it is 1 if it was TRUE and the error otherwise). The LOOKUP function is looking for 2, but it will take the last entry that is closest to 2 (so it takes the last 1). This corresponds to an array of numbers (we have 12 entries in C4:C15, so we need an array of numbers 1:12 corresponding to the number of rows to skip). The INDEX function then takes the array C4:C15 and finds the last entry containing "Red" and starts the array. This goes to C15. Then, it counts the number of times Green appears in that modified array with "PM" in column B. This tells you the number of consecutive Greens.

  5. #5
    Forum Contributor
    Join Date
    07-28-2012
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    104

    Re: Days in the Green formula

    I just thought of something. What happens if someone has Green for one day, the following AM, it is Red, and the following PM it is back to Green? Would this be one consecutive green or two? The formula I just posted (Post #4 above) would treat it as one consecutive day. If you want it to show two, that would be a more complicated formula. I did it with an array formula. You can check it out in the attached spreadsheet. I was hoping to find a way to do with without an array formula, so maybe someone else can help.
    Last edited by SlipEternal; 07-25-2017 at 03:21 PM.

  6. #6
    Registered User
    Join Date
    01-15-2015
    Location
    California
    MS-Off Ver
    2010
    Posts
    96

    Re: Days in the Green formula

    This works great. and actually I have a formula already built into each PM line that if the preceding AM line is Red, then it defaults to Red. Much appreciated!!!!!
    Quote Originally Posted by SlipEternal View Post
    I just thought of something. What happens if someone has Green for one day, the following AM, it is Red, and the following PM it is back to Green? Would this be one consecutive green or two? The formula I just posted (Post #4 above) would treat it as one consecutive day. If you want it to show two, that would be a more complicated formula. I did it with an array formula. You can check it out in the attached spreadsheet. I was hoping to find a way to do with without an array formula, so maybe someone else can help.

+ 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. copy "green fill with dark green text"
    By stephme55 in forum Excel General
    Replies: 1
    Last Post: 02-04-2017, 02:04 AM
  2. Green up formula
    By nickm84 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-17-2016, 12:44 AM
  3. Replies: 1
    Last Post: 01-06-2014, 04:31 AM
  4. Replies: 1
    Last Post: 07-16-2013, 01:16 PM
  5. Replies: 6
    Last Post: 08-23-2012, 12:14 PM
  6. inconsistent formula green flags
    By dshaw11220 in forum Excel General
    Replies: 2
    Last Post: 05-03-2010, 02:25 AM
  7. Excel: want neg numbers to show red/pos green via if-then formula
    By Jim in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-15-2006, 06:30 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