Counting Consecutive Zero's - More info this time!

Status
Not open for further replies.

RandyD123

Board Regular
Joined
Dec 4, 2013
Messages
188
What I need is a formula that will work in column LP for each row.

Info that may help:

Each person has two rows because they can be in 1 of 2 location each day. Some days they may spend a few hours in each location.

My range is from F3 to LO3 for each person. All columns below row 2 are manual entries, except column LP!!

I only need to track consecutive zeros but not include RDO's

The date in the LP column will change to todays date using =TODAY()

Once any row for a person hits 14, meaning 14 consecutive zero's, not including RDO's their name has to change to yellow.

If a person gets any number on any given day, other than zero or RDO the count has to start over. Basically RDO's should be completely ignored, and only zero's get counted

In the example you can see what it should look like and what should happen.

On 6/29 column LL will get a manual input of either a whole number in quarter hour increments or get a zero or get another RDO.

LP3 counted 10 consecutive zero's and on 6/29 if they get a any number other than zero that current value of 10 has to change to a 0. If they get a zero on 6/29 that current value of 10 has to change to 11

LP4 counted 1 zero and on 6/29 it will either change to a 2 or if they get a number it has to change to 0

LP7 has to change to either 0 or 14, depending on what gets entered on 6/29. If it changes to 14 their name has to highlight in yellow.

LP10 has a 14 which means that their name should have highlighted in yellow on 6/28. If they get a number on 6/29, that value of 14 has to change to 0 and their name will lose the highlight.

I have a sheet on GDrive. Any help would be very much appreciated. Thank You.

Screenshot 2021-08-14 200847.png
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,892
Office Version
  1. 365
Platform
  1. Windows
Duplicate to: Counting consecutive Zeros

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread. If you do not receive a response, you can "bump" it by replying to it yourself, though we advise you to wait 24 hours before doing so, and not to bump a thread more than once a day.
 
Status
Not open for further replies.
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,697
Messages
5,765,987
Members
425,320
Latest member
Galin

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