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

##### New Member
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.

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

#### JoeMo

##### MrExcel MVP
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

##### New Member
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

##### New Member
The AvgConsecZeros function is returning zero under all situations.

#### Rick Rothstein

##### MrExcel MVP
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:

##### New Member
Thank you Rick. I can now do both task using the separate codes though

Cheers

##### New Member
The two codes have worked.

Thanks once more.

#### Peter_SSs

##### MrExcel MVP, Moderator
.. 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:

##### New Member
.. or just use standard worksheet functions?

Attendance

 A B C D E F G H I J K L M 1 Day1 Day2 Day3 Day4 Day5 Day6 Day7 Day8 Day9 Day10 Avg Here Avg Away 2 Student1 1 1 1 1 1 1.666666667 1.25 3 Student2 1 1 1 1 1 1 1.5 1.333333333 4 Student3 1 1 1 1 1 1 1 1 4 2 5 Student4 NA 10 6 Student5 1 1 1 1 1 1 1 1 1 1 10 NA

<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>
</tbody>

 Cell Formula 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

Thanks Peter,
It really did.