B
Bryan De-Lara
Bernie helped me with my last request, and I thank him.
But...always a but. His formula works a treat.
What I have is one work book with 3 sheets. The first has the names of staff
in the 1st column, second column is S, 3rd is S, 4th is D.
Name S S D. Formula is S*S*D.
The 2nd sheet is 1st column with the names again. From B to IU is the
working days of the year, (2007) IV being the total of the cells added
together when a 1 is entered in each cell.
This is the D on page 1. The S on page 1 is the formula that Bernie gave me
which is =SUMPRODUCT((A4:IU4=1)*(B4:IV4<>1)). This counts the occurrences of
blocks of 1's. i.e. 1 111 11 1111= 4.
This is the first S, the 2nd S = the first, so it would be 4*4*D (which is
10) 160.
The 3rd sheet is as the 2nd apart that it is 2008.
As it is for absences and we use the Bradford system it's a rolling year. So
I add sheet 2 & 3 together. The problem I have is if someone has 31st
December off in 2007, and the 2nd January in 2008 it is one absence, it is
the same absence so should only be counted as 1. I add 2007 to 2008 to give
a points total. As there is no space between the cells on page 2 & 3 it
increases S by 1 which it should do the way it is set up. It gives 1
occurrence for Dec 31st and 1 for Jan 2nd.
How can I make it 1 occurrence and not 2.
I've played a bit with it, but I'm getting nowhere fast.
Any help would be kindly accepted.
Bryan.
But...always a but. His formula works a treat.
What I have is one work book with 3 sheets. The first has the names of staff
in the 1st column, second column is S, 3rd is S, 4th is D.
Name S S D. Formula is S*S*D.
The 2nd sheet is 1st column with the names again. From B to IU is the
working days of the year, (2007) IV being the total of the cells added
together when a 1 is entered in each cell.
This is the D on page 1. The S on page 1 is the formula that Bernie gave me
which is =SUMPRODUCT((A4:IU4=1)*(B4:IV4<>1)). This counts the occurrences of
blocks of 1's. i.e. 1 111 11 1111= 4.
This is the first S, the 2nd S = the first, so it would be 4*4*D (which is
10) 160.
The 3rd sheet is as the 2nd apart that it is 2008.
As it is for absences and we use the Bradford system it's a rolling year. So
I add sheet 2 & 3 together. The problem I have is if someone has 31st
December off in 2007, and the 2nd January in 2008 it is one absence, it is
the same absence so should only be counted as 1. I add 2007 to 2008 to give
a points total. As there is no space between the cells on page 2 & 3 it
increases S by 1 which it should do the way it is set up. It gives 1
occurrence for Dec 31st and 1 for Jan 2nd.
How can I make it 1 occurrence and not 2.
I've played a bit with it, but I'm getting nowhere fast.
Any help would be kindly accepted.
Bryan.