I am wondering if Excel has the capability of detecting a series of numbers.
For example, I am tracking attendance in a given row with "1" being present
and "0" being absent. When someone is absent for three days straight (0 0 0
in a given row), can Excel give me a warning of some kind? I know that with
Filters, Excel can give various warnings or prompts.

Any help you can provide would be appreciated!

Hi
one way:
- select C1:X1
- goto 'Format - conditional Format' and choose 'Formula is'
- enter the formula:
=COUNTIF(A1:C1,0)=3
- choose your format

"gracegift" wrote:

I am wondering if Excel has the capability of detecting a series of numbers.
For example, I am tracking attendance in a given row with "1" being present
and "0" being absent. When someone is absent for three days straight (0 0 0
in a given row), can Excel give me a warning of some kind? I know that with
Filters, Excel can give various warnings or prompts.

Any help you can provide would be appreciated!

Perhaps 2 options to play around with ..

Assume you have this kind of set-up=20
in Sheet1, cols A to Z (say),
data in row2 down:

------ 1-Oct 2-Oct 3-Oct 4-Oct 5-Oct 6-Oct etc
Max 0 1 0 0 0 0
Peter 0 0 0 0 0 1
Tom 1 0 0 0 1 1
Jane 1 1 0 0 0 0
etc

where B1:Z1 will contain the actual dates (in "d-mmm"=20
format) of all *working days only*

Option 1: Use conditional formatting to highlight the 1st=20
cell of any 3 consecutive zero cells in-a-row within the=20
grid (as per your specs)

Select the "inside"** range: B2:Z100 (say)=20
**range excluding the top row and col A =20

Click Format Conditional Formatting
Under Condition 1, make the settings as:
Formula Is |=3D AND(NOT(OR($A2=3D"",COUNTA(B2:D2)<3)),SUM
(B2:D2)=3D0)
Click Format button Patterns tab brown? OK
Click OK at the main dialog

For the sample data above, you'll see that cells:=20

D2, E2,=20
B3, C3, D3,=20
C4,=20
D5 and E5

will be conditionally formatted with brown fill color

Option 2: Extract alert phrases containing the names and=20
period of any 3 consecutive zero cells in-a-row within the=20
attendance table in Sheet1
=20
In a new Sheet2
-------------------
Put in A2:

=3DIF(OR(Sheet1!$A2=3D"",COUNTA(Sheet1!B2:D2)<3)," ",IF(SUM
(Sheet1!B2:D2)=3D0,Sheet1!$A2&": "&TEXT(Sheet1!B$1,"d-mmm")
&" to "&TEXT(Sheet1!D$1,"d-mmm"),""))

Copy across as many cols as you have working dates in=20
Sheet1, then fill down by as many rows as there are names=20
expected in col A in Sheet1

The formulas will return the alert phrases such as:

Max: 3-Oct to 5-Oct
Peter: 1-Oct to 3-Oct Peter: 2-Oct to 4-Oct Peter: 3-Oct=20
to 5-Oct
Tom: 2-Oct to 4-Oct=20
Jane: 3-Oct to 5-Oct

--
Rgds
Max
xl 97
---
GMT+8, 1=B0 22' N 103=B0 45' E
xdemechanik <atyahoo<dotcom
----
"gracegift" wrote:
I am wondering if Excel has the capability of detecting a=20
series of numbers. =20
For example, I am tracking attendance in a given row=20
with "1" being present=20
and "0" being absent. When someone is absent for three=20
days straight (0 0 0=20
in a given row), can Excel give me a warning of some=20
kind? I know that with=20
Filters, Excel can give various warnings or prompts.

Any help you can provide would be appreciated!

The easiest way I see to do this is to use a Worksheet_Change event macro.
This macro would fire automatically if any cell entry changes, and it would
check to see if the changed cell is within a specific range, and if it is it
would check to see if the entry is zero, and if it is it would check to see
if this zero entry is the third of 3 consecutive zero entries. The result
would be a message box saying whatever you want it to say or maybe an
automatic printout of whatever you want. An example macro is:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1:G10")) Is Nothing Then Exit Sub
If Target.Count 1 Then Exit Sub
If Target = "" Then Exit Sub
If Target.Column < 3 Then Exit Sub
If Target.Value = 0 Then
If Application.CountA(Target.Offset(, -2).Resize(, 2)) = 2 And _
Application.Sum(Target.Offset(, -2).Resize(, 2)) = 0 And _
IsNumeric(Target.Offset(, -2)) And
IsNumeric(Target.Offset(, -1)) Then
MsgBox "Three in a row at " & Target.Offset(, -2).Resize(,
3).Address(0, 0)
End If
End If
End Sub
Change the A1:G10 range to the range in your data that would hold the 1 and
0 numbers. This macro may need to be massaged a little depending on the
layout of your data. This macro has to be placed in the sheet module of the
sheet that holds your data. Right-click on the sheet tab, select View Code,
and paste this macro into the displayed module. Post back if you need more.
HTH Otto
"gracegift" wrote in message
...
I am wondering if Excel has the capability of detecting a series of
numbers.
For example, I am tracking attendance in a given row with "1" being
present
and "0" being absent. When someone is absent for three days straight (0 0
0
in a given row), can Excel give me a warning of some kind? I know that
with
Filters, Excel can give various warnings or prompts.

