Calculating bradford factors (array formula?)

Hi all,

I'm trying to come up with a way to calculate the bradford factors of each employee using a formula. The guy who worked on this before me devised a way to do it, but it comprises of 3 separate formulas and makes things overly complicated when adding a new employee or removing an old one. I was wondering if there is a way to calculate this in one simple array formula, rather than 3 space consuming ones. Apologies for my lack of technical terminology as well, I'm still trying to get my head around nested IF statements and so this problem is probably a little beyond my scope.

(For those who don't know, bradford factors are a method of measuring the number of sickness occurrences and total absences of an employee. This is done by counting each individual absence, and the number of blocks of absences (where more than one day is taken off at a time) and then multiplying the total number of individual absences by the square of the occurrences.)

Please see the example spreadsheet below for the current system and the basic function i need this spreadsheet to have.

Thanks in advance,

Taylor

Why does row 8 only have 1 formula?

Why does row 9 formula refer to rows 11 and 10?

What 3 formula are you looking to replace?

Some meaningful data would help.

Apologies,

I don't fully understand why the formulas were set up the way they have been, but as i said, it was the previous employee before me who came up with this system, and I'm just looking to improve on it.

I believe E9 counts whether or not both C9 and D9 contain "yes", which they would return if the cell that their formulas are referencing contains the letter "s" (used to record a sickness absence). I think that C9 counts whether B11 contains "s", and D9 counts whether B10 contains an "s". I think that this is used to count when there are absences of more than one day in a row.

I basically need a formula just to calculate when there is more than one cell containing "s" in a row. Do you know of any way to do this?

Thanks again

Taylor

If you don't understand, I suggest you start from scratch and define what you want to show, from the beginning. There will only be one s per row as far as I can see, could be many per column and that can be counted with

=COUNTIF(B:B,"s")

Hi,

Please see the attached spreadsheet with notes. I am trying to find a way to count when there are consecutive sick days, and then count consecutive sick days as 1 occurrence each time. I.e. an employee marked in as sick from the 4th-7th of January would be seen as 1 occurrence, but 4 separate individual occurrences. We currently have a COUNTIF formula in place to count individual sick days, and so just need to be able to count occurrences.

I am still not absolutely sure what you are trying to determine, so it is hard to come up with a formula. It seems you want to know whether a sickness day is part of a run of sick days, or whether this is the start of a new bout of sickness. But why are you looking forward, why not work on the current row? Are you trying to determine when a bout of sickness starts or the number of sickness periods (4 within the 9 days in your example)?

BTW, the formula of =SUM(B4-B5) in B6 is wasteful, =B4-B5 suffices.

Originally Posted by Bob Phillips

I am still not absolutely sure what you are trying to determine, so it is hard to come up with a formula. It seems you want to know whether a sickness day is part of a run of sick days, or whether this is the start of a new bout of sickness. But why are you looking forward, why not work on the current row? Are you trying to determine when a bout of sickness starts or the number of sickness periods (4 within the 9 days in your example)?

Hi Bob,

That is exactly what i'm trying to determine. As I said, I'm not entirely sure why the formulas were set up like they were, but if you have any suggestions to achieve the same result without the complexities of dealing with 3 separate columns all referencing different cells, then I'd be happy to take those on board and incorporate them into this spreadsheet. We already have a COUNTIF formula to sum the total number of sick days, but we need to find how many periods or bouts of sickness there have been like you said.

Thanks for your continued help by the way, it's much appreciated.

Thanks

Taylor