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.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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
.. 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.
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,570
Latest member
rik81h

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