[Tutorial Tuesday]

Connection and relationships are key to a successful business. Many service-oriented companies understand that providing the same service person to customers will strengthen the relationship and ensure better customer satisfaction. Increased customer satisfaction through connection is true whether it is a plumber, landscaper, or care staff in a hospital. In today’s tutorial, we’ll look at some ways to examine turnover statistics from a hospital setting.

The tutorial will examine how you can figure these stats out in your favourite spreadsheet (Excel, Google Sheets, Libre Office.)

In our use-case, we imagine that a patient is in the hospital for seven days. Each day there are some care staff who see the patient for the first time. There are also some staff who see the patient for the last time. In our example we want to determine:

- The total number of care staff that the person in a week.
- How many new care staff the patient sees each day. New care staff are people who the patient has not seen before.
- How many care staff the patient sees for the last time each day. These are care staff who do not revisit the patient after that day.

I have done our example in Google Sheets. This should work just as well in Excel, except where I’ve added a few notes.

Let’s get started. First we will start with a list of the each of the visits that staff make. This is as simple as a date that each person came to see the patient.

The first thing we need to do is get a list of the staff who have visited the patient. We need want a list that doesn’t repeat the duplicates. In Excel, you can select the area and use the Remove Duplicates function. In Google Sheets you can use the =unique() formula.

Select a cell where you want your unique list to start. They =unique( and then select the area that you want to filter to unique items. Add your closing bracket. In our case the formula is

`=unique(b2:b23)`

When we add this formula to D2, we get a list of only the unique items.

Next, we’ll add a count of the number of visits each person made. This is done by a countif statement. We ask Excel to count the number of times the name appears in the list. The formula looks at a block of cells and counts the items that look like the name that we have stored in D@. The formula looks like this:

`=countif($B$2:$B$23, D2)`

The results are:

We’ll put a few headings on our spreadsheet to help us understand what each of the columns mean. Next, we’ll find the first and last day when each staff member visited the patient. This is done by the =minifs() and =maxifs() statements. These statements are structured as follows:

- Look at a range of values (in our case the dates.)
- Look at the criteria that are given (similar to the countif – look at the names in the range.)
- Find the min or max values for only the selected items.

The formulas look like this:

`=minifs($A$2:$A$23, $B$2:$B$23,D2)`

or

`=maxifs($A$2:$A$23, $B$2:$B$23, D2)`

Because minifs() and maxifs() deals with numbers, you may see the results as numbers. You need to format the cells back to a date format to see the earliest and latest date that the staff visited a patient.

There is lots of information that you can get from this data so far. You can find the average number of days staff visited the patient. You can find out the number of visits per day, and much more. But we aren’t done yet.

Next, we are going to find out how many new visitors the patient had every day. And we are going to find out how many visitors the patient never saw again after that date.

We need to start by coming up with a unique list of dates. We’ll use the =unique() formula again. This time we will point it at the column with the dates.

`=unique(A2:A23)`

Then, based on the first-day and last-day entries that we just determined, we can find out how many people had a first-day or the last day on the date in question.

The formula is a =countif() formula again. Count the date in the first-day column if it equals the date for the row that you are on (e.g. March 17.) The same thing is done for the last-day column.

First-Time Visits:

`=countif(F$2:F$6, $D9)`

Last-time visits:

`=countif(G$2:G$6, $D9)`

And there you have your stats for a single patient in a week. You can use a similar approach if you measure your service staff per month or year. Or if you are trying to figure out how many different faces a client sees when they walk in the front door of your accounting firm. All of this data might be very helpful to you in understanding how you are building (or not) good relationships with your customers.

An Excel version of example can be downloaded here so that you can play with the formulas and try it out for yourself.

2019-03-26 Tutorial Tuesday.xlsx

Leave a comment below about what works or doesn’t in this sheet.