Counting consecutive Zeros

[RandyD123]

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

 

[RandyD123]

I have corrected the 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.

 

[RandyD123]

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

 

[RandyD123]

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?