Help needed

  • Thread starter Thread starter Bryan De-Lara
  • Start date Start date
B

Bryan De-Lara

Hi, can anyone help please. I have a problem with a workbook I have done to
track absences at work. We use the Bradford system which is S S D. The first
S being the amount of times a person is absent, the second S is multiplied
by the first and finally the D is the number of days absent.
All that is fine and works very well. The problem I have is this.
I have to update the first S so that excel can do the calculation. I have a
row with 256 cells. 1 goes into each cell and is counted and updates D
automatically. The first S is an occurrence no matter if it is 1 day or the
whole year. But if say, January 1st was off then back on the 3rd would be 1
occurrence. Off again on the 6th and 7th, then back again would be 2
occurrences. How can I get it to count in blocks?
Anyone's help would be gratefully received. I'm pulling my hair out trying
to find books explaining if it can be done.
Bryan.
 
Bryan,

I'm a little unclear on your layout, but if you have only S and some other
code (like W, for Working), you could count the number of differences using
(note the offset of the range addresses)

=SUMPRODUCT((A2:A257="S")*(A3:A258<>"S"))

W
W
S
S
W
S
S
S
W
W
S
W
W
W
S

would result in that formula returning 4...

HTH,
Bernie
MS Excel MVP
 
Thanks Bernie, I'm sorry for being vague. I guess what I am trying to do is
this. Each cell in a row of 256 either is blank if you are at work, or has a
1 for each day absent. I need to count in blocks of 1's. i.e. 111 1
11111 1 11 which would equals 5 blocks or instances. The 1 being for
each day absent. I already count each 1 for each day, being D.
S S D is S = S * D. The first S the one I'm having a problem with. I do have
a workbook that I'm trying to improve on so that mistakes are less likely.
It is only 192kb and might be easier for me to send rather than confuse you
with how I am trying to explain it.

Regards.
Bryan.
 
Bryan,

No need for the file - your explanation this time is better.

Try this, for 1's entered into row 2:

=SUMPRODUCT((A2:IU2=1)*(B2:IV2<>1))

Note the offset of the ranges by 1 column.

HTH,
Bernie
MS Excel MVP
 
Thank you Bernie, just what the doctor ordered. Luckily I can add to cells
on the top as I've used all the cells across.
Anyway, thank you once again.

Bryan.
 
Back
Top