# Excel consecutive formula

#### mahmed1

##### Well-known Member
Hiya

I have a row of data in cells D3, E3, F3 etc

what i want to do is highlight the values in there to see if the consecutive values are greater than 3 and then highlight it

ie if its 1, 1, 3, 4, 5, 5 (highlighted), 3, 2, 5,5,5 (Highlighted), 5 (Highlighted), 5 (Highlighted), 2,4,4,4 (Highlighted)

so im just trying to highlight all values that exceeda number 3 in my case 3 times in a row , if it dropa back then the count should find the next occurence and highlight until it drops back again

hoping im making sense of it

Im typing from a phone hence why i could highlight the values so have put highligh next to the value

### Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

#### Rick Rothstein

##### MrExcel MVP
You say a formula, but given what you are asking for, I assume you want a formula to use in Conditional Formatting. With that in mind, select all the cells between cell D3 and the last data cell in Row 3 (make sure cell D3 is the active cell) and use this formula in the Conditional Formatting dialog box (don't forget to choose a fill color)...
Excel Formula:
``=OR(COUNTIF(D3:F3,D3)=3,COUNTIF(C3:E3,D3)=3,COUNTIF(B3:D3,D3)=3)``

#### mahmed1

##### Well-known Member
Thank you - yes that’s right

this conditionial formatting will need to be transferred to other rows as well following same logic

would I need to change the formula if I could reference 2 variables instead
1 to see if the value exceeds the variable value
And 2nd checks to see if it exceeds it more than the consecutive days which will be the 2nd variable ?

this will save me from having a helper Row

and also the data in these Rows will continue to expand

thank you again

#### mahmed1

##### Well-known Member
Hi Rick i tried that but unfortunately it was not highlighting the right cells

in the example below - it should be highlighted like this

#### Rick Rothstein

##### MrExcel MVP

The formula worked for me when I tested it before posting it. Tell us what cells were those numbers in Message #4 were in?

#### mahmed1

##### Well-known Member
Ok say in cells F33:T33 there are values (the conditional formatting will be added to multiple rows so could even be 3 but say row 33)

In F341:T34 - I have this formula copied across =IF(F33>\$C\$33,1,0) where C33 is the variable days i want to check against

I have tried to apply your formula to it but it just wouldnt highlight the right cells
I amended the formula to the following =
=OR(COUNTIF(F34:H34,F34)=\$C\$33,COUNTIF(G34:I34,F34)=\$C\$33,COUNTIF(I34:K34,F34)=\$C\$33)

This works but is not robust where i start the formula 2 rows back =SUMIF(D34:F34,F34)=\$D\$33but if i change the C33 variable to 4 days or 5 days it breaks
it only works with 3 days

#### Rick Rothstein

##### MrExcel MVP

If I am not mistaken, I believe my formula applied to the range F34:T34 should be this instead of what you posted...
Excel Formula:
``=OR(COUNTIF(F34:H34,F34)=\$C\$33,COUNTIF(E34:G34,F34)=\$C\$33,COUNTIF(D34:F34,F34)=\$C\$33)``

#### mahmed1

##### Well-known Member
Il give that formula a go - thank you with this formula work if i changed the c33 variable to check to say 4 or 5 days consecutive?

#### Rick Rothstein

##### MrExcel MVP
As for you comment about it only applying to 3 repeats and failing for 4 or 5 repeats, you are right... your first post only mentioned 3 repeats or more. I am using XL2010 and I am sure there is no general solution for cell C33 varying... I cannot speak for the newest versions of Excel which added extra array handling abilities and some new functions, they might possibly be able to so what you want directly. For my version of Excel, I believe a VBA event procedure may be the only way to do what you want. So I guess my first question is... what version of Excel are you using?

#### mahmed1

##### Well-known Member
I want the users to have the flexibility to change and highlight number of consecutive values based on that variable

Replies
21
Views
156
Replies
34
Views
884
Replies
3
Views
147
Replies
8
Views
171
Replies
3
Views
208

1,137,152
Messages
5,679,899
Members
419,861
Latest member
AceDaMace

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back