How to count a number of consecutive cells with 1 in row that is intermittently interrupted with blank

A

Adamu Mani

New Member
Joined
Oct 28, 2019
Messages
6
Good day all,
My excel skills are very poor. I have a situation where I want compute the average number of times a student attends class consecutively over a period of say a semester. Rows represent individual students and the columns are days. In the cells are either 1 if students attends or blank if he does'nt. I want return the average number of days each student attends without break. I also want to know the average number of days a student is consecutively absent.
I checked the platform for a similar situation but I did not identify any.

Thank you for your help.
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sort by date Sort by votes
Here are a pair of User Defined Functions (UDF) you can try after you install them in your workbook.

To install the UDF:
1. With your workbook active press Alt and F11 keys. This will open the VBE window.
2. In the project tree on the left of the VBE window, find your project and click on it.
3. On the VBE menu: Insert>Module
4. Copy the UDF from your browser window and paste it into the white space in the VBE window.
5. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
6. Use the UDF as you would any native Excel function.
7. Make sure you have enabled macros whenever you open the file or the code will not run.

Below the UDFs is an example so you can see how to apply them.
Rich (BB code): 
Function AvgConsecOnes(R As Range) As Double
'assumes R is a row of cells filled with either 0 or 1
' find average number of consecutive 1's in R
Dim S As Long, V As Variant, CtOnes As Long, G As Long
V = R.Value
ReDim A(1 To UBound(V, 2))
CtOnes = Application.CountIf(R, 1)
For i = LBound(V, 2) To UBound(V, 2)
    If V(1, i) = 1 Then
        If i = UBound(V, 2) Then G = G + 1
        S = S + 1
    Else
        If S > 0 Then G = G + 1
        S = 0
    End If
Next i
If G > 0 Then
    AvgConsecOnes = CtOnes / G
Else
    AvgConsecOnes = 0
End If
End Function
Function AvgConsecZeros(R As Range) As Double
'assumes R is a row of cells filled with either 0 or 1
' find average number of consecutive 0's in R
Dim S As Long, V As Variant, A() As Long, CtZeros As Long, G As Long
V = R.Value
ReDim A(1 To UBound(V, 2))
CtZeros = Application.CountIf(R, 0)
For i = LBound(V, 2) To UBound(V, 2)
    If V(1, i) = 0 Then
        If i = UBound(V, 2) Then G = G + 1
        S = S + 1
    Else
        If S > 0 Then G = G + 1
        S = 0
    End If
Next i
If G > 0 Then
    AvgConsecZeros = CtZeros / G
Else
    AvgConsecZeros = 0
End If
End Function
Excel Workbook
ABCDEFGHIJKLM
1Day1Day2Day3Day4Day5Day6Day7Day8Day9Day10Avg1'sAvg0's
2Student101011011001.6671.250
3Student210111010011.5001.333
4Student311111001114.0002.000
Sheet2
 
Upvote 0
Dear Joe,
Thank you so much for the UDF and the step-by-step guide on how to implement it.
I am very grateful.
Cheers

Adamu Mani.
 
Upvote 0
Here is another way to write the UDF's that JoeMo posted...
Code: 
Function AvgConsecOnes(R As Range) As Double
  Dim Sum As Long, Txt As String
  Txt = Application.Trim(Replace(Join(Application.Index(R.Value, 1, 0), ""), 0, " "))
  Sum = Len(Replace(Txt, " ", ""))
  AvgConsecOnes = Sum / UBound(Split(Txt & " "))
End Function

Function AvgConsecZeros(R As Range) As Double
  Dim Sum As Long, Txt As String
  Txt = Application.Trim(Replace(Join(Application.Index(R.Value, 1, 0), ""), 1, " "))
  Sum = Len(Replace(Txt, " ", ""))
  AvgConsecZeros = Sum / UBound(Split(Txt & " "))
End Function

EDIT NOTE
----------------------------
Actually, in thinking about it, these two UDF's can be combined into a single UDF by adding an extra argument allowing the programmer to specify 0 or 1 as the number to be averaged...
Code: 
Function AvgConsec(R As Range, ZeroOrOne As Long) As Double
  Dim Sum As Long, Txt As String
  Txt = Application.Trim(Replace(Join(Application.Index(R.Value, 1, 0), ""), 1 - ZeroOrOne, " "))
  Sum = Len(Replace(Txt, " ", ""))
  AvgConsec = Sum / UBound(Split(Txt & " "))
End Function
Note the change in the function's name. To use it to average the 1's...

=AvgConsec(B2:K2,1)

To average the 0's...

=AvgConsec(B2:K2,0)
 
Last edited:
Upvote 0
.. or just use standard worksheet functions?

