Formula to count occurences of sickness

[David Kenney]

Hi all,

Hope you can help - I have limited excel knowledge and I'm stumped with a request from my boss:

I have a stsff sickness/leave monitor spreadsheet.

It has a summary worksheet, and 12 monthly sheets.
On each monthly sheet I have staff names listed in column B (B4 to B40), with dates in row 3 (Example January runs from C3 to AF3). In each row I currently enter a 1 for a full day holiday, 0.5 for half a day holiday and "S" for a days sickness. Sums for holidays and sickness are shown in the summary sheet.

I need to show my boss occurences of sickness for each member of staff (so if someone has a day off during the week, then another the following week - that ciounts as two. If someone has a friday off, then a monday - that counts as one).

I hope this makes sense!


Submitted via EggHeadCafe - Software Developer Portal of Choice
Command Prompt Here with VS.NET Environment
http://www.eggheadcafe.com/tutorial...9d-98f34bef0a9a/command-prompt-here-with.aspx

 

[ker_01]

For formula-specific questions, consider posting to
microsoft.public.Excel.Worksheetfunctions

If I were setting this up from scratch, I would put in an 's' for the first
day of any sick leave (marking the instance), and each subsequent day with a
different letter showing that the leave extended across multiple days. That
would make the formulas even easier. However,

For each row, what you are really worried about is how many cells have a
value of "s" where the preceeding cell was /not/ "s"

for your first employee in row 4: find a cell and enter:
=SUMPRODUCT((C3:AF3="s")*1,(B3:AE3<>"s")*1)

Or paste this into the "Jan" cell in your summary worksheet for this
employee, then select the cell references (e.g. C3:AF3) and then go select
the same range on your Jan spreadsheet so that it gets the full page
reference.

This should give you your count of sick occurences. Note that this formula
cannot be entered in the range of B3:AF3 because that would create a circular
reference.

Important note: this will NOT guarantee an accurate count, due to the
workbook design. If you have an employee who is sick on Jan 31 and that
sickness lasts two days, this method will show a new instance on Feb 1
because the months are broken out across worksheets. Suggested fix: Add a new
column in C3 of each monthly worksheet. Set that cell value equal to the last
day of the month on the previous month's spreadsheet (e.g. the Jan 31 "s"
will now also show in C3 of the Feb sheet, and the Feb data will be in
D3:AG3). Adjust the formula above by moving both ranges to the right one cell:

=SUMPRODUCT((D3:AG3="s")*1,(C3:AF3<>"s")*1)

and that should give you an accurate count of sick instances, including
those that bridge across months.

HTH,
Keith