Excel consecutive formula

[mahmed1]

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

 

[Rick Rothstein]

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)...

=OR(COUNTIF(D3:F3,D3)=3,COUNTIF(C3:E3,D3)=3,COUNTIF(B3:D3,D3)=3)

 

[mahmed1]

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]

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]

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]

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]

If I am not mistaken, I believe my formula applied to the range F34:T34 should be this instead of what you posted...

=OR(COUNTIF(F34:H34,F34)=$C$33,COUNTIF(E34:G34,F34)=$C$33,COUNTIF(D34:F34,F34)=$C$33)

 

[mahmed1]

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]

Follow up...
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]

I want the users to have the flexibility to change and highlight number of consecutive values based on that variable