Thursday, July 17, 2008

Using the =countif Function for Staff Schedules

As a business grows the ability to move staff to meet demands becomes critical to keeping staff costs low and customer satisfaction staff. Each year we create a new Excel spreadsheet to schedule staff. Each sheet is a month and one sheet at the end counts the total days for each staff member. Within each month we summarize the number of staff per office per day (number of nurses, doctors, assistants).

The Excel book for scheduling was our offices' first quality improvement process. Prior to it's use we where reluctant to reschedule staff because of the difficulty of re-making the schedule (Word document). Since the implementation of the Excel workbook we can change it on the fly, the countif function (see picture below) tallies assigned staff by day, week, office and month. The workbook is also automatically published for staff to see on the intranet.

Countif is a function that counts a cell if it meets a criteria =countif(A1:B5, "c*") counts any cell in the range that starts with the letter C. We've also thought about using a database and pivot tables for the same function but in this case simple seems to be better and more functional.

No comments: