HI ALL,

I have a Sheet which has a Header Row which consists of Dates like
this:

01-May-06 02-May-06 03-May-06 04-May-06 05-May-06 06-May-06 07-May-06 08-May-06 09-May-06 10-May-06 11-May-06 12-May-06 13-May-06....etc..

on the Second row, i.e below Header row, i have Attendance marked for
employees corresponding to each day....like this:

PH L P P P WO WO LWP P P L L WO A A WO WO A etc....

On this row, in the last column, i want to create a formula which would
evaluate if there are 3 consecutive Absentisms (A) one after the
other, so that an AOD (Absent on Duty) can be raised for that employee.
i.e..

A A A

PH = Public Holiday. (rostered leave)
L = Leave (rostered leave)
P = Present
WO = Weekly Off
LWP = Leave without Pay (informed but not sanctioned leave - was
Rostered for that day.)
UL = Unpaid Leave (informed but not sanctioned leave - was Rostered for
that day.)
A - Absent

1] I want a formula such that it can evaluate, if there was a WO in
between the 3 A's.
3] find 3 consecutive A's in a row. (one after the other).
2] The Last Working Day (LWD) before the 3 A's, which could be (P, UL,
LWP) except (WO, PH, L).

PLEASE HELP ASAP

Please note that there should be 3 consecutive A's, one after the
other, & the Last Working Day would be a date (in Header column above),
which would be before the 3 A's. The only criteria is it should not be
a Weekly off (WO), or Leave(L), or PH (Public Holiday). Rest the Last
Working day could be a UL or a LWP.

So, How to find 3 consecutive A's in a Row & a LWD date (by Lookup)
before them.


PLEASE HELP ASAP.

Since its possible that there could be 3 A's in the beginning of the
month for some employees who were absent for 3 consecutive days but
later on rejoined the company ( were accepted by the company), i want
to find the latest 3 A's i.e..... 3 consecutive A's later in the month
& get a LWD just before them.


PLEASE HELP ASAP

so we only have to count three a's(to begin with),or do you need to know if
there were more than three?
--
paul

remove nospam for email addy!



"junoon" wrote:

Since its possible that there could be 3 A's in the beginning of the
month for some employees who were absent for 3 consecutive days but
later on rejoined the company ( were accepted by the company), i want
to find the latest 3 A's i.e..... 3 consecutive A's later in the month
& get a LWD just before them.


PLEASE HELP ASAP

Hi Paul,

Thanks for your reply,

Yes, Atleast 3 A's is a must & they should be the latest ones. Then
before the 3 A's you get the Last Working Day.


Emp Name 13/5/2006 14/5/2006 15/5/2006 16/5/2006 17/5/2006
LWD
john P WO A
A A 13/5/2006
Jacob P P A
A A 14/5/2006

In the 1st case for John, his LWD would be 13/5/2006, Jacob's would be
14th.

As you can see, I want to get the LWD Dates in Column "LWD".

Hope that Helps!

Assuming that B1:AF1 contains the data, and B2:AF2 contains the
attendance, try the following formulas, which need to be confirmed with
CONTROL+SHIFT+ENTER, not just ENTER...

AG2, copied down:

=MATCH(2,1/(COUNTIF(OFFSET(B2,,ROW(INDIRECT("1:"&COLUMNS(B2:A F2)-2))-1,,3
),"A")=3))

AH2, copied down:

=INDEX($B$1:$AF$1,MATCH(2,1/IF(ISNUMBER(MATCH(B2:AF2,{"LWP","P","UL"},0))
,IF(COLUMN(B2:AF2)-COLUMN(B2)+1<AG2,1))))

Hope this helps!

In article .com,
"junoon" wrote:

HI ALL,

I have a Sheet which has a Header Row which consists of Dates like
this:

01-May-06 02-May-06 03-May-06 04-May-06 05-May-06 06-May-06 07-May-06
08-May-06 09-May-06 10-May-06 11-May-06 12-May-06 13-May-06....etc..

on the Second row, i.e below Header row, i have Attendance marked for
employees corresponding to each day....like this:

PH L P P P WO WO LWP P P L L WO A A WO WO A etc....

On this row, in the last column, i want to create a formula which would
evaluate if there are 3 consecutive Absentisms (A) one after the
other, so that an AOD (Absent on Duty) can be raised for that employee.
i.e..

A A A

PH = Public Holiday. (rostered leave)
L = Leave (rostered leave)
P = Present
WO = Weekly Off
LWP = Leave without Pay (informed but not sanctioned leave - was
Rostered for that day.)
UL = Unpaid Leave (informed but not sanctioned leave - was Rostered for
that day.)
A - Absent

1] I want a formula such that it can evaluate, if there was a WO in
between the 3 A's.
3] find 3 consecutive A's in a row. (one after the other).
2] The Last Working Day (LWD) before the 3 A's, which could be (P, UL,
LWP) except (WO, PH, L).

PLEASE HELP ASAP

Excellent Domenic,

Thats just pure GENIUS!

But Please explain to me the formula, i.e. how it works, so that i can
apply that to different situations i.e. modify it.

Especially the part where you divide by 1 (in both formulas).


Warm Regards,

Junoon

Sorry,

I meant 1 getting divided by the formulas.....

Could please kindly explain me the breakup of both your formulas, so
that i can understand better.

PLEASE REPLY ASAP...as i have been trying different options before &
would like to know why these formulas work properly....

For simplicity sake, let's assume that A1:F2 contains the following
data...

Emp Name 13/5/2006 14/5/2006 15/5/2006 16/5/2006 17/5/2006
John P WO A A A

If we look at the following formula...

