Excel consecutive formula

mahmed1

Well-known Member
Joined
Mar 28, 2009
Messages
2,245
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
Joined
Apr 18, 2011
Messages
37,197
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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
Joined
Mar 28, 2009
Messages
2,245
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
Joined
Mar 28, 2009
Messages
2,245
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi Rick i tried that but unfortunately it was not highlighting the right cells

in the example below - it should be highlighted like this

1602195365824.png
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,197
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Mar 28, 2009
Messages
2,245
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
Joined
Apr 18, 2011
Messages
37,197
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Mar 28, 2009
Messages
2,245
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
Joined
Apr 18, 2011
Messages
37,197
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Mar 28, 2009
Messages
2,245
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I want the users to have the flexibility to change and highlight number of consecutive values based on that variable
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top