counting consecutive days

[Guest]

I work at a school and need some help in creating a formula and/or program for the following problem

Our school drops students from enrollment if they have CONSECUTIVELY missed 15 or more days. I need to create a formula that would "mark" any student who has reached 15 days. Here is an example of the spreadsheet

A B C D E F G H
1 Student #12 U R S
2 Student #13 X X X R
3 Student #14 R J
4 Student #15 U U J R
5 Student #16 R
6 Student #17 U U U R U J

The codes are
U = unexcused absenc
X = was not enrolle
R = release day (students do not report
J = excused absenc
S = suspende
<blank> = presen

The formula needs to
1) count the following codes as consecutive days:
2) start counting again when the following codes are recognized in the sequence: J or <blank
3) ignore the following codes when counting (treat as if were not there):

For example
Student #17 would have 4 consecutive days (Columns B thru F), but since she was excused in Column G, her consecutive days start again, when the next U is recognized (in this case, in Column H)

Any help in figuring this out would be greatly appreciated. If anyone needs more information or more clarification, just let me know

Thank for your help.

 

[Guest]

If you are looking for a "flag" to show when there are 15
consectutive "U", you might try to use conditional
formatting and set the evaluation formula to look at the
previous columns and if they are all "U" then turn the
cell red. Here is the formula I used:

=COUNTIF(S7:AG7,"U")>14

-----Original Message-----
I work at a school and need some help in creating a

formula and/or program for the following problem:

Our school drops students from enrollment if they have

CONSECUTIVELY missed 15 or more days. I need to create a
formula that would "mark" any student who has reached 15
days. Here is an example of the spreadsheet:

A B C D E F G H
1 Student #12 U R S S
2 Student #13 X X X R U
3 Student #14 R J U
4 Student #15 U U J R
5 Student #16 R U
6 Student #17 U U U R U J U


The codes are:
U = unexcused absence
X = was not enrolled
R = release day (students do not report)
J = excused absence
S = suspended
<blank> = present


The formula needs to:
1) count the following codes as consecutive days: U
2) start counting again when the following codes are

3) ignore the following codes when counting (treat as if were not there): R


For example:
Student #17 would have 4 consecutive days (Columns B thru

F), but since she was excused in Column G, her consecutive
days start again, when the next U is recognized (in this
case, in Column H).

Any help in figuring this out would be greatly

appreciated. If anyone needs more information or more
clarification, just let me know.

 

[Guest]

The formula you gave works for "U"'s, but not consecutively.

Like I stated before, if a student misses 13 days, but on the 14th day, she is here (which would = <blank> in the spreadsheet) and then missed the 15th day, she would not be dropped, because she was here within the 15 day drop. Likewise, if she missed 13 days, but had an excused absence ("J") on the 14th day, and then missed again on the 15th day, she would not be dropped, because she was excused on the 14th day. In this example, her consecutive "drop" days would start over (from 1) on the 15th day

Any suggestions

 

[Jeff]

Mel,

If you send me your email address, I'll send you
a spreadsheet with a macro to do what you want I
believe. (I'll send after work - remove NOSPAM
from my email address below)

Jeff

(e-mail address removed)

-----Original Message-----
The formula you gave works for "U"'s, but not consecutively.

Like I stated before, if a student misses 13 days, but

on the 14th day, she is here (which would = <blank> in
the spreadsheet) and then missed the 15th day, she would
not be dropped, because she was here within the 15 day
drop. Likewise, if she missed 13 days, but had an
excused absence ("J") on the 14th day, and then missed
again on the 15th day, she would not be dropped, because
she was excused on the 14th day. In this example, her
consecutive "drop" days would start over (from 1) on the
15th day.