# Counting consecutive values less than a .9

1. ## 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. ## 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.

3. ## Re: Counting consecutive values less than a .9

Thanks for getting back to me Richard.

Capture.PNG

4. ## 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.

5. ## 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. ## Re: Counting consecutive values less than a .9

Thank you guys this has help me out a lot.

7. ## Re: Counting consecutive values less than a .9

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

8. ## 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.

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### 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