Adding Tiers to a Census

Sorry for the lack of updates folks, it's been a busy week.  Going forward, I'm going to try to do at least 2 posts every week so you actually have a reason to read my blog.

When we left off last time, I was discussing a common problem with enrollment listings:  missing tiers.  You've likely seen these during open enrollment when you're selecting your benefits and looking at what you have to pay.  Most health plans are 4-tier:

• Employee Only
• Employee + Spouse
• Employee + Children
• Family

Our starting point is going to be a worst case scenario.  We've received a census with an employee ID number and the relationship to the employee, but nothing else.  Also, the members aren't already sorted with the employee in the top position for every family unit.

The method I use for adding tiers to a census works by counting the spaces between employees and seeing what the dependent types in between are, but we need to put the employee at the top of these family units in order for that to work.  Let's throw it in a pivot table (select B2:C12 and hit Alt+D, P, and F).

Drag the Employee ID field into the row box followed by the Relationship field.

Pull out the Total rows by right clicking on them on the left and selecting Hide.  Then right click on any of the Employee entries in the second column and select Order > Move to Beginning.  This will move the Employee field to the top for every Employee ID number.  This will be our new census.

Copy and paste these values to a convenient location and we can start manipulating them.  Let's start again from this point.

Add a tier column, preferably next to the relationship column.  Here is the formula we will be putting into the tier column:

=IF(C2="Employee",
IF(C3="Employee","EE Only",
IF(MATCH("Spouse",C3:C12,0)
IF(C4="Employee","E+S","Fam"),"E+CH")),"")

Pretend that is all on one line, Blogger is killing me with formatting issues...

So this is a rather inelegant solution.  I came up with it when I first started working with Excel and, coming from a light programming background, I was in love with nested If statements.  If I have some spare time, I might think of a much cleaner way of doing this, but my lunch is almost over so I'll just explain how my ugly formula works.

First it's checking to see if it is in an Employee row.  For my industry it usually simplifies the census to show it this way as opposed to putting it next to every family member.  If it is an employee, it does everything else in the formula, if not, it leaves a blank "" space.

The next step it checks is to see if the next row down contains an Employee.  If it does, and this assumes you've sorted the census correctly, that means the Employee on the current line has Employee Only coverage.  We call that EE Only in our jargon.

If the next entry is not an employee, we go to a Match function.  Match returns the first position of a value in a range.  In this example, we're checking the next 10 cells to see if a Spouse is between this Employee and the next Employee down the line.  In practice, you'll want to look 20+ cells down because the Match formula throws an error if you're looking for something that doesn't fall into it's range.  We're using the Match function in case we received a census with the Employee in the first position but the dependents scrambled; in this example we already had to sort the members to put the Employee first so we might as well have put the Spouse second and Children last which would simplify the process somewhat.

If there is no Spouse before the next Employee, we've eliminated the possibility of Employee + Spouse coverage and Family coverage, so this must be an Employee + Children.

The next step is to see if this an Employee + Spouse.  We already know this unit contains a Spouse, and if C4 is another Employee then we know this dependent unit is only 2 people, so we can assume Employee + Spouse coverage.  Anything else that made it this far through the nested Ifs would be a Family by default.

This method does have some limitations.  If we copy the formula down, we'll get errors once we get to the end of the list because we're looking for the next Employee in line and there aren't any.  This isn't that big of a deal though, just jump down to the end of your census and manually enter the tier for the last dependent unit.

This is definitely a quick and dirty solution; there are many ways to approach a problem like this and I highly doubt that the one I came up with when I knew very little about Excel is the most elegant one.  It still works though, so I haven't really taken the time to revisit it yet.  Maybe in a future post.  I'd also like the formulas to point to a table so it's easier to change the Employee, Spouse, and Child strings to match the census.

That's all for now.  It's almost Friday!