<b>Attendance</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:71px;" /><col style="width:49px;" /><col style="width:49px;" /><col style="width:49px;" /><col style="width:49px;" /><col style="width:49px;" /><col style="width:49px;" /><col style="width:49px;" /><col style="width:49px;" /><col style="width:49px;" /><col style="width:56px;" /><col style="width:86px;" /><col style="width:99px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td><td >L</td><td >M</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:center; ">Day1</td><td style="font-size:10pt; text-align:center; ">Day2</td><td style="font-size:10pt; text-align:center; ">Day3</td><td style="font-size:10pt; text-align:center; ">Day4</td><td style="font-size:10pt; text-align:center; ">Day5</td><td style="font-size:10pt; text-align:center; ">Day6</td><td style="font-size:10pt; text-align:center; ">Day7</td><td style="font-size:10pt; text-align:center; ">Day8</td><td style="font-size:10pt; text-align:center; ">Day9</td><td style="font-size:10pt; text-align:center; ">Day10</td><td style="font-size:10pt; text-align:right; ">Avg Here</td><td style="font-size:10pt; text-align:right; ">Avg Away</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:10pt; ">Student1</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">1.666666667</td><td style="font-size:10pt; text-align:right; ">1.25</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:10pt; ">Student2</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:right; ">1.5</td><td style="font-size:10pt; text-align:right; ">1.333333333</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:10pt; ">Student3</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:right; ">4</td><td style="font-size:10pt; text-align:right; ">2</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-size:10pt; ">Student4</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">NA</td><td style="font-size:10pt; text-align:right; ">10</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="font-size:10pt; ">Student5</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:right; ">10</td><td style="font-size:10pt; text-align:right; ">NA</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >L2</td><td >=IFERROR(SUM<span style=' color:008000; '>(B2:K2)</span>/COUNTIFS<span style=' color:008000; '>(B2:K2,1,A2:J2,"<>1")</span>,"NA")</td></tr><tr><td >M2</td><td >=IFERROR(COUNTBLANK<span style=' color:008000; '>(B2:K2)</span>/COUNTIFS<span style=' color:008000; '>(B2:K2,"",A2:J2,"<>")</span>,"NA")</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4
 
Last edited:
Upvote 0
Peter_SSs said:
.. or just use standard worksheet functions?

Attendance

ABCDEFGHIJKLM
Student1
Student2
Student3
Student4
Student5

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:71px;"><col style="width:49px;"><col style="width:49px;"><col style="width:49px;"><col style="width:49px;"><col style="width:49px;"><col style="width:49px;"><col style="width:49px;"><col style="width:49px;"><col style="width:49px;"><col style="width:56px;"><col style="width:86px;"><col style="width:99px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="align: center"]Day1[/TD]
[TD="align: center"]Day2[/TD]
[TD="align: center"]Day3[/TD]
[TD="align: center"]Day4[/TD]
[TD="align: center"]Day5[/TD]
[TD="align: center"]Day6[/TD]
[TD="align: center"]Day7[/TD]
[TD="align: center"]Day8[/TD]
[TD="align: center"]Day9[/TD]
[TD="align: center"]Day10[/TD]
[TD="align: right"]Avg Here[/TD]
[TD="align: right"]Avg Away[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="align: center"]1[/TD]

[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]

[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]

[TD="align: right"]1.666666667[/TD]
[TD="align: right"]1.25[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="align: center"]1[/TD]

[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]

[TD="align: center"]1[/TD]

[TD="align: center"]1[/TD]
[TD="align: right"]1.5[/TD]
[TD="align: right"]1.333333333[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]

[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="align: right"]NA[/TD]
[TD="align: right"]10[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]NA[/TD]

</tbody>

Spreadsheet Formulas
CellFormula
L2=IFERROR(SUM(B2:K2)/COUNTIFS(B2:K2,1,A2:J2,"<>1"),"NA")
M2=IFERROR(COUNTBLANK(B2:K2)/COUNTIFS(B2:K2,"",A2:J2,"<>"),"NA")

<tbody>
</tbody>

<tbody>
</tbody>

Excel tables to the web - Excel Jeanie Html 4
Click to expand...


Thanks Peter,
It really did.
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question Question
Count if consecutive value is repeated 3 times.
Replies
2
Views
1K
Fluff
Fluff
T
  • Question Question
Count records for a date(day) and then average over the number of days
Replies
1
Views
594
RasGhul
RasGhul
G
  • Solved
Excel 2016 number of consecutive cells in row = 3
2
Replies
10
Views
1K
Gregory123987
G
M
  • Question Question
How do I add the last consecutive cells starting from right to left and also reset and/or skip particular cells?
Replies
6
Views
1K
Eric W
Eric W
A
  • Question Question
COUNT two or more consecutive text values and check each if it's +/- than a value
Replies
2
Views
668
aspasovski86
A

Forum statistics

Threads
1,224,818
Messages
6,181,152
Members
453,021
Latest member
Justyna P

Share this page

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
Back
Top