Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

Calculate the number of times groupings of three zeros (0's) appear in a row

I tried using the "Countifs" (Google Measure) …but, I’m hoping there’s a simple(r) way to "Look" 👀 …at:

  1. rows of zeros (0’s) …and
  2. determine how many times three (3) zeros appear [together]
  3. see Google Sheet – below ⤵️

https://docs.google.com/spreadsheets/d/1BjMWeSzq2D7RjqEtNJs-emNdcv38PdYETsJWZDVfftQ/edit?gid=2100307022#gid=2100307022

Explained:

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

  • Employee A …has 4 instances (over 18 weeks)
  • Employee B …has 2 instances (over 18 weeks)
  • Employee C …has 6 instances (over 18 weeks)
  • Employee D …has 4 instances (over 18 weeks)

enter image description here

>Solution :

Here’s one approach you may test out:

=byrow(B2:S,lambda(Σ,if(counta(Σ)=0,,len(regexreplace(regexreplace(join(,Σ),"0{3}","Λ"),"[^Λ]",)))))

enter image description here

  • the formula assumes there will be no blank cells in a given row of 18 weeks
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading