Counting consecutive Zeros

RandyD123

Board Regular
Joined
Dec 4, 2013
Messages
195
I am trying to count consecutive Zeros on each row. This would have to exclude "RDO". My range starts at F3. The count would be on today's date in column "LP". All rows have data validation that only allows whole numbers 1-10 in quarter hour increments or RDO. If any cell in the row gets a number the count would have to start over. Once the count reaches 14 consecutive zero's the name would highlight in yellow. If the count gets broken after 14 by a number then the count starts over. I have a sheet posted here. I'm not sure if this would be a macro or a formula. Any help would be much appreciated. Thanks

Screenshot 2021-08-13 175824.png
 

RandyD123

Board Regular
Joined
Dec 4, 2013
Messages
195
I have corrected the formula.
Excel Formula:
=IF(OR(INDEX($F3:$LO3,LP$1)=0,INDEX($F3:$LO3,LP$1)="RDO"),MAX(IF(COUNTIF(OFFSET(INDEX($F3:$LO3,LP$1),0,0,1,0-ROW(INDIRECT("$1:$"&LP$1))),0)+COUNTIF(OFFSET(INDEX($F3:$LO3,LP$1),0,0,1,0-ROW(INDIRECT("$1:$"&LP$1))),"RDO")=ROW(INDIRECT("$1:$"&LP$1)),ROW(INDIRECT("$1:$"&LP$1))-COUNTIF(OFFSET(INDEX($F3:$LO3,LP$1),0,0,1,0-ROW(INDIRECT("$1:$"&LP$1))),"RDO"),"")),0)
Ok that seems to work but it leaves me with a couple of questions. If for example lets say that Sunday 8/15 gets skipped, I have it set to turn that cell yellow. But if I go back today and just fill in Sunday, but not Monday, LP doesn't count anything. I guess I can live with that just not sure why it does that.

Is the "helper cell" still needed and what exactly is the info that it is showing? (Mon01/02)?

Finally, if any cell in LP hits 14 or more, the entire corresponding name cell has to highlight in yellow. Once that number drops below 14 the name cell will lose the highlight. I think that might be a conditional formatting thing, just not sure how to do that.

I can't thank you enough for all the help so far. This is so far out of my skills so this is awesome that you are taking your time to help me.
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

RandyD123

Board Regular
Joined
Dec 4, 2013
Messages
195
Ok that seems to work but it leaves me with a couple of questions. If for example lets say that Sunday 8/15 gets skipped, I have it set to turn that cell yellow. But if I go back today and just fill in Sunday, but not Monday, LP doesn't count anything. I guess I can live with that just not sure why it does that.

Is the "helper cell" still needed and what exactly is the info that it is showing? (Mon01/02)?

Finally, if any cell in LP hits 14 or more, the entire corresponding name cell has to highlight in yellow. Once that number drops below 14 the name cell will lose the highlight. I think that might be a conditional formatting thing, just not sure how to do that.

I can't thank you enough for all the help so far. This is so far out of my skills so this is awesome that you are taking your time to help me.
So I see that the "helper cell" is needed. Just not sure why it references the "E" column? And why it shows a (random date?) with no spaces? Obviously I am fine with a helper cell just curious why it wouldn't reference the "F" column? I see that when I make it reference the F column the date changes to Sun01/01....

Also I noticed if I back the date up to 8/8 in F3, nothing gets counted until all cells prior to todays date get a number or an RDO. Which is fine. I think I like that. The fact that the cells highlight in yellow is a reminder that no blank cell are allowed!!!
 
Last edited:

RandyD123

Board Regular
Joined
Dec 4, 2013
Messages
195
So I see that the "helper cell" is needed. Just not sure why it references the "E" column? And why it shows a (random date?) with no spaces? Obviously I am fine with a helper cell just curious why it wouldn't reference the "F" column? I see that when I make it reference the F column the date changes to Sun01/01....

Also I noticed if I back the date up to 8/8 in F3, nothing gets counted until all cells prior to todays date get a number or an RDO. Which is fine. I think I like that. The fact that the cells highlight in yellow is a reminder that no blank cell are allowed!!!
So I figured out the conditional formatting for the name column. The only thing left is the "helper cell" explanation, why it references the "E" column and not the "F" column. And why it's formatted the way it is?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,115
Messages
5,768,204
Members
425,459
Latest member
Danniey

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