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

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.
 

Some videos you may like

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
Joined
May 26, 2009
Messages
16,692
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
 

Adamu Mani

New Member
Joined
Oct 28, 2019
Messages
6
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.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,666
Office Version
2010
Platform
Windows
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:

Adamu Mani

New Member
Joined
Oct 28, 2019
Messages
6
The two codes have worked.

Thanks once more.:)
Adamu
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
43,719
Office Version
365
Platform
Windows
.. 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:

Adamu Mani

New Member
Joined
Oct 28, 2019
Messages
6
.. or just use standard worksheet functions?

Attendance

ABCDEFGHIJKLM
1 Day1Day2Day3Day4Day5Day6Day7Day8Day9Day10Avg HereAvg Away
2Student1 1 11 11 1.6666666671.25
3Student21 111 1 11.51.333333333
4Student311111 11142
5Student4 NA10
6Student5111111111110NA

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

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

Thanks Peter,
It really did.
 

Watch MrExcel Video

Forum statistics

Threads
1,090,445
Messages
5,414,553
Members
403,532
Latest member
mikexcel12

This Week's Hot Topics

Top