How to check two consecutive occurrences

Question

How to check two consecutive occurrences

Hi there,

Does anybody know if there is a way to count the number of consecutive occurrences in a column?

As it is not so easy to explain exactly what I want with words, here's an example.

I am an English teacher and I have a group with three students.

I have created a table to control their attendance.

I would like a formula to inform me every time each student misses two classes in a roll.

Here's my table... Can somebody help me to accomplish that in the "Two Consecutive Absences" table? ...Thanks.

Mac mini (Late 2012), iOS 12.0.1, null

Posted on Oct 23, 2018 1:35 PM

Reply

Answer 1

SGIII

Level 8

37,562 points

Oct 23, 2018 3:16 PM in response to JMAURICIOENGLISH

JMAURICIOENGLISH wrote:

I thought there would be a single formula for the "Two Consecutive Absences table"... something like...

=countifs((attendance:control::B);"Absent")...

There are single formula approaches in Excel, using "array formulas". But Numbers doesn't support those. So I think in Numbers the only way is to have "counter" columns or similar.

SG

Reply

Answer 2

Oct 23, 2018 2:37 PM in response to SGIII

Thanks for your quick response, SG.

I tried something similar to your suggestion. Mine was a bit longer than yours, but it also did the trick.

I thought there would be a single formula for the "Two Consecutive Absences table"... something like...

=countifs((attendance:control::B);"Absent")... but with something else to sum only the consecutive occurrences.

Again... your way helped me to reduce the amount of formulas, columns and rows I had created to accomplish the task.

Thank you very much.

João

Reply

Answer 3

Oct 24, 2018 8:38 AM in response to SGIII

Yeah... I understand. Thanks.

Reply

Answer 4

SGIII

Level 8

37,562 points

Oct 23, 2018 2:37 PM in response to JMAURICIOENGLISH

One way is to add an "counter" column for each student in the Attendance Control table, and then hide those columns if you want.

The formula in E2 filled right and down:

=IF(AND(B1="ABSENT",B2="ABSENT"),1,"")

The formula in A1 of the second table, filled right:

=SUM(ATTENDANCE CONTROL::E)

Make sure you don't have any $ anchors in the formulas.

Replace the , with ; in the formulas if your region uses , as a decimal separator.

SG

Reply