In Healthcare financial analysis, it is very important to know who your new patients are vs. your existing patients. New patients are usually defined as a patient that has been seen in the current year but has not been seen for some time period before. For example, a new patient might be a patient that was seen this year (2007) but has not been seen for three years prior (2004, 2005, and 2006). Even if the patient was seen in December 2003, he would still be considered new if seen again in 2007 as per the definition.

Given this definition, the challenge becomes "given X amount of records, how do I write a formula or create a process to count new vs. established patients?" Fortunately Excel makes this easy via the use of pivot tables. For very large amounts of records, Microsoft Access can perform the same function via crosstab queries.

For example, you're boss asks you to quicky identify and count the new vs. established patients for 2007. His definition is that a new patient is one that has been seen at least once in 2007 but not seen for at least two years prior. In other words, a new patient would be one seen in 2007 but not in 2005 or 2006. You are provided a list of medical record numbers (patient numbers), the year in which they were seen, and the charge incurred. Below is that list:

Rather than trying to build formulas to break down the new vs. established patients, first create a pivot table on the entire data set. In practice, the data would probably include millions of records so Microsoft Access and a crosstab query would be the tool of choice. Keep the medical record numbers in the first column while pivoting the years across the columns. The Excel pivot table, or Access crosstab query, would look like this:

At this point it becomes easy to identify the new and established patients. The new patients are highlighted in yellow while the established patient are highlighted in green. As per the definition, the new patients incurred charges in 2007 but not for 2005 or 2006. Conversely, the established patients incurred charges in 2007 but also incurred charges in either 2005 or 2006.