=MATCH(2,1/(COUNTIF(OFFSET(B2,,ROW(INDIRECT("1:"&COLUMNS(B2:F 2)-2))-1,,3)
,"A")=3))

....OFFSET references an array of ranges. Each range is made up of three
cells, B2:D2, C2:E2, and D2:F2. And COUNTIF counts the number of A's
for each range. So, the formula is evaluated as follows...

=MATCH(2,1/({1;2;3}=3))

=MATCH(2,1/{FALSE;FALSE;TRUE})

=MATCH(2,{#DIV/0!;#DIV0!;1})

....and returns 3. Note the following:

1) The numerical equivalent of TRUE and FALSE is 1 and 0, respectively.

2) 1 divided by TRUE or FALSE will always return 1 or #DIV/0!,
respectively.

3) 2 is used as the lookup value, and will always be larger than any
value in the lookup range.

4) The range type for match is omitted, and defaults to 1.

5) Since MATCH is not looking for an exact match, and 2 is larger than
any value in the lookup range, it returns the position of the last
numerical value, ignoring #DIV/0!.

Now, the following formula...

=INDEX($B$1:$F$1,MATCH(2,1/IF(ISNUMBER(MATCH(B2:F2,{"LWP","P","UL"},0)),I
F(COLUMN(B2:F2)-COLUMN(B2)+1<G2,1))))

....is evaluated as follows...

=INDEX($B$1:$F$1,MATCH(2,1/IF(ISNUMBER({2,#N/A,#N/A,#N/A,#N/A}),IF({2,3,4
,5,6}-2+1<3,1))))

=INDEX($B$1:$F$1,MATCH(2,1/IF({TRUE,FALSE,FALSE,FALSE,FALSE},IF({1,2,3,4,
5}<3,1))))

=INDEX($B$1:$F$1,MATCH(2,1/IF({TRUE,FALSE,FALSE,FALSE,FALSE},IF({TRUE,TRU
E,FALSE,FALSE,FALSE},1))))

=INDEX($B$1:$F$1,MATCH(2,1/{1,FALSE,FALSE,FALSE,FALSE}))

=INDEX($B$1:$F$1,MATCH(2,{1,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!}))

=INDEX($B$1:$F$1,1)

....and returns the first value indexed in B1:F1, which is 13/5/2006.

Hope this helps!

In article .com,
"junoon" wrote:

Sorry,

I meant 1 getting divided by the formulas.....

Could please kindly explain me the breakup of both your formulas, so
that i can understand better.

PLEASE REPLY ASAP...as i have been trying different options before &
would like to know why these formulas work properly....

Dom that s good!I was working on the same basic theory in that i had countif
and offset formulas looking at three cell ranges in a helper row to find the
LWD positions
I might have got to your formulas in a few days!
--
paul

remove nospam for email addy!



"Domenic" wrote:

For simplicity sake, let's assume that A1:F2 contains the following
data...

Emp Name 13/5/2006 14/5/2006 15/5/2006 16/5/2006 17/5/2006
John P WO A A A

If we look at the following formula...

=MATCH(2,1/(COUNTIF(OFFSET(B2,,ROW(INDIRECT("1:"&COLUMNS(B2:F 2)-2))-1,,3)
,"A")=3))

....OFFSET references an array of ranges. Each range is made up of three
cells, B2:D2, C2:E2, and D2:F2. And COUNTIF counts the number of A's
for each range. So, the formula is evaluated as follows...

=MATCH(2,1/({1;2;3}=3))

=MATCH(2,1/{FALSE;FALSE;TRUE})

=MATCH(2,{#DIV/0!;#DIV0!;1})

....and returns 3. Note the following:

1) The numerical equivalent of TRUE and FALSE is 1 and 0, respectively.

2) 1 divided by TRUE or FALSE will always return 1 or #DIV/0!,
respectively.

3) 2 is used as the lookup value, and will always be larger than any
value in the lookup range.

4) The range type for match is omitted, and defaults to 1.

5) Since MATCH is not looking for an exact match, and 2 is larger than
any value in the lookup range, it returns the position of the last
numerical value, ignoring #DIV/0!.

Now, the following formula...

=INDEX($B$1:$F$1,MATCH(2,1/IF(ISNUMBER(MATCH(B2:F2,{"LWP","P","UL"},0)),I
F(COLUMN(B2:F2)-COLUMN(B2)+1<G2,1))))

....is evaluated as follows...

=INDEX($B$1:$F$1,MATCH(2,1/IF(ISNUMBER({2,#N/A,#N/A,#N/A,#N/A}),IF({2,3,4
,5,6}-2+1<3,1))))

=INDEX($B$1:$F$1,MATCH(2,1/IF({TRUE,FALSE,FALSE,FALSE,FALSE},IF({1,2,3,4,
5}<3,1))))

=INDEX($B$1:$F$1,MATCH(2,1/IF({TRUE,FALSE,FALSE,FALSE,FALSE},IF({TRUE,TRU
E,FALSE,FALSE,FALSE},1))))

=INDEX($B$1:$F$1,MATCH(2,1/{1,FALSE,FALSE,FALSE,FALSE}))

=INDEX($B$1:$F$1,MATCH(2,{1,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!}))

=INDEX($B$1:$F$1,1)

....and returns the first value indexed in B1:F1, which is 13/5/2006.

Hope this helps!

In article .com,
"junoon" wrote:

Sorry,

I meant 1 getting divided by the formulas.....

Could please kindly explain me the breakup of both your formulas, so
that i can understand better.

PLEASE REPLY ASAP...as i have been trying different options before &
would like to know why these formulas work properly....