Any help you can provide would be appreciated!

Frank:

Thanks SO much for your reply. Your simple formula totally did the trick!


"Frank Kabel" wrote:

Hi
one way:
- select C1:X1
- goto 'Format - conditional Format' and choose 'Formula is'
- enter the formula:
=COUNTIF(A1:C1,0)=3
- choose your format

"gracegift" wrote:

I am wondering if Excel has the capability of detecting a series of numbers.
For example, I am tracking attendance in a given row with "1" being present
and "0" being absent. When someone is absent for three days straight (0 0 0
in a given row), can Excel give me a warning of some kind? I know that with
Filters, Excel can give various warnings or prompts.

Any help you can provide would be appreciated!

Thanks for your reply. I appreciate your time!

"Max" wrote:

Perhaps 2 options to play around with ..

Assume you have this kind of set-up
in Sheet1, cols A to Z (say),
data in row2 down:

------ 1-Oct 2-Oct 3-Oct 4-Oct 5-Oct 6-Oct etc
Max 0 1 0 0 0 0
Peter 0 0 0 0 0 1
Tom 1 0 0 0 1 1
Jane 1 1 0 0 0 0
etc

where B1:Z1 will contain the actual dates (in "d-mmm"
format) of all *working days only*

Option 1: Use conditional formatting to highlight the 1st
cell of any 3 consecutive zero cells in-a-row within the
grid (as per your specs)

Select the "inside"** range: B2:Z100 (say)
**range excluding the top row and col A

Click Format Conditional Formatting
Under Condition 1, make the settings as:
Formula Is |= AND(NOT(OR($A2="",COUNTA(B2:D2)<3)),SUM
(B2:D2)=0)
Click Format button Patterns tab brown? OK
Click OK at the main dialog

For the sample data above, you'll see that cells:

D2, E2,
B3, C3, D3,
C4,
D5 and E5

will be conditionally formatted with brown fill color

Option 2: Extract alert phrases containing the names and
period of any 3 consecutive zero cells in-a-row within the
attendance table in Sheet1

In a new Sheet2
-------------------
Put in A2:

=IF(OR(Sheet1!$A2="",COUNTA(Sheet1!B2:D2)<3),"",IF (SUM
(Sheet1!B2:D2)=0,Sheet1!$A2&": "&TEXT(Sheet1!B$1,"d-mmm")
&" to "&TEXT(Sheet1!D$1,"d-mmm"),""))

Copy across as many cols as you have working dates in
Sheet1, then fill down by as many rows as there are names
expected in col A in Sheet1

The formulas will return the alert phrases such as:

Max: 3-Oct to 5-Oct
Peter: 1-Oct to 3-Oct Peter: 2-Oct to 4-Oct Peter: 3-Oct
to 5-Oct
Tom: 2-Oct to 4-Oct
Jane: 3-Oct to 5-Oct

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"gracegift" wrote:
I am wondering if Excel has the capability of detecting a
series of numbers.
For example, I am tracking attendance in a given row
with "1" being present
and "0" being absent. When someone is absent for three
days straight (0 0 0
in a given row), can Excel give me a warning of some
kind? I know that with
Filters, Excel can give various warnings or prompts.

Any help you can provide would be appreciated!

Thanks for your reply! I appreciate your time!

"Otto Moehrbach" wrote:

The easiest way I see to do this is to use a Worksheet_Change event macro.
This macro would fire automatically if any cell entry changes, and it would
check to see if the changed cell is within a specific range, and if it is it
would check to see if the entry is zero, and if it is it would check to see
if this zero entry is the third of 3 consecutive zero entries. The result
would be a message box saying whatever you want it to say or maybe an
automatic printout of whatever you want. An example macro is:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1:G10")) Is Nothing Then Exit Sub
If Target.Count 1 Then Exit Sub
If Target = "" Then Exit Sub
If Target.Column < 3 Then Exit Sub
If Target.Value = 0 Then
If Application.CountA(Target.Offset(, -2).Resize(, 2)) = 2 And _
Application.Sum(Target.Offset(, -2).Resize(, 2)) = 0 And _
IsNumeric(Target.Offset(, -2)) And
IsNumeric(Target.Offset(, -1)) Then
MsgBox "Three in a row at " & Target.Offset(, -2).Resize(,
3).Address(0, 0)
End If
End If
End Sub
Change the A1:G10 range to the range in your data that would hold the 1 and
0 numbers. This macro may need to be massaged a little depending on the
layout of your data. This macro has to be placed in the sheet module of the
sheet that holds your data. Right-click on the sheet tab, select View Code,
and paste this macro into the displayed module. Post back if you need more.
HTH Otto
"gracegift" wrote in message
...
I am wondering if Excel has the capability of detecting a series of
numbers.
For example, I am tracking attendance in a given row with "1" being
present
and "0" being absent. When someone is absent for three days straight (0 0
0
in a given row), can Excel give me a warning of some kind? I know that
with
Filters, Excel can give various warnings or prompts.

Any help you can provide would be appreciated!

You're welcome !
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"gracegift" wrote in message
...
Thanks for your reply. I appreciate your time!