Count consecutive absences with a limit

[rhwong]

I have an attendance roster for the month and want to count the number of
absences. The problem I am having is that I can only count 5 consecutive
absences at one time. They can have 7 consecutive absences for one period and
then another set of absences for 5 days. That totals to 10 allowed absences
and 2 unallowed absences. How would you run a function to do this calculation
automatically.

 

[Domenic]

Can you provide a sample of the data, along with the actual/expected
results?

 

[rhwong via OfficeKB.com]

Hi Domenic
Here is a sample of the data:
A B C D E F G H I J K L M N
O P Q R S
NAME 1 2 3 4 5 6 7 8 9 10 11 12 13 14
15 Acutal A Expected A Total Present
STUDENT1 X A A A A A A X X X A A A X X
9 8 6
STUDENT2 X X X X A A A A A A A X X X X
7 5 8

A = NAME
B THRU P are the days
Q = actual absenses
R = total expected absences where if more than 5 are consecutive can only
count 5 and continue to count absences for the rest of the month.
S = total present.

Hope this sample helps.

 

[Domenic]

Try...

=SUM(IF(FREQUENCY(IF(B22="A",COLUMN(B22)),IF(B22<>"A",COLUMN(B22)
))<5,FREQUENCY(IF(B22="A",COLUMN(B22)),IF(B22<>"A",COLUMN(B22))),
5))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

 

[rhwong via OfficeKB.com]

I get a value error now.

Try...

=SUM(IF(FREQUENCY(IF(B22="A",COLUMN(B22)),IF(B22<>"A",COLUMN(B22)
))<5,FREQUENCY(IF(B22="A",COLUMN(B22)),IF(B22<>"A",COLUMN(B22))),
5))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

Hi Domenic
Here is a sample of the data:

[quoted text clipped - 15 lines]

Hope this sample helps.

 

[Domenic]

As mentioned, the formula needs to be confirmed with
CONTROL+SHIFT+ENTER, not just ENTER. In other words, press both the
CONTROL and SHIFT keys down, then while they're both pressed down, press
the ENTER key. Excel will automatically place braces {...} around the
formula indicating that you've entered it correctly.

Hope this helps!

I get a value error now.

Try...

=SUM(IF(FREQUENCY(IF(B22="A",COLUMN(B22)),IF(B22<>"A",COLUMN(B22)
))<5,FREQUENCY(IF(B22="A",COLUMN(B22)),IF(B22<>"A",COLUMN(B22))),
5))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

Hi Domenic
Here is a sample of the data:

[quoted text clipped - 15 lines]

Hope this sample helps.

 

[rhwong via OfficeKB.com]

Domenic,
I thought I did confirm it with CONTROL+SHIFT+ENTER. But I was wrong You are
the BOSS! Thank you it works.

I get a value error now.

Try...

[quoted text clipped - 11 lines]