Bradford Points automatic counter

Hi,
I am trying to build a spreadsheet that records staff sickness. I have made a spreadsheet (example supplied) that captures days off and a column at the end that will conditionally format the colours to display the current level, eg.. green, amber, red in a cell dependant on points accrued. Hope this is making sense??
I can send an example of the spreadsheet if anyone can assist.

Thanks everyone!!

It sounds straight-forward. I don't see your example.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Hi 63falcondude,
I have never attached a file to this forum before as I am new to this and excel, hopefully this has worked.

Thanks for your assistance!

Tony

You're sample sheet is not clear.

Are the desired results based on this sample shown in rows 8, 9, and 10 (specifically in cells S8, U9, and U10)?

If so, how do you get 3x3x3, 5x5x5, and 7x7x7 for AM4, AM5, and AM6 respectively?

Keep in mind that I do not know what a Bradford score is.

Hiya,
Bradford points are based like this:

B is the Bradford Factor score
S is the total number of spells (instances) of absence of an individual over a set period
D is the total number of days of absence of that individual over the same set period[2]

The 'set period' is typically set as a rolling 52-week period.

For example, this is how 10 days absence could be shown:
1 instance of absence with a duration of ten days (1 × 1 × 10) = 10 points
3 instances of absence; one of one, one of three and one of six days (3 × 3 × 10) = 90 points
5 instances of absence; each of two days (5 × 5 × 10) = 250 points
10 instances of absence; each of one day (10 × 10 × 10) = 1000 points

I know this sounds complicated but I am just trying to get Excel to add the points up into cell AM04 based on this criteria. Maybe I am looking at it completely wrong, I`m not sure as I say I am just a novice to Excel.
Sorry!!

Best regards,
Tony

I wasn't asking for an explanation of Bradford Scores but thank you for taking the time to explain them to me anyway.

Can you now explain why (based on your example from post #3) AM4 should be 3x3x3? Where do the 3's come from as the only thing in F4:AI4 is 1.0 in cell H4.

I have the same questions for why AM5 should be 5x5x5 and why AM6 should be 7x7x7.

This was just an explanation - not an example. Sorry if this is confusing, I was just trying to explain the criteria. I guess I want excel to work out the Bradford formula (based on the Bradford system)and drop it into cell AM4 (if that makes sense?)
I am starting to confuse myself now lol

Please go back and re-read post #2.

Please create a representative sample of your data along with the desired results of the formula (manually entered) based on that data.

Hi shg,
This is EXACTLY what I was getting at, WOW you soon figured this out.
The only other question I would ask,is how will excel know how many days per occasion are taken? how can I capture this? do you know what I mean?
Thanks a million,

Best regards,
Tony