welshcuriosity
New Member
- Joined
- Sep 14, 2015
- Messages
- 2
Hello,
I've been asked by my manager in work to see if I can adapt a spreadsheet to work out people's Bradford Factor (a way of monitoring people's sickness https://en.wikipedia.org/wiki/Bradford_Factor)
At the moment, they are using a spreadsheet with people's names in column A (starting at A2), and then on each person's row they use different coloured cells to monitor different absent reasons (one colour for holidays, one for sickness etc). A1 onwards lists the dates.
A very crude example is below - (red) is a red coloured cell, there is no text in the cell:
<tbody>
</tbody>
I need to work out 2 things from the data:
How many total days have been coloured red,
How many instances of red days (number of continuous red squares)
Using the table above, it would work out as:
Alice:
Total days = 3 (1.5 days worth in the first continuous red section, half a day on 04/01/15 AM, and 1 whole day from 29/03/15 PM to 30/03/15 AM. A dirty way to work out would be the total number of red cells / 2)
Instances = 3 (1 instance from 01/01/15 AM to 02/01/15 AM, 1 instance on 04/01/15 AM, and 1 instance from 29/03/15 PM to 30/03/15 AM)
Bradford Factor = 27 (3 instances squared x 3 days = 9 x 3)
Bob:
Total days = 2
Instances = 2
Bradford Factor = 8 (2 instances squared x 2 days = 4 x 2)
I've been able to work out how to count the total number of coloured squares using a function from https://support.microsoft.com/en-gb/kb/2815384 so I can work out half of the Bradford Factor data, but that's where I'm getting stuck.
Does anyone know how to count the number of instances in a given range?
I've been asked by my manager in work to see if I can adapt a spreadsheet to work out people's Bradford Factor (a way of monitoring people's sickness https://en.wikipedia.org/wiki/Bradford_Factor)
At the moment, they are using a spreadsheet with people's names in column A (starting at A2), and then on each person's row they use different coloured cells to monitor different absent reasons (one colour for holidays, one for sickness etc). A1 onwards lists the dates.
A very crude example is below - (red) is a red coloured cell, there is no text in the cell:
01/01/15 AM | 01/01/15 PM | 02/01/15 AM | 02/01/15 PM | 03/01/15 AM | 03/01/15 PM | 04/01/15 AM | 04/01/15 PM | ... | ... | 29/03/15 AM | 29/03/15 PM | 30/03/15 AM | 30/03/15 PM | |
Alice | (red) | (red) | (red) | (red) | (red) | (red) | ||||||||
Bob | (red) | (red) | (red) | (red) |
<tbody>
</tbody>
I need to work out 2 things from the data:
How many total days have been coloured red,
How many instances of red days (number of continuous red squares)
Using the table above, it would work out as:
Alice:
Total days = 3 (1.5 days worth in the first continuous red section, half a day on 04/01/15 AM, and 1 whole day from 29/03/15 PM to 30/03/15 AM. A dirty way to work out would be the total number of red cells / 2)
Instances = 3 (1 instance from 01/01/15 AM to 02/01/15 AM, 1 instance on 04/01/15 AM, and 1 instance from 29/03/15 PM to 30/03/15 AM)
Bradford Factor = 27 (3 instances squared x 3 days = 9 x 3)
Bob:
Total days = 2
Instances = 2
Bradford Factor = 8 (2 instances squared x 2 days = 4 x 2)
I've been able to work out how to count the total number of coloured squares using a function from https://support.microsoft.com/en-gb/kb/2815384 so I can work out half of the Bradford Factor data, but that's where I'm getting stuck.
Does anyone know how to count the number of instances in a given range?