+ Reply to Thread
Results 1 to 8 of 8

Counting consecutive values less than a .9

  1. #1
    Registered User
    Join Date
    06-16-2017
    Location
    USA
    MS-Off Ver
    2013
    Posts
    4

    Counting consecutive values less than a .9

    Hello,
    I am trying to count consecutive value(CCV) in a row and have it give a rating of green yellow and red. Green Rating 3 consecutive days or less below .9, Yellow Rating is 4 to 7 consecutive days below 0.9 and RED rating is anything 8 consecutive days below 0.9. Day 13 it turns back to green because they achieved .9.

    Example:
    CCV Rating
    1 Green Day 1
    1 Green Day 2
    .98 Green Day 3
    .9 Green Day 4
    .8 Green Day 5
    .7 Green Day 6
    .8 Green Day 7
    .8 Yellow Day 8
    .75 Yellow Day 9
    .85 Yellow Day 10
    .86 Yellow Day 11
    .75 RED Day 12
    .9 Green Day 13

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,384

    Re: Counting consecutive values less than a .9

    Hi and welcome to the forum,
    Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

    Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    06-16-2017
    Location
    USA
    MS-Off Ver
    2013
    Posts
    4

    Re: Counting consecutive values less than a .9

    Thanks for getting back to me Richard.

    Capture.PNG

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,261

    Re: Counting consecutive values less than a .9

    IMO, the easiest is to have a simple helper column to count consecutive occurrences.
    1) In E2 =IF(B2<0.9,1,0)
    2) In E3 =IF(B2<0.9,E2+1,0) copy down as far as needed.
    3) If you split the data in F2:F4 out into a lookup table, then C2 can be a simple lookup function that returns the desired color based on the value in E2. With proper attention to relative and absolute references, the formula in C2 could then be copied down as far as needed.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  5. #5
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,891

    Re: Counting consecutive values less than a .9

    Here's another option for you to consider.

    In C2

    =LOOKUP(IF(B2<0.9,ROWS(B$2:B2)-MAX(IF(B$2:B2>=0.9,ROW(B$2:B2)-1)),0),{0,4,8},{"Green","Yellow","Red"}) Ctrl Shift Enter

  6. #6
    Registered User
    Join Date
    06-16-2017
    Location
    USA
    MS-Off Ver
    2013
    Posts
    4

    Re: Counting consecutive values less than a .9

    Thank you guys this has help me out a lot.

  7. #7
    Registered User
    Join Date
    06-16-2017
    Location
    USA
    MS-Off Ver
    2013
    Posts
    4

    Re: Counting consecutive values less than a .9

    Thank you. This is a awesome. Exactly what i am looking for.

  8. #8
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,891

    Re: Counting consecutive values less than a .9

    Glad to help. Just FYI, you can use Conditional Formatting to color the cells instead of having the name of the color.

    To do this, highlight C2:C26 > Conditional Formatting > New Rule > Use a formula
    =IF(B2<0.9,ROWS(B$2:B2)-MAX(IF(B$2:B2>=0.9,ROW(B$2:B2)-1)),0)>=8
    Format: Fill red > OK > OK

    With C2:C26 still highlighted, Conditional Formatting > New Rule > Use a formula
    =IF(B2<0.9,ROWS(B$2:B2)-MAX(IF(B$2:B2>=0.9,ROW(B$2:B2)-1)),0)<=7
    Format: Fill yellow > OK > OK

    With C2:C26 still highlighted, Conditional Formatting > New Rule > Use a formula
    =IF(B2<0.9,ROWS(B$2:B2)-MAX(IF(B$2:B2>=0.9,ROW(B$2:B2)-1)),0)<=3
    Format: Fill green > OK > OK

    See attached workbook for the result.
    Attached Files Attached Files

+ 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. [SOLVED] (HELP) Counting Consecutive Values < 1 From Right
    By mus1ca in forum Excel General
    Replies: 6
    Last Post: 07-22-2014, 08:51 AM
  2. [SOLVED] Counting consecutive values less than X
    By stellards20 in forum Excel General
    Replies: 3
    Last Post: 06-18-2014, 12:08 AM
  3. Counting same values in consecutive columns
    By santhoosan in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-11-2013, 03:48 AM
  4. Counting Consecutive values and saving them
    By tdrouillard in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-11-2013, 10:23 PM
  5. [SOLVED] Counting Consecutive Values
    By khauskins in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-24-2012, 11:37 AM
  6. Counting consecutive values
    By Elijah in forum Excel General
    Replies: 7
    Last Post: 04-23-2010, 05:13 PM
  7. Counting consecutive non-zero values
    By RichH6109 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-26-2007, 07:21 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