Count consecutive absences with a limit
 

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.

Count consecutive absences with a limit
 

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

In article <667be0932f52d@uwe, "rhwong" <u26788@uwe wrote:

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.

Count consecutive absences with a limit
 

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 wrote:
Can you provide a sample of the data, along with the actual/expected
results?

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.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200609/1

Count consecutive absences with a limit
 

Try...

=SUM(IF(FREQUENCY(IF(B2:P2="A",COLUMN(B2:P2)),IF(B 2:P2<"A",COLUMN(B2:P2)
))<5,FREQUENCY(IF(B2:P2="A",COLUMN(B2:P2)),IF(B2:P 2<"A",COLUMN(B2:P2))),
5))

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

Hope this helps!

In article <6692c2d6c4ada@uwe, "rhwong via OfficeKB.com" <u26788@uwe
wrote:

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.

Count consecutive absences with a limit
 

I get a value error now.

Domenic wrote:
Try...

=SUM(IF(FREQUENCY(IF(B2:P2="A",COLUMN(B2:P2)),IF( B2:P2<"A",COLUMN(B2:P2)
))<5,FREQUENCY(IF(B2:P2="A",COLUMN(B2:P2)),IF(B2: P2<"A",COLUMN(B2:P2))),
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.

--
Message posted via http://www.officekb.com

Count consecutive absences with a limit
 

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!

In article <66937e42e97b6@uwe, "rhwong via OfficeKB.com" <u26788@uwe
wrote:

I get a value error now.

Domenic wrote:
Try...

=SUM(IF(FREQUENCY(IF(B2:P2="A",COLUMN(B2:P2)),IF( B2:P2<"A",COLUMN(B2:P2)
))<5,FREQUENCY(IF(B2:P2="A",COLUMN(B2:P2)),IF(B2: P2<"A",COLUMN(B2:P2))),
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.

Count consecutive absences with a limit
 

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

rhwong wrote:
I get a value error now.

Try...

[quoted text clipped - 11 lines]

Hope this sample helps.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200609/1