bradford factor

**Tjenkins** · 10-28-2010, 11:10 PM

I am trying to add the bradford factor to my spreadsheet
sickness days are recorded with "sick"

As you can see in the example there are three times an employee has been sick
first time for 1 day then 2 then 3 total = 3 occasions
can you help me with the formula that needs to go into the cell J1 so that it adds the occasions and gives a result of 3

Thanks for the help
TJ

**6StringJazzer** · 10-28-2010, 11:55 PM

See attached. The formula in K1 is an "array formula". You will notice it is {surrounded by brackets like this}. That means that it functions as a kind of iteration, performing the function repeatedly over a range. To create an array formula you first enter the formula, then press CTRL-SHIFT-ENTER to enter it. Excel adds the brackets for you; it doesn't work if you try to type them in.

I also completed the computation of the Bradford factor for this data.

This solution requires there to be at least one blank cell after the last sick day.

**6StringJazzer** · 10-28-2010, 11:57 PM

BTW the documentation on array formulas isn't that good, and I have never seen a really good explanation of how they work. You generally get an intuition for it after seeing about 50 examples, but I still can't even write a good explanation myself.

**beaunydal** · 10-29-2010, 12:33 AM

For what it's worth, these array formulas seem to work in xl2003...

Please Login or Register  to view this content.

if there can't be a blank cell between the formula and the rightmost row 1 cell set aside for entry of "sick" or left blank for "not sick".

Please Login or Register  to view this content.

if you are able to move the formula one cell to the right of the cells set aside for entries.

**Tjenkins** · 10-29-2010, 12:39 AM

Any reason I cant copy the formula down ?
Or why if I take one of the "sick" out it goes wrong

Thanks again
TJ

**DonkeyOte** · 10-29-2010, 03:24 AM

Traditional Frequency Array would be along the lines of:

Please Login or Register  to view this content.

Originally Posted by 6StringJazzer

BTW the documentation on array formulas isn't that good, and I have never seen a really good explanation of how they work.

I would suggest reading through Colin Legg's Tutorial - linked in my Sig (Arrays) - pretty extensive IMO.

**DonkeyOte** · 10-29-2010, 03:32 AM

Just as a follow up to beaunydal's post... if you know I1 can never be "sick" (and is not part of the precedent range) then another non array alternative would be:

Please Login or Register  to view this content.

**beaunydal** · 10-29-2010, 06:18 AM

I was not having any success with SUMPRODUCT so I switched to array.
I should have persisted with SUMPRODUCT since it seems this standard formula is OK...

Please Login or Register  to view this content.

or, if the blank column between the formula and the row of entries is used...

Please Login or Register  to view this content.

**DonkeyOte** · 10-29-2010, 06:38 AM

@beaunydal, I don't disagree - I was merely trying to illustrate that the SUMPRODUCT need only be performed if count of "sick" is 2* to n-1

If in reality the ranges are as small as implied by the sample then granted the circumvention of SUMPRODUCT is not really worthwhile ... if however the "real" ranges are much bigger then performing the SUMPRODUCTs unnecessarily is worth avoiding IMO.

* in my CHOOSE I failed to add the 1 -> 1 result - ie should be {0,1,2,8} with {0,1,SUMPRODUCT,1}