• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Number of Child ID has >= 3 Consecutive Days Analysis in Excel

Lorens29

New Member
Hello,

Please help me find the Excel Formula to count number of Child ID has FALSE >=3 consecutive days.
I hope that someone can help me with the problem. I have attached an excel with the sample data.
Thank you to anyone who can solve this for me, and provide me with the excel logic to this, - please updated the table to show how
Many thanks once again. I hope my explanation was clear. I have googled and tried - but with little or no success.
 

Attachments

GraH - Guido

Well-Known Member
Try this to indicate on which date the period of 3 or more days begins
=COUNTIFS([Child ID],[@[Child ID]],[Child is Present?],FALSE(),[Attendance Date],"<="&([@[Attendance Date]]+2),[Attendance Date],">="&[@[Attendance Date]])>=3

Or when it ends
=COUNTIFS([Child ID],[@[Child ID]],[Child is Present?],FALSE(),[Attendance Date],">="&([@[Attendance Date]]-2),[Attendance Date],"<="&[@[Attendance Date]])>=3
 

Attachments

Lorens29

New Member
Thank you bro.

This formula work well.
If you don't mind, can you help me again to combine the formulas:
  1. =COUNTIFS([Child ID],[@[Child ID]],[Child is Present?],FALSE(),[Attendance Date],"<="&([@[Attendance Date]]+2),[Attendance Date],">="&[@[Attendance Date]])>=3 and =COUNTIF(Table1[Consecutive Absences of 3 or more days Start],TRUE())
  2. =COUNTIFS([Child ID],[@[Child ID]],[Child is Present?],FALSE(),[Attendance Date],">="&([@[Attendance Date]]-2),[Attendance Date],"<="&[@[Attendance Date]])>=3 and =COUNTIF(Table1[Consecutive Absences of 3 or more days End],TRUE())
The combine formula will help me to answer my problem that the number of Child ID who has >=3 Consecutyive absences equal 2 Child ID.

Thank you for help
 

GraH - Guido

Well-Known Member
Dear Lorens29,

You might want to visit the forum rules. And understand why it is polite to indicate in your post you have crossposted.

As for the formula:
- I noticed there could be a problem related to Sundays, as there is no school that day (from your data).
=COUNTIFS([Child ID],[@[Child ID]],[Child is Present?],FALSE(),[Attendance Date],"<="&WORKDAY.INTL([@[Attendance Date]],2,11),[Attendance Date],">="&[@[Attendance Date]])>=3
= COUNTIFS([Child ID],[@[Child ID]],[Child is Present?],FALSE(),[Attendance Date],">="&WORKDAY.INTL([@[Attendance Date]],-2,11),[Attendance Date],"<="&[@[Attendance Date]])>=3

I don't understand your latest question. Do you want to indicate all involved rows of the periods per child?
 

Lorens29

New Member
Dear Lorens29,

You might want to visit the forum rules. And understand why it is polite to indicate in your post you have crossposted.

As for the formula:
- I noticed there could be a problem related to Sundays, as there is no school that day (from your data).
=COUNTIFS([Child ID],[@[Child ID]],[Child is Present?],FALSE(),[Attendance Date],"<="&WORKDAY.INTL([@[Attendance Date]],2,11),[Attendance Date],">="&[@[Attendance Date]])>=3
= COUNTIFS([Child ID],[@[Child ID]],[Child is Present?],FALSE(),[Attendance Date],">="&WORKDAY.INTL([@[Attendance Date]],-2,11),[Attendance Date],"<="&[@[Attendance Date]])>=3

I don't understand your latest question. Do you want to indicate all involved rows of the periods per child?
Hi GraH - Guido,

Thank you for reminding me. I will deleted my posting on excelguru.
Thanks for noticed.
My latest question Is there perhaps a direct formula for getting results that there are 2 ChildIDs who were absent for 3 days or more?
Please look my explain in attachment (E1:F2) like this picture.

67839

The first Formula is correct because sundays and holidays not included in active day.

Thanks
 

Attachments

Last edited:

AlanSidman

Well-Known Member
I think that you may have missed the point of notifying when crossposting. It is not necessary to delete one or the other if you have crossposted. It is all about notifying each forum that you have crossposted and where. In this manner, anyone willing to help can see if there has been a solved response or not and decide whether to help or not. Reread the link I offered in post #6. This is a standard request at nearly all forums.
 
Top