Hi Santanu,
Let me start by restating the problem:
- Need to count the number of Holidays and Sundays as Leave Days, when the day prior and day after are Leave Days.
- For example, LHSL would get counted as four Leave Days, with the 1 Holiday and 1 Sunday days included in that count.
- LHHHSL would get counted as six Leave Days, with the three Holidays and one Sunday included in the count.
- However, PHHSL would get counted as a single Leave Day, since the day before the Holiday is not a Leave Day.
We can break up the problem as follows:
- Since Holidays and Sundays behave the same way, we can treat them as the same. So in the explanation below, any reference to Holidays apply to Sundays also.
- We need to find the Holidays that are preceded by a Leave Day (L)
- Then for those Holidays that are preceded by an L, we need to find if they are followed by an L as well.
- Once we combine the result from #2 and#3, we get the count of Holidays that need to be counted as Leave Days.
For #2:
We can use a simple LOOKUP to determine if the day before the Holiday is a Leave Day. As you may recall, LOOKUP finds the previous value in an array that does not exceed the value being sought.
For #3:
We can again use LOOKUP, but this time we need to find the
next value in a sequence. (i.e. we need to find the next value, after the series of Holidays and Sundays.
Here is an explanation of the formula...
=COUNT(1/(LOOKUP(-
LOOKUP(IF(LOOKUP(IF(($G3:$X3="Sunday")+($G3:$X3="Holiday"), COLUMN($G3:$X3)), IF(NOT(($G3:$X3="Sunday")+($G3:$X3="Holiday")), COLUMN($G3:$X3)), $G3:$X3)="L", -(COLUMN($G3:$X3))), -LARGE(IF(NOT(($G3:$X3="Sunday")+($G3:$X3="Holiday")), COLUMN($G3:$X3)),ROW(OFFSET(A$1,,,COLUMNS($G3:$X3))))), COLUMN($G3:$X3), $G3:$X3)="L"),
1/($G3:$X3="L"))
Starting from the right, the segment in blue
1/($G3:$X3="L") counts the number of cells that have an "L" in them.
The segment in
green returns the value for the day before a Holiday. (It looks up the column numbers for days=Holidays in the column numbers for days <>Holidays. That would ensure that the
previous column number gets returned. Finally, the last part of this segment returns the actual cell value where the previous column number was found.)
We can now check if the value from the
green segment is an "L". If so, we have identified a Holiday that will is a
candidate to be counted as a Leave Day.
The next outer segment of the formula (simplified for ease of description below):
IF(<segment in
green>="L", -COLUMN($G3:$X3))
returns the Column Numbers whereever an L was found, but as negative values. The reason for using negative values will become clear shortly.
But before we discuss the reason for the negative values, let us look at the next segment of the formula. the segment in
red:
It finds the column numbers where the days are not Holidays.
It then reverses the order so that the array is in descending order.
Something like:
{24;18;17;16;15;14;12;10;9;8;7;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!}
The errors can be ignored since LOOKUP is very forgiving!
Now is time to explain the reason for using the negated numbers.
Normally, if you LOOKUP(4, {1,3,5,7}), you get 3 (which is the value that does not exceed the value you are looking for). But what if you want it to return 5 (which is the value larger than the value you are looking for)?
Fortunately for us, LOOKUP also works with negative numbers.
So, =LOOKUP(-4,{-7,-5,-3,-1}) would return -5. If we ignore the negative sign, you would notice that the returned value is higher (5) than the value we are seeking (4).
The segment in
bold (with the
green and
red portions) (besides looking like a Christmas tree) does the following:
For every column number where an "L" was found (which is the cell before the Holidays), it finds the column number of the cell
after the Holidays.
Now we are in the home-stretch!
The outermost LOOKUP then negates the result from the
bold section (resulting in a positive value), and locates it in the column numbers for the full range. The outermost LOOKUP thus returns the value of the cell immediately after a Holiday.
All we have to do now is to COUNT the instances where the outermost LOOKUP resulted in an "L".
1/(LOOKUP(...)="L") ensures that only matches are counted. (As you may recall, COUNT only counts numbers.)
That is it! Enjoy!!
Cheers,
Sajan.
