Calculate number of consecutive occurances over 14
Hi all,
Trying to sort some data in a spreadsheet...
We have a table that shows annual leave and I need to find any rows that contain more than 14 consecutive number 1s to signify more than 14 days off in a row.
The sheet is attached, 0 means no leave taken, 1 means a day taken
Any help would be MASSIVELY appreciated
CF in B2:
=AND(B2=1,SUMIF($B$1:B$1,">="&B$1-14,$B2:B2)>13)
Quang PT
Hi,
It comes up with False? Even when I know the row has met the criteria.
Last edited by GeorgeBarber; 06-08-2021 at 07:32 AM.
Provide a sample file demonstrating the problem.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
Attached is the sample file with the formula inOriginally Posted by AliGW
Bebo's formula is meant to be used as a conditional formatting rule as illustrated in the file attached to post #2.
In the file attached to post #5 the formula has been placed into cell B2 instead, which is causing a circular referenced error.
Please tell us if the conditional formatting in the file attached to post #2 is not what you want, and why.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Hi All,
I have now sorted through the data manually, so thank you all for your help anyway.
I was trying to find a way to indicate in one column whether that sequence came up, so it would be easier to sift the data.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks