David @ Work

No New Posts Until Summer

2010-02-03T09:49:00.000-08:00

This is an update about the lack of updates. Sorry to disappoint, I know you saw a new post from David @ Work pop up in your RSS reader and got hot and bothered for some Excel knowledge.

Much to my surprise, people more important than me became aware of my modest Excel and design skills and decided to put them to good use. If this sounds like something that you'd like, I assure you it isn't, but if you're compelled to put your own hand on the stove just leave a bunch of Edward Tufte and Stephen Few books sitting around your desk. I'm pretty sure that's what gave me away.

Back to the point, I now have the task of redesigning nearly every single template that our analytics department uses to be cleaner, more concise, more flexible, more informative, more intuitive, and more aesthetically pleasing. I know enough to fully comprehend the terrifying scope of this task, but not enough to competently complete it. I guess I should probably read all of the books I have sitting around my desk.

So for the next several months I'll be up to my eyeballs with this stuff. All of our clients are different, so we have to account for plans without vision, without dental, with multiple plan designs at multiple locations, with HRAs, with only a few years of experience, and--most problematically--without 12 month plan years. There go all of my mod 12 formulas...

What does this mean for the blog? I won't be updating it. I'm already thinking about the redesign project every waking minute and I'm spending all of my free time reading non-fiction books. I don't have the time or the enthusiasm to keep writing about Excel on top of all of that.

In all seriousness though, this is an exciting opportunity. The transition might be a ton of work, but once the project is done we will have, unquestionably, the sexiest charts in the entire industry. That's my motivation. Because of the nature of our work, the goal of our analytical exhibits isn't just to tell a story with the data, but also to market ourselves to existing and potential clients and reveal our competitors for what they are: amateurs. To paraphrase Batman, this isn't a spreadsheet, it's an operating table. And I am the surgeon.

I do plan to revisit the blog when my life permits. I have an article in my head about creating Conway's Game of Life in Excel using circular references that needs to be written at some point.

Charts that Suck

2009-12-30T14:17:00.000-08:00

Yes, I know every excel blog has a feature like this, but I'm not feeling inspired today and I need some filler. Also this chart just plain irritated me.

Problems I have with this chart:

It's 3d.
The bars are all different colors.
It's using the default Excel palette. Not only is everyone bored with it by now, it was ugly to begin with.
It's 3d.
The key is an inefficient way to label this type of chart.
I don't think these are "relative prices". Just prices would be fine.
The bars are touching! Data needs to breathe.
Vertical text.
It's 3d.
The "Liquids" label on the axis wouldn't be necessary if the chart was labeled correctly in the first place.

Things that I appreciate about this chart:

The data is ordered in a useful way.
A bar chart is the correct graph for the data, as opposed to a line graph, scatter plot, or pie chart.
No wacky textures or other irritating effects were added.

Let's fix it. First of all, change the color palette. I could write a book about proper color palette use in Excel. It wouldn't be terribly informative or correct, and I'd probably end up anthropomorphizing the colors and turning it into trashy cyberpunk pulp starring the color fuschia, but I could write it. I'll put a post together about it in the distant future. For now, here are the rules for a good color palette (I'm not intentionally making this post a series of numbered lists, it's just kind of turning out that way):

White.
Black.
5-10 shades of gray, ranging from very light to very dark.
A strong red, something a little deeper than just the 255 red (for "bad" or negative numbers).
Several monochromatic color series for ordered data. Generally, lighter is better because you can use it for backgrounds as well. I like blues and greens for this.
A series of complementary colors for unrelated data. Think pie chart. I like autumn colors or colors that remind me of food for this.

Here is my current color palette. You can see that I've broken some of my own rules, but this is a work in progress. I'm not happy with the red series and I need more gray.

Because this is a chart about money I'm going to use a green*. That's probably more thought than you need to put into something like this, but anything worth doing is worth doing right. I'm just getting into all of the psychology behind color, and I'd recommend Pantone Guide to Communicating with Color as a good place to start.

The background of the chart should be very light gray or white, grid lines and borders should be gray if you want them, the actual bars should not have borders.

As far as the fonts, I like Georgia for text, Tahoma for numbers. You might have heard that you should change the font based on whether it will be read on paper or electronically as Serifs are supposed to be easier to read in print and Sans Serifs are supposed to be easier to read on a computer screen. This is hogwash and hasn't been supported by scientific study; Google it if you want. Regardless of medium, Georgia is a damn fine font, though the descending numbers make it unsuitable for numbers by themselves so that is why I also use Tahoma. I usually use 8 point font for labels, 10 point font for titles, but the scale really depends on where you're going to put it. As a rule though, titles should be about 2 points bigger than the rest of the text for most applications.

I changed the chart to a horizontal bar chart so that the long category names would fit easily and we wouldn't have to use a key. The bars are also a little too skinny by default. The chart reflects the data, so if you want your data to look solid you'd better use bars that don't look like they'd snap off in a stiff breeze. I changed the gap width to 70.

The repaired chart:

Note to self: stop leaving the mouse cursor in screen shots. That's all for today. In unrelated news, my girlfriend is going to buy me office 2010 for Christmas. I'm a little scared to leave the comfort of 2003 behind, but it will be nice to have my own Excel license if I ever want to start freelancing. Next Christmas I want Tableau.

-David

* I realize that not all money is green and that not all of my readers are from the United States, but U.S. currency is the most recognized currency in the world according to an unscientific poll I conducted that technically has the largest sampling bias possible.

Enhance Excel's Autosave with a File Syncing Service

2009-12-17T13:28:00.000-08:00

I love this tip because I invented it. I suppose other people might do the same thing, but it would be a parallel evolution of ideas as opposed to me looking at what other Excel blogs were writing about a year ago, adding swear words, and calling it an original post.

If you use Excel extensively, you know that it has the potential to lock up or crash. A lot. This mostly happens to me when I'm using formula auditing, but I've also been the victim of unprovoked crashes on multiple occasions. Losing over an hour of work is pretty much a day-ending and possibly career-ending event for me as my only tools for coping with adversity are random, shocking violence and destruction of property, so I go to great lengths to make sure this never happens.

Fortunately, Excel has an autosave feature. Unfortunately, this feature sucks. Depending on how Excel crashed and how you restarted it, you may or may not be given the list of recoverable documents. Also, despite setting the autosave interval as low as 10 minutes, you'll have inexplicable gaps in coverage. Browsing to the directory where autosaves are kept can help, but Excel seems to clean this directory out pretty regularly.

What is one to do? Well, you should be backing up the autosave directory remotely and instantly, giving you access to every version of your Excel documents going back as far as your storage space will allow. There are a number of programs that do this, many of them free. A lot of people like to use Drop Box for file syncing, but Drop Box only backs up one folder unless you hack it with odd shortcuts. I prefer SugarSync. SugarSync can back up or sync any folder you want either with your online account only or between multiple computers. SugarSync is free to sign up for, but if you do it with this link I get a free storage upgrade. Please?

I'm not going to go into the details of how to set up SugarSync, suffice to say it isn't brain science. Once you do have it set up, you need to locate your autosave directory. In Excel, go to Tools > Options > Save.

The AutoRecover save location is the directory you want. Keep this path handy, as SugarSync forces you to browse to the location instead of just pasting it in. Also, make sure that autosave is enabled with a time interval that you're comfortable with.

Now in SugarSync go to Manage Sync Folders and click on Add Folders from this Computer. From there, browse to the backup folder, usually C:\Documents and Settings\Your User Name\Application Data\Microsoft\Excel\, and check the box next to it, then hit okay. Congratulations, you'll never lose more than x minutes of work in Excel ever again.

You may notice though that this folder is pretty empty. It actually syncs up with the local folder, so it's deleting files at the same time Excel is. It hangs on to all of these files in a deleted files folder though, so you should have every version going back quite a ways. I think SugarSync hangs onto the files until you run out of space; I have files going back to mid-November when I last cleaned it out.

The bad thing about this system? Well for starters you can't tell what the file is for sure until you open it because Excel assigns it a gibberish name. If you have a rough idea of the size of the workbook and when you were last working on the version you want though, you shouldn't have too hard of a time. The other problem is that really large files that you're working on over the course of the day will back up dozens of times and that can eat into your free space pretty quickly. You could always register for more space, which brings me to the last negative: SugarSync's business model may not be profitable, and they might not be around forever. I'm sure they'll give you advance notice if the service is going down though so you don't lose all of your data, and there will always be some other free option out there to replace it, so this shouldn't be a big deal.

The great thing about this system? It isn't just protecting you from crashes, it's protecting you from yourself. Have you ever started working on an old workbook as a template but then hit save instead of save as, unintentionally destroying the original? Have you ever screwed up a workbook so completely that you need to go back in time and start over? Have you ever had a workbook out on your network mysteriously vanish? Have you ever just plain forgotten to save a file? If you haven't, it's probably because you're young and your brain hasn't been ravaged by alcohol, drugs, reality television, and old age. Your probably aren't even burnt out on your job yet! Ah, sweet ignorance... but for the rest of us, this is a great back up plan and it's saved my bacon more than I care to admit.

Also, once you have SugarSync up and running, you can go crazy synchronizing mp3s, your personal macro workbook, plug-ins, and all sorts of other stuff between your work and home computers. It'll be like you never even left the office!

-David

Secret Santa Worksheet

2009-12-11T18:02:00.000-08:00

I made a worksheet for everyone who wants to do a Secret Santa gift exchange but doesn't own a hat. You type in a list of names (the gift givers), click a button, and get a new list of names (the gift receivers) randomly shuffled with no name in its previous position. I almost decided to sell this, but several things changed my mind:

My VBA coding skills are actually horrible and I'd be ashamed to profit from them.
It would almost certainly complicate my taxes which I can barely do as it is.
I couldn't find any terms of use for the Santa image but I was too lazy to find a replacement.
The only people who would buy it would be people who read my blog, and I love you all too much to let money come between us.

So, uh, happy holidays. I hope it fits. What? You want to know how it works? Sigh...

Okay so my stupid worksheet looks like this:

Oh look, it's being ironically festive! Maybe it should move to Capitol Hill and read the Stranger on its iPhone while wearing jeans that are way too small! If you don't live in Seattle you have no idea what I'm talking about!

Anyway, let me point out the nicer features:

Images
I grabbed that Santa from a Secret Santa excel template that Microsoft hosts. Their template sucks though because it doesn't shuffle the names for you, so don't bother looking for it. I googled "free holiday clip art" to get those other two images.

Dynamic Named Ranges
Dynamic named ranges are awesome. I think I first read about them on the PHD Blog. Anyway, there are three in this worksheet, but they all look like this, which points to the gift givers column:

=OFFSET('Secret Santa Name Picker'!$C$3,0,0,COUNTA('Secret Santa Name Picker'!E:E)-1,1)

So it's just counting how many entries there are in all of column C, subtracting out 1 for the column title, and creating a range with that height starting at cell C3. I guess I should briefly explain the offset function. It returns a cell or range reference, and the arguments are as follows:

=OFFSET(starting cell, cells moved left (negative for right), cells moved down (negative for up), range height, range width)

So my formula starts at cell C3, doesn't offset because that is the column I want, and has a height equal to the entries in the column (less the title) and a width of one. Now the name, Givers, will always point to all of the names in column C, no matter how many I enter. Don't try to be clever and break it by leaving blank spaces in the middle of the list, and yes that will break it. The other ranges are Receivers (self explanatory) and Dummy (which refers to me for having to add a "helper" column because I don't know how to use data types in VBA).

Button

A button! I really have no idea how this works. I cannibalized two pieces of code, mashed them together, and ran through the debugger about a million times tweaking things until it finally worked. The basic idea: I take the input range, Givers, and copy its entries to the Receivers range. At the same time I populate the Dummy range with random numbers. I then sort both Receivers and Dummy by the values in Dummy, effectively randomizing the order of the names. Finally, I run back through both Givers and Receivers making sure none of the entries match (if your family allows people to draw their own name in Secret Santa, you're doing it wrong). If the entries do match, it goes back through the array and switches it with the first value that won't cause either entry to match the Givers range.

Here it is:

Private Sub CommandButton1_Click()
    Dim xTemp As Double
    Dim yTemp As String
    Dim i As Long
    Dim j As Long
    Dim vArr As Range
    Dim l As Integer
    Dim moreDifferentArray As Range
    Dim returnArray As Range

    l = Range("arraysize")
    Set vArr = Range("Givers")
    Set returnArray = Range("Receivers")
    Set moreDifferentArray = Range("Dummy")
     'pass range values to array

    'add randomness
    For i = 1 To l
        returnArray(i) = vArr(i)
        moreDifferentArray(i) = Rnd()
    Next i

    'sort I think. Forgot where this came from, sorry...
    For j = 2 To l
        xTemp = moreDifferentArray(j)
        yTemp = returnArray(j)
            For i = j - 1 To 1 Step -1
                If (moreDifferentArray(i) <= xTemp) Then GoTo 10
                moreDifferentArray(i + 1) = moreDifferentArray(i)
                returnArray(i + 1) = returnArray(i)
            Next i
        i = 0
10      moreDifferentArray(i + 1) = xTemp
        returnArray(i + 1) = yTemp
    Next j


     'check for dupes
     For i = 1 To l
        If vArr(i) = returnArray(i) Then
            For j = 1 To l
                If vArr(j) <> returnArray(i) Then
                    yTemp = returnArray(i)
                    returnArray(i) = returnArray(j)
                    returnArray(j) = yTemp
                    j = l
                End If
            Next j
        End If
    Next i

End Sub

That's about all there is to it. You can download the workbook here. Remember to enable macros or it won't work. Happy holidays!

-David

I just now turned ClearType on after scorching my retinas for years

2009-12-03T15:36:00.000-08:00

Still alive, just really busy. Most insurance plans renew January first, so 'tis the season.

Here is a quick tip that you probably already know about, but I'm an idiot and I just found out about it a week ago. There is a setting in Windows XP called ClearType that makes your fonts render more smoothly. Predictably, this is turned off by default.

I didn't know I needed glasses until I started living 1/3 of my life inside of Excel. It isn't easy on the eyes, so you have to take care of yourself. This setting actually makes a huge difference. It should be on by default in Windows 7, if you have Vista you've already gouged your eyes out so it doesn't matter, so here is how to turn it on in XP:

1. Go to Display in the Control Panel.
2. Select the Appearance tab, then Effects.
3. Check the box for "Use the following method to smooth edges of screen fonts", then select ClearType from the drop down list.
4. Hit okay, apply, etc.

Just remember, it won't make your pie charts look any better.

Parsing Names

2009-11-10T13:02:00.000-08:00

I'm thankful for the coworkers who ask me to do odd-jobs in Excel. If it wasn't for them, I'd never have anything to blog about.

Today's post is about parsing text, and specifically about turning a full name into a first, middle, and last name. Let's start with a list of names. Oh look, an obscure 80s television reference!

So we want to change these into first, middle, and last names. How hard is that? Select the names, go to Data > Text to Columns, select space delimited, finish the wizard, and Bob's your uncle. Is that a correct use of an apostrophe? A contraction of Bob is? I think so.

Anyway, this doesn't work so well because some people have four names. The census I was using in reality had a person with five different words in their full name. Obviously these people are trouble makers and deserve to have their benefits revoked, but let's be the bigger person and try to work with them.

First off, a few things:

Some people have small words that are part of their last name (Van, De La, etc.). Because people can also have short middle names, we can't just pull these out by checking the length. We'd have to keep a list of popular last-name articles and hope nobody's middle name was Van. But we don't want to do this because...
I don't know anything about the naming conventions of other countries/languages. Would Chow Yun Fat consider Yun a middle name, or is it part of his last name? I don't know! So...
We're going to have to consider the first chunk of letters the first name and the last chunk of letters the last name, and just throw everything else into the middle name and let someone else sort it out.

Now that we've defined our task, the solution is relatively straight forward. To grab the first name, we're going to use the LEFT formula to grab the text from the left side of the name and the FIND formula to find the first space, or where we want to stop.

LEFT takes 2 arguments: the word you want to chop and how many characters you want counting in from the left side of the word. FIND is going to look for the string we specify, in this case a space (" "), and return the position of the first occurrence of that string in the text that we are searching. Because we don't want to include the space, we'll subtract 1 from FIND's result.

The last name is not as easy. I spent a good 2 minutes thinking about it, decided that there must be a function in Excel that reverses text, and went online to look for it. No need to re-invent the wheel, there are probably only 5 people in the world that are actually doing new things in Excel and incidentally they all have very nice Excel blogs.

Instead of just a reverse text function, I found a question about and answer to the exact same problem I was having. As a bonus, the link does include the code for a user-defined function called REVERSETEXT that was apparently written by the infamous J. Walkenbach. I think he is a professional banjo player or something. I'm going to use the ugly solution though, as originally posted by Aladin Akyurek at that same link:

=RIGHT(D2,LEN(D2)-SEARCH("@",SUBSTITUTE(D2," ","@",LEN(D2)-LEN(SUBSTITUTE(D2," ","")))))

What is this code doing? Well the end of my lunch is fast approaching so some of this may be less than accurate, but here goes. The first thing it's doing is counting the number of spaces in the string:

LEN(D2)-LEN(SUBSTITUTE(D2," ",""))

By deleting the spaces (John Doe = JohnDoe) and seeing how much the length of the string (returned by LEN) changes, we can find the number of spaces in the name.

We use this number for the second SUBSTITUTE function. This function is substituting the last space in the name with an @ symbol. The SUBSTITUTE function first takes the original string as an argument, then the text you want to change, what you want that text to change to, and, optionally, which instance of that old text you want to change. So, for example, the formula SUBSTITUTE("Ba Ba","Ba","Da Bing",2) will return the string "Ba Da Bing", as it is only changing the second occurrence of "Ba". We counted the number of spaces in the full name and are using that as the instance, so only the last space will become an @ symbol.

After that, we're just using the SEARCH function to find the position of the @ symbol in the array. We subtract that position from the length of the full name, which will give us the number of characters after the last space in the string. We can then pass this number to the RIGHT function to count in that many characters from the right side of the string and pull those out.

The next part is easy. For the middle name, we're just going to use the MID formula and subtract the lengths of the first name and last name, then TRIM any extra spaces.

The MID formula returns sections of text. You pass it the text you want to cut, where you want to start cutting, and how many letters you want to take. We're telling it to cut the full name, start at the character after the length of the first name, which will be the first space, and then telling it to take as many characters as the full name has less the characters we already pulled out for the first and last names. Since this will include the spaces, we're also using the TRIM formula.

The nice thing about this method is that it works fine for people with and without a middle name. Where doesn't it work? People with only one name. People like Sting. Or Bono. Or Prince. As you can see, people with only one name are generally pompous, self-important rock stars. We don't shouldn't care whether or not they get correctly registered for their life insurance benefit or whatever else you might be using your census for. If you really wanted to though, you can fix that by catching errors using the IF and ISERROR functions.

-David

Add Scroll Bars to a Chart, Blog Stats

2009-11-04T18:26:00.000-08:00

I got asked to do a random project at work looking at paid claims for a large claimant before and after a certain date, so I created a chart with a scroll bar that presents that data in a slightly more interesting way. I'll get to that in a second, but first, how about some fun blog stats?

Posts: 12
Page Impressions: 288
Ad Clicks: 1
Comments: 1*
Google Adsense Earnings: $0.14
Amazon Associate Program Earnings: $0.00
Incidents of Paranormal Activity: 2

So I need to post a lot more, people are still reading my blog, my posts are so thorough that no one feels the need to comment, all of my readers are probably running AdBlock, and financially my blog is a runaway success. Now when people ask me if writing an Excel blog is rewarding, I can answer, "Yes, soul-crushingly so."

Enough fun, let's get down to business. Here is the data set we're going to start with:

My task was to sum up all of the claims before and after a certain point, so the first thing I want is a column that shows claims paid to date, or cumulative claims. In cell C2, type in the formula:

=sum(b$2:b2)

This formula sums the values from B2 down to the row of the formula, so copying it down will give us cumulative claims.

I'm also going to create a few cells for some markers and the actual scroll bar value. Feel free to put these anywhere on the worksheet. Seriously, go nuts. I like putting everything in column Z so that it's harder for people to peer review my work (which is already a waste of their time because I never make mistakes).

The Scroll Bar Value will be the cell that the scroll bar object is linked to later, while the Month Markers are just so I can put data labels in the middle of the month but have my tick marks on the ends of the month.

Add these three series to an XY-Chart as follows:

Because I chose funny values, the chart should be completely unreadable, but we're going to change the scale later so that doesn't matter too much. Double click the data point at $1 billion (Scroll Bar Value) and add an error bar to it. This should be a negative error bar only at -100%. We don't need the point at all, we're just looking for a vertical line.

After that, double click on the error bar (the vertical line below your data point) and format it in a color that stands out. I like red. Also, take the 'T' off of the end of your marker.

(Just a note about my color palette: I hate it. I like most of it, but I never use those purples. I'm tempted to add more gray or maybe some beige and off-white colors for table backgrounds, but I haven't gotten around to it yet.)

Now we can fix the scale of the chart. It looks like our dates run from January - May of 2009. Double click on your x-axis, make sure the number is formatted as a date, and then simply type in 1/1/2009 as the minimum and 5/31/2009 (there are 31 days in May, right? I hate months...), with a major unit of 30.

This should give us a nicely formatted axis:

Now double click on the x-axis again, go to patterns, and turn off the tick mark labels. We're instead going to label the mid-points, which are those little grey triangles above. To do this, you'll need an XY-Chart labeler, which I've previously discussed here. Go to the dates for your Month Marker series and format them as Custom > "mmm". After that, right click on your chart, go to JWalk Chart Tools, and select the data labels tab. From there you can link the Month Marker series to the cells we just reformatted like so:

After that, double click on your shiny new data labels, go to the alignment tab, and change the label position to "Below". After that, double click on the actual data points, set the marker to "None", and the x-axis is done.

Now we just need to take care of that y-axis. It looks like our claims top out at under $18,000, so let's set the maximum for our y-axis scale to $20,000 with major increments of $4,000. After that, double click on your Cumulative Claims Series and add a line to it on the patterns tab. Now our chart is looking pretty good.

Now let's create the scroll bar. Go to the control toolbar (add it under the view menu if you don't have it), click on the Scroll Bar button, and drag out a horizontal scroll bar under your chart. You may want to turn on snap-to-grid if you'd prefer more control over the size of the bar.

Ack, tool tips in my screenshots! The next thing we're going to have to do is link the bar to a cell and give it a minimum and maximum. For the min and max, we're going to want to use the VALUE function on the first date and last date in our series. The properties of the scroll bar won't except the date format. I just used MIN and MAX because I don't trust my data not to move around, but if the data isn't sorted your line is going to look like small intestines or some other, less disgusting thing that is squiggly.

Now let's go into the scroll bar and set some values. Right click on the scroll bar and select "Properties". We have the numeric values for the beginning and end date, and we just want it to link to the same cell our Scroll Bar Value data series is linked to (Z2 for me). Your properties should look something like this:

You may have to initialize the value of the linked cell to a value between the min and the max, mine was acting a little wonky. If everything worked out, you should have a scroll bar that moves a line around your chart. Remember to turn off design mode (on the control toolbar) to interact with the scroll bar instead of just throwing it around the sheet.

I threw some formulas under the scroll bar to give some additional data:

The named range "scrollbarvalue" is pointing to cell Z2, the cell the scroll bar is linked to. Dates are the dates in column A, and paidclaims are the claim amounts in column B (not the cumulative claims). Cell C26 is the last value in the cumulative claims column, or the total paid claims for this individual over this period. The SUMIF formula is simply adding up all of the paid claims for the dates that are strictly greater than the scroll bar value, N24 is the remaining claims, and column O is showing the percentage of total claims paid. It looks like this in practice:

There you have it, a functional chart template that admittedly has few useful applications, but scroll bars will impress the people in your office that actually allow macros in Excel (actual email: "Is the scroll bar supposed to do something? Mine just looks like a little picture?!" Sigh...). I'll try to put the workbook up later, I don't have my drop.io password handy at work.

Questions? Comments? Let me know in the, uh, comments.

-David

* My lone comment is actually from Jon Peltier, so I consider this a win.

Adding Tiers to a Census

2009-10-29T12:59:00.000-07:00

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!

3 Common Census Problems and How to Fix Them

2009-10-22T21:04:00.000-07:00

I found some free time, so here is a post about census manipulation.

In the insurance industry, you deal with censuses... er, censi... let's call them enrollment listings. You deal with enrollment listings a great deal. Insurance carriers need to know how old everyone is, their gender, enrollment tier, zip code, and benefit elections in order to properly underwrite the case. I myself use them for setting COBRA rates, modeling plan design changes, and other mathematical sleight of hand.

The problem is that when you work on self funded groups, they usually use an administrator that is a separate entity from the insurance carrier. As far as the client is concerned, the administrator is only processing the claims. Other features like response time, quality of reporting, case management notes, those are the consultant's problem and they are always a problem.

When looking at a list of proposals from administrators, every client naturally chooses the cheapest option so every report that we deal with is the worst report ever from the most bare bones administrators that we'll tolerate working with. For example, even if I've asked for an enrollment listing 20 times I still have to specify every individual field I want and it's still wrong and no two listings ever look the same. I would love to just pass this problem onto the carriers when we don't need the census ourselves, but 12 out of 10 times (most proposal requests go to a shared email address like proposals@evilcorporation.com, so I hear about every problem way more than a fair universe would allow) they just send the census back to us with an email much like the following:

Hi David,

I can't use this census. Can you fix it?

Thanks!!!!!

-Jeffrey Out

There are usually several emails after this where I force Jeffrey to tell me what he can't use and how he needs it fixed, but you get the idea. Just remind yourself how much you're getting paid to deal with these kinds of people and pour a shot of whiskey into your mocha; violence isn't the answer.

The fixing is what actually ties this post into Excel. Here are the top 3 enrollment listing problems I come across:

Valus for some fields only appear once because the census was created in a pivot table and they need it on every line.
There is some problem with the date/age format.
There are no tiers.

The first one is a pretty easy problem to fix. Imagine this:

The problem is the "Plan" field. You need the value for the plan to show up on the line for every member on that plan, not just the top one. Now imagine there are 18 different plans for 870 different members so you can't just fill the values down. What is one to do?

Now there may be in fact a better way to do this, and if there is I would love to know it, but this is how I taught myself to solve this problem. In the first blank field, set it equal to the cell directly above it that has the value, in this case B3.

After doing this, copy the formula. Then select the entire range of cells that we need to fill in the blanks for, and in the menu select Edit > Go To, then click the Special button.

From the Special window, select the option button for Blanks and hit okay.

This will select only the blank cells in the area you had previously selected. Now, just paste in the formula and all of the cells will equal the proceeding cell, filling your Plan values down for every appropriate member. Now simply click the letter B above to select the whole column, hit CTRL+C to copy it, and then hit ALT+E, S, V to paste the column back in as values so it won't get scrambled during sorting. If you don't know all of the keyboard shortcuts for every paste special option, you should learn them. It will save you a million headaches. If I could only recommend 3 things that everyone should know in Excel, it would probably be Pivot Tables, Paste Special, and V-Lookups.

So, that is problem one solved. The second problem can be a little more tricky at times. Imagine, if you dare, the following:

Here we have an administrator using a Tandy or Amiga for their primary reporting system, the output of which they've twisted into a cruel approximation of a usable census. Problems? Let's start with the zip code: people have 9, 8, 5, and 4 digit zip codes. Apparently somebody imported them as text and the leading zeros were cut off. This happens a lot, and the carriers seem to have no way of fixing it themselves. Actually they probably decided it was cheaper to have me do it since they don't have to pay me.

Technically, there shouldn't ever be an 8 digit zip code because the dash would have caused it to be treated as text and the leading zero would have survived, but I'm trying to imagine a worst case scenario.

How do we fix this? Well you only need the last 4 digits of a zip code if you're delivering mail (right? I don't know...), so let's chop those off of the end.

First we'll use the SUBSTITUTE function to replace the dash with some white space. We can trim this later. Next we'll use the LEFT function to grab the first five characters starting from the left. The nice thing about this is that it can handle even the string with less than 5 characters without throwing an error.

As I said, we also need to TRIM this to remove the white space for any 8 digit zip codes. Now we can put back in those proceeding zeros. For this we'll use the TEXT formula with the text set to "00000". If there are 5 digits, Excel leaves them alone, but if there are 4, Excel places them as the last 4 and the first digit will be a zero, just what we're looking for.

The zip codes should now be usable, so we can just copy that column and paste it in as values so that they don't somehow get destroyed by the end user.

Now let's look at these dates:

Note that these dates have changed from previous pictures. Suffice to say that the conditions under which I produce these blog posts are not ideal, specifically I do not own a copy of Excel and must use a remote connection to finish the ideas I start at lunch. I know, I know, terrible...

Back to the problem at hand, not only are these dates text strings mixing letters and numbers, but the day field doesn't have a leading zero for the first 9 days of the month! This could get painful.

Instead of creating a bunch of ugly if formulas, I'm just going to create a few new columns temporarily. The first will grab the month using LEFT:

To get the day and year with a proceeding zero for the date, we're going to use SUBSTITUTE to pull out the month and then format it as TEXT using "000000":

Our next task is to turn that 3-letter month into something usable. I'm going to replace it with the number of the month using a V-Lookup because our final destination, the date formula, can't use the text. In some empty space, type the following:

Select those 4 cells and hover your pointer over the lower-right corner. That plus symbol is what I call the pattern copier. I call it that because that's what it does and I don't actually know the real name. Anyway, once your cursor turns into the big + symbol, click and drag down until you get to December. This is your v-lookup table.

We're basically done now. The last step is to use the DATE formula to create a usable D.O.B. DATE accepts numbers as the arguments for year, month, and day, returning a date. The year and day we can get simply using RIGHT and LEFT, while we'll use a vlookup for our month. Remember to hit F4 to lock the range for your v-lookup. I still forget to do that on an almost daily basis.

It should look like the following:

And there you have it. As a quick bonus, my personal method of calculating a person's current age based on their date of birth is:

=year(now()-dateofbirth)-1900

Just make sure you format it as a number; Excel will format it by default as a date and it will be more useless than my Google ads.

That leaves the third problem: no tiers. This post is getting long though and I still want to watch The Unborn tonight, so that problem will have to wait for another day. It shouldn't take long though, so I may have time to put that up this weekend and even proof read this post...

-David

Book Review Isn't Happening

2009-10-20T07:37:00.000-07:00

Well it turns out the 4-Hour Work Week is more get rich quick than productivity. Also, I had to return it to the library yesterday and I was only half way through, so that review probably won't happen. The book did have some interesting ideas though, and I really like the idea of outsourcing all of my email to an assistant in India who can tell everyone, "No, I don't have time to meet with you. No, I can't tell you how well your proposal is positioned. No, I won't price an HSA plan you have no intention of implementing."

I'm really hammered this week at work, so the posting might be a bit light. I'll try to do something at lunch today if I get a chance. Blogging is much harder than I anticipated, but then again I've spent over an hour drafting a 3 sentence email, so maybe I need to stop obsessing over my writing and just get it out there.

Hopefully you're having a better week than me. Oh, and the comments are now working, so if you have an Excel question please fire away.

-David

State Postal Code Function

2009-10-15T19:00:00.000-07:00

Here is the function I talked about in the previous post. I don't really know anything about VBA, so I'm sure this code has all kinds of issues. For instance, I couldn't find a way to initialize values in a static array using a comma separated list and instead had to initialize each spot in the array on a separate line. I'm pretty sure you could do that with Java, but it's been a while.

The code after the break:

Public Function stateab(State As String)

Dim StateName(1 To 59) As String
Dim StateCode(1 To 59) As String

StateName(1) = "Alabama"
StateName(2) = "Alaska"
StateName(3) = "American Samoa"
StateName(4) = "Arizona"
StateName(5) = "Arkansas"
StateName(6) = "California"
StateName(7) = "Colorado"
StateName(8) = "Connecticut"
StateName(9) = "Delaware"
StateName(10) = "Dist. of Columbia"
StateName(11) = "Florida"
StateName(12) = "Georgia"
StateName(13) = "Guam"
StateName(14) = "Hawaii"
StateName(15) = "Idaho"
StateName(16) = "Illinois"
StateName(17) = "Indiana"
StateName(18) = "Iowa"
StateName(19) = "Kansas"
StateName(20) = "Kentucky"
StateName(21) = "Louisiana"
StateName(22) = "Maine"
StateName(23) = "Maryland"
StateName(24) = "Marshall Islands"
StateName(25) = "Massachusetts"
StateName(26) = "Michigan"
StateName(27) = "Micronesia"
StateName(28) = "Minnesota"
StateName(29) = "Mississippi"
StateName(30) = "Missouri"
StateName(31) = "Montana"
StateName(32) = "Nebraska"
StateName(33) = "Nevada"
StateName(34) = "New Hampshire"
StateName(35) = "New Jersey"
StateName(36) = "New Mexico"
StateName(37) = "New York"
StateName(38) = "North Carolina"
StateName(39) = "North Dakota"
StateName(40) = "Northern Marianas"
StateName(41) = "Ohio"
StateName(42) = "Oklahoma"
StateName(43) = "Oregon"
StateName(44) = "Palau"
StateName(45) = "Pennsylvania"
StateName(46) = "Puerto Rico"
StateName(47) = "Rhode Island"
StateName(48) = "South Carolina"
StateName(49) = "South Dakota"
StateName(50) = "Tennessee"
StateName(51) = "Texas"
StateName(52) = "Utah"
StateName(53) = "Vermont"
StateName(54) = "Virginia"
StateName(55) = "Virgin Islands"
StateName(56) = "Washington"
StateName(57) = "West Virginia"
StateName(58) = "Wisconsin"
StateName(59) = "Wyoming"
StateCode(1) = "AL"
StateCode(2) = "AK"
StateCode(3) = "AS"
StateCode(4) = "AZ"
StateCode(5) = "AR"
StateCode(6) = "CA"
StateCode(7) = "CO"
StateCode(8) = "CT"
StateCode(9) = "DE"
StateCode(10) = "DC"
StateCode(11) = "FL"
StateCode(12) = "GA"
StateCode(13) = "GU"
StateCode(14) = "HI"
StateCode(15) = "ID"
StateCode(16) = "IL"
StateCode(17) = "IN"
StateCode(18) = "IA"
StateCode(19) = "KS"
StateCode(20) = "KY"
StateCode(21) = "LA"
StateCode(22) = "ME"
StateCode(23) = "MD"
StateCode(24) = "MH"
StateCode(25) = "MA"
StateCode(26) = "MI"
StateCode(27) = "FM"
StateCode(28) = "MN"
StateCode(29) = "MS"
StateCode(30) = "MO"
StateCode(31) = "MT"
StateCode(32) = "NE"
StateCode(33) = "NV"
StateCode(34) = "NH"
StateCode(35) = "NJ"
StateCode(36) = "NM"
StateCode(37) = "NY"
StateCode(38) = "NC"
StateCode(39) = "ND"
StateCode(40) = "MP"
StateCode(41) = "OH"
StateCode(42) = "OK"
StateCode(43) = "OR"
StateCode(44) = "PW"
StateCode(45) = "PA"
StateCode(46) = "PR"
StateCode(47) = "RI"
StateCode(48) = "SC"
StateCode(49) = "SD"
StateCode(50) = "TN"
StateCode(51) = "TX"
StateCode(52) = "UT"
StateCode(53) = "VT"
StateCode(54) = "VA"
StateCode(55) = "VI"
StateCode(56) = "WA"
StateCode(57) = "WV"
StateCode(58) = "WI"
StateCode(59) = "WY"

If Len(State) = 2 Then
    For i = 1 To 59
        If StateCode(i) = State Then
        stateab = StateName(i)
        Exit For
        End If
    Next i
Else
    For i = 1 To 59
        If StateName(i) = State Then
        stateab = StateCode(i)
        Exit For
        End If
    Next i
End If

End Function

The important thing about this is that it's declared as a Function instead of Sub. It is also set up to accept a String as an argument.

The way the function works is simple. It checks the length of the String you passed it to see if it's possibly a postal code or a state name. If it's a state name, it counts through the StateName array until it finds a match and returns the value from the equivalent position in the StateCode array. If passed a postal abbreviation, it returns the full state name in a similar manner. It doesn't catch any errors or do anything fancy, but it seems to work okay. If I was going to change it, I'd probably have it trim the String that is passed to remove any extra white space. Also, I might want to add some variations of Washington, D.C.

It's very important to give your function a good, strong name. I named this function stateab, after St. Ateab, the patron saint of the post office.

If you've never created a function in Excel--as opposed to a macro--here is a quick tutorial.

When you first save your function and then try to call it in your workbook with =stateab(A1), you're going to get a Name? error. The problem with functions in Excel is that they only load correctly if you load them as an add in; otherwise, you have to specify a path to the sheet that houses the function, add it as a reference, and it still probably won't work.

Comparatively, creating an add in is dead simple. You want to create a separate workbook for your function called "YourNameFunctions" or something, but definitely don't put it in your personal.xls. Personal should be for macros, this new workbook will be for functions. You only need to save this function in the workbook using the visual basic editor, the rest of the workbook should be blank.

Once that is done, go to file and select "Save As". From the huge list of extensions, select ".xla". This is for Excel Add In. You'll want to save this file in your default Add-In directory for Excel. To find this, go to Tools > Add-Ins > Browse. The directory you start in will be the default directory, and you should put your file there. Alternately, you can use this browse button to find your file anywhere on your computer.

Once the file is saved, you should restart Excel (I think...). After that, when you go to Tools > Add-Ins, the file you just saved should show up on that list. Check the box next to it, and you're set. Test it out; the function should work fine without Name? errors or any other shenanigans. Like your other add ins, your function workbook should now load every time you start Excel.

Upcoming posts will cover basic census manipulation and a possible review of a productivity book: The 4-Hour Work Week.

-David

Useless Metrics and Scatter Plots

2009-10-11T20:27:00.000-07:00

I was reading Lifehacker Friday during lunch and saw a story about DataMasher.org. DataMasher seems like a good idea: simplify data from various government reports, split it up by state, let users specify a relationship (i.e. alcohol consumption / average temperature), and drop the results onto a choropleth map. I actually like the site, the choropleth maps are very nicely done and there are some fun data sets that I might normally be too lazy to dig up. I'm also a big fan of things that are free.

The problem with the site is that it allows you to save these "mash-ups" and you can see just how poorly most people understand statistical measures. Apparently after miles-per-gallon swept the Statistical Measurement Awards back in 1920, people decided that the best way to find the statistical relationship between any two variables is to divide one by the other. Always. The result is a bunch of super informative "mash-ups" like, oh, # of Deaths Due to Motor Vehicle Accidents per 100k divided by Alcohol Consumption/ Binge Drinkers.

Motor accidents divided by binge drinkers displayed by state. Hmm. What is this supposed to tell me? That drunk drivers in Tennessee are that much more effective killing machines than the ones here in Washington? That people in Utah drink less? No, it can't tell you anything, and every single mash up on the site is like this! People chose two variables that are strongly correlated, divided one by the other, and created a completely useless metric.

It would be like creating a map that shows number of human eyeballs per state divided by number of humans per state. In a perfect world, states would start with a value of 2 in the very center of the country but approach 1 the closer they got to the ocean due to the increasing number of pirates. In reality though, every state will have a value of almost exactly 2 and my map will be an uninformative, one-color piece of suck.

There are extremely few useful charts in the world, and almost all of them are a minor variation on bar/column charts, line charts, and scatter plots. This isn't a rule or anything, but it's a good starting point: when comparing between different categories use bar/column charts, when comparing over time use line charts, and when comparing two variables use a scatter plot.

Scatterplots are perfect for almost all of the data sets people combined on DataMasher.org. The only trouble is that 50 states is a lot of points and you can't label XY points in excel so you're out of luck if you want people to know which states are which. The solution?

By far my favorite, favorite, favorite add on for Excel is, well, Sparklines, but a close second would be John Walkenbach's Chart Tools or, alternately, the XY Chart Labeler add in. Labeling points in an XY chart is awesome. If you haven't tried it, you don't know what you're missing. You can use it to label points, create a fake scale on an axis... well, I'm sure there are a lot of other great things, but those are the only two I can think of right now.

The great thing about using this for states is that you can reduce the state down to the postal abbreviation, center the data label, and then delete the point marker. This cleans up the chart quite a bit and is similar to the idea behind a stem-and-leaf plot: using the values as the visual element of the chart.

To add the data labels, first you need to install one of the chart utilities I mentioned above. This example will use JWalk Chart Tools. First, I like to make my points invisible, but you can do this in either order. Double click on the points, go to the Patterns tab, and set both lines and points to 'None'.

After you hit okay, you'll have some invisible points. If you ever lose these and want to edit them again, just use the arrow keys to move around the different chart elements and you'll eventually find them. After that, hit Shift+F10 and the right-click menu for that object will pop up. I use this more often for axes that I've deleted and want to rescale.

After you right click on the points, select JWalk Chart Tools from the bottom of the menu. Select the Data Labels tab, choose the series you want to label from the list, and then drag the selection over the range of state postal code abbreviations. Leave the box checked for Create Links to Label Cells.

Now you'll want to double click the data labels and center them so they aren't off to the right of your actual points. You'll likely end up with a few points majorly overlapping. The main point of a scatter plot is to show relationships, not exact values, so don't be afraid to jiggle the data to make it legible. People are probably going to be more interested in the trend line than the individual points in any event. Scatterplots always need trendlines. You can add this by right clicking on your points and selecting Add Trendline.

That was a nice looking chart. I'm still figuring out this whole blogging thing. The default image hosting in Blogger is giving me fits.

One thing I didn't mention in all of this is that the original data I pulled from DataMasher.org only had full state names not the postal abbreviations. I'd like to think I know all of the postal codes, but I send maybe 5 letters in an entire year and all of those go to the same 3 states. And even if I did remember them all correctly, it's a pain typing out 50 postal codes. That's probably over 100 letters!

So, I wrote my own Excel formula that exchanges state names with postal abbreviations and vice versa. That post will be later this week.

-David

Drawing Toolbar

2009-10-06T19:23:00.000-07:00

Just a quick post today, and no math.

Before I get into today's tip, I want to explain a bit about my background. When I went to college (2000-2007), we never learned anything about Excel. We did our regressions and such in a program that I think was called R. There might have been another program too, but nothing that anybody uses in real life. Definitely not SAS. Anyway, when I started applying for jobs, everyone kept asking me how comfortable I was with Excel, and I would tell them I hadn't really used it but I'm sure I could pick it up pretty quickly.

I didn't get called back on these jobs, so when I interviewed for my current job, I lied my ass off. I don't recommend this in most scenarios, but I've used enough Microsoft products to know that the help files are pretty exhaustive and anything else I can find online easily enough. It's just a bunch of boxes and numbers, how hard could it be?

Within a couple of weeks I was considered the office Excel guru. I would get a lot of comments along the lines of "Wow, how do you know all of this stuff?" and "You're lucky, we never learned this kind of thing when I was in school", and in my head I'd be answering "I literally copied and pasted your question into Google, clicked on the first link, and read it" and "No, you're lucky! Because, um, uh... dammit."

Eventually though, the help file failed me. I tend to spend a lot of time lining up charts and tables and I realized that this spending felt a lot like wasting, so I went into the help file and typed in "snap to grid".

Do you know how many articles the help file has about snap to grid? Zero. Zip. Nothing. I might as well have typed in "make things you drag line up more better". But this option is in Excel! Do you know what it's called? That's right, snap to grid.

And no, I didn't Google it in this case, instead I wondered who would be stupid enough to make a program with a grid that you couldn't snap to and proceeded to waste roughly two years of my life trying to line my charts up to within a pixel of each other using a ruler.

Today's tip then is to use the draw menu. There are some fantastic things in the draw menu, such as:

Snap to Grid
Align (Left, Right, Center, Top, Middle, Bottom)
Distribute (Horizontally, Vertically)
Autoshape
Text Box

Snap to grid does what you'd expect, and it was everything I imagined it would be.

The align and distribute options are great for lining up or equally spacing multiple charts without resizing your columns and rows. Here is an example of align bottom:

Why yes, that IS bold-italic Comic Sans! You like it too?

Finally, if you make a lot of charts and you aren't using auto shapes or text boxes, you're either very lucky to have such nice data or you're doing it wrong. I'm all for making charts correctly, but every once in a while I have an outlier that I have to show in a graph but I don't want to smash all of my data down to fit it on the axis. Before you can even think of some fancy way to finesse a solution with alternate axes or clever use of data labels, I've simply drawn the point onto my chart and I'm good to go. Things like this used to bother the perfectionist in me, but because I have deadlines and because so many of my charts seem to be one-off projects instead of something I'm going to do over and over, I no longer mind this "duct tape" approach to fixing charts.

Also, for things like that outlier above, another great feature is grouping. You can select the text box, the line, and the square, right click, and then select "Group"; now they will all move in perfect harmony instead of us having to drag them one at a time.

That's all for today. I'll probably get back to my large claim chart later this week. Oh, and I've sort've narrowed down the scope of this blog. I'm going to focus on Excel, general office productivity, and whatever video/board games I'm currently playing. I'll try to keep that last one to a minimum, but I'm sure some people would like a detailed analysis of the math behind Settlers of Catan.

* Every once in a while I do horrible things at work just to get a reaction. Things like using donut charts, drop shadow, 3d-effects, and Comic Sans. Nobody notices.

Two Color XY -Area Combo Chart

2009-09-30T19:25:00.000-07:00

I was reading a post on the PTS Blog about how to fill in the area between two lines in an excel chart and I thought of a way to make it more complicated and thus more intimidating to your coworkers.

The original article shows how to color the area between two lines in a chart where the lines never cross. I'm going to assume you're like me and you read that blog every day so you already know what I'm talking about.

What if you want to cross the lines? The good news is that crossing the lines doesn't break anything, the area between the lines is still filled in perfectly. The bad news is that the fill series is only one color and as soon as you show it to someone they'll ask why the positive and negative areas aren't green and red, respectively (which you shouldn't do anyway because a lot of people are red/green colorblind). Before today, you would have said that it would take too long to add a second color, that it was impossible, or you would have just drawn auto shapes on top of the chart. Today though there is a much easier solution:

Download the workbook at the end of this post.
Plug in your data values.
Profit.

If you actually want to know how the chart works, please read on...

I'm assuming we're all starting from this point:

Okay, so the first thing that is obvious is that you'll need to add another data series if you want an additional color. Creating separate positive and negative data series doesn't work by itself though, you need your positive and negative shading to end where the lines intersect, but the shading will continue on to the next point on the axis with the current configuration. What we need to do is find the points where the lines intersect and add them to our secondary x-axis (just a recap, the lines are on the primary x-axis, the shading on the secondary axis).

This isn't as hard as it sounds, but when searching for a way to find the point where lines intersect in Excel I found a lot of people suggesting Goalseek. Letting machines do math for you is only okay if you told it exactly what to do. If you didn't tell it what to do, the computer is thinking for you, and that is a slippery slope to Skynet. But that's a whole different post.

The common formula for a straight line is y = mx + b, where m is the slope and b is the y-axis intercept. At each point of intersection, we have two lines:

y1 = mx1 + b

y2 = nx2 + c

Because we want the point where the lines intersect, y1 = y2 and x1 = x2. Because both of the formulas equal y, they are also equal to each other and we can simplify to:

mx + b = nx + c

Solving for x gives us:

x = (c-b)/(m-n)

Excel has built in functions for calculating the slope and the intercept for a range of x and y values which they've cleverly named SLOPE() and INTERCEPT(). We'll only need two points to calculate each of our line segments, so our formulas will take the form

slope({y1,y2},{x1,x2}),

where (x1,y1) are the coordinates of the beginning of the line segment and (x2,y2) is the end point of the line segment.

Because the lines can potentially cross between any two data points, we're doubling the size of the original secondary x-axis. Every other point will be one of the original data points, and the in between points will either be NA() if the lines don't cross (Excel will skip over NA() errors when plotting most types of charts) or something like this formula if they do cross:

=(INTERCEPT(c4:c5,d4:d5)-INTERCEPT(b4:b5,d4:d5)) /

(SLOPE(b4:b5,d4:d5)-SLOPE(c4:c5,d4:d5))

Of course the formulas in the workbook are much uglier because I'm counting ROWS() and using the OFFSET() formula so I don't have to manually add each line segment from the original data set, but it's a little hard(er) to follow.

Your new secondary x axis should look something like column G. You stil run from 0 to 1,000, you still repeat the first and last value, and every other point is one of the original points as calculated in Mr. Peltier's post. The points in between are #N/A errors where the points don't cross, and the points of intersection we calculated with the INTERCEPT() and SLOPE() formulas if they do. An easy way to tell if the lines cross? In column F we had previously calculated the difference between the two original data series. If the product of any two adjacent points in column F is negative, the lines intersect between those two points.

The next step is to rebuild the three Area data series. Area1 is the transparent series , Area2 is the positive series, and Area3 is the negative series.

To create Area1, every other row should point to an entry in the original series in column E. The in-between points should once again be NA() errors if the lines don't intersect (same for Area1 and Area2 as well), and when they do intersect we just need to calculate the y value at that point. We already have the x value, so we can plug that into our y = mx + b equation. The formula for H9 would be:

=SLOPE(b6:b7,d6:d7)*g9+INTERCEPT(b6:b7,d6:d7)

You could also use the points c6:c7 for your y values as both line formulas will give the same result with that x value (g9).

Area2 and Area3 are incredibly straight forward. We still use the points in column F for every other entry, but the trick is that we only use the positive points in Area2 and only negative points in Area3. This is easy to do with MAX(f4,0) for Area1 and MIN(f4,0) for Area3. All other points are either NA() errors if there is no point of intersection or zero.

Now you just need to go into your graph and add these series to the chart. Series 1 and 2 are lines, Series 3 (the transparent series) should now point to h2:h20, Series 4 (the positive series) should point to the values in column I, and you'll have to add a Series 5 (negative) to point to the values in column J. The secondary x-axis values also need to be changed to the values in column G. Also, if your Series 5 didn't default over to the secondary axes, you'll have to switch it. Double click on each area series, change the colors, and you're all set.

If everything went correctly, you should have something like this:

If not, the workbook will make more sense than I ever could:

http://drop.io/davidatwork/asset/twocolorxyareachart-xls

Let me know what you think in the comments.

-David

Large Claim Chart #2 - Pretty Pictures

2009-09-24T19:23:00.000-07:00

Alright, I finally got around to posting this thing...

So when we left off I was explaining the idea behind a large claim chart and why they can be useful. Now, a history lesson.

When I first started my job, we had one table that explained large claims that I've recreated below. Like all images on this blog, please click for a usable size.

Here we have 5 years of large claims in 5 bins. The size of the bins changes depending on the group, but suffice to say that they are almost always worthless. The average year for a mid-sized group will have 25+ large claims between $25,000 and $250,000 each. You can't split that up into 5 useful bins, it's too much ground for just five bins to cover. Looking at the large claim table as filled in above, even if I capped all of the claims at $150,000 the table could total up anywhere from $3.1m to $4.7m. That isn't precise by anyone's standards.

Also, they aren't really bins. If you look at the header row it has a greater than sign, which means in 2008-09 there were 6 claims total over over $15,000, all 6 of those were over $25,000, 5 of those were over $50,000, etc. So a claim in the largest bin is also counted in every other bin. Apparently this isn't intuitive because I get asked about it all the time and I usually have to explain it for 30 minutes before it clicks. I'd like to scrap this chart altogether, but people fear change. Who can blame them?

This is my first attempt to show off large claim experience visually. Note that I didn't crop or touch up these images. Also, for this image pretend that there are five years shown on the x-axis that correspond with the five vertical columns of dots.

You may ask yourself, "Why are the dots arranged semi-randomly in each year? And why don't I own Spaced: The Complete Series on dvd?" This is still the largest problem with the chart, but I can't think of a better way to do it. Large claims occur over several months so it doesn't make sense to arrange them by month, and since we're concerned primarily with stop loss we only care what the year end total was for each plan year. I can't arrange the claims in a vertical line, they would overlap too much. So instead, clusters. They've grown on me, but it still seems like that x-axis could be put to better use. If you can think of a better way to arrange them, please leave a comment.

I should also point out that the clusters aren't random. They used to be, but now they are just scattered over 5, 7, or 9 repeating horizontal positions in each year. This gives me a little room to play with the data so that it doesn't overlap and hopefully looks more appealing without drawing every single stupid dot by myself.

So that chart was a good start, we can easily see at a glance that year three was very good and year four was very, very bad. New questions came up though. What was wrong with all of these large claimants? Is it contagious? Would it have been preventable with early detection? Do we need to address it in our wellness plan*? Also, which of these claims exceeded the stop loss deductible?

This next version of the chart addresses those questions (I mean, it would've if I had included the diagnosis for each of those labels, but I'm saving that for the final reveal. Skip down if you're impatient). Looking at the numbers on each claim will give you the diagnosis, but when you see the same diagnosis in several years you wonder if it's the same person. We have an answer for that too. Oh, and I added a thick, grey line to indicate the specific deductible in each plan year.

The chart has now transformed into what I call a "Spaghetti and Meatballs" chart, which I think deserves scare quotes (in the future, all quotes are scare quotes). I changed the color of claimants appearing in multiple years to a peaceful green and connected them all with lines. I don't have a picture of what the chart looked like when I kept everything orange, but before you go trying to recreate it at home please believe me that it will make you gouge out your eyes.

That chart is pretty informative, but it can be improved. After seeing someone with a horribly expensive, scary diagnosis appearing year after year, the person who pays those bills will wonder if they are still active on the plan.

Here is the current and possibly final version of my large claim chart. In an attempt to put Edward Tufte's Escaping Flatland chapter into practice, each point shows which year a claimant appeared in, how much their claims were that year, if they appeared in multiple years, if they are still active, and what they were diagnosed with that year. It's also sort of a Micro/Macro design, with the graph above giving a rough overview of the large claim history with more gritty details available below for those willing to invest some time into the chart.

And what stories it can tell! Look at the $250,000 claimant in 2007-08. That year she was Owned by a Lich, the following year she suffered Beholder-Related Agony, and in 2009-10 she was Reamed by a Dragon. Despite all of that, she is still active on the plan! What a daring tale of survival! Less uplifting is the story of the $350,000 claimant in 2005-06. With $350,000 in claims and a diagnosis of Finger of Death, we can only assume the worst when we observe that this member is no longer active.

Unfortunately the data table is still there, but I had to leave a familiar point of entry in the chart so people wouldn't completely freak out.

Better version available here.

A few boring notes on the chart that I feel obligated to throw in: I really like it, but I want to make it even better so feedback would be great. However, I made this at work and they'd probably be irritated if it started showing up other places, so please don't use this commercially. There is a Creative Commons thingy below. Also, we take HIPAA very seriously, so all of these claim numbers included are random and all of the diagnoses are D&D inspired because I've been playing an obscene amount of Baldur's Gate 2 lately.

-David

* Don't get me started...

Large Claim Chart by David Montgomery is licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 United States License.

Update

2009-09-23T18:06:00.000-07:00

Did anybody read my blog while I was gone? No? Great!

I'm going to get serious about updating this thing at some point, just probably not this week. I'm a little bit under the weather, I'm preparing to reformat my Mac and install Snow Leopard, my 1TB drive with all of my movies on it died, and Jaclyn surprised me with a DSi for my birthday about a month early. So yeah, not a lot of time to blog.

My large claim chart will eventually be posted. Also, the over-commercialization of this blog is a joke. But that doesn't mean you shouldn't buy stuff!!! I'm trying to buy a house in the next two years. I'd love to put Adsense up, but for some reason Google insists I already have an application in that has been pending review for 2 months.

Large Claim Chart #1 - Background

2009-09-16T19:00:00.000-07:00

In the coming days (or weeks, my Gamefly account is dominating a zero-sum game with my blog) I'm going to be posting my large claim chart. It gets a little health insurance-y, so I thought I'd explain a few things before I begin.

For the zero people who read this blog...

Anyway, I'm an employee benefits consultant and analyst working on mostly self insured groups. What self insured means in Washington State is that the group pays all of their claims up to a certain point, either on an individual or aggregate basis. After that, the group's stop loss insurance will pay the rest of the claim(s). For the purpose of the large claim chart, we're interested in individual stop loss insurance.

Individual stop loss insurance is nearly every self insured group's largest fixed cost. Let's say a group has a specific deductible of $200,000. Some jerk on the plan gets mauled by a basilisk and goes in for a $500,000 operation to save his life. Why does the plan care? Not only do they have to pay the $200,000 to meet the specific deductible, but their insurance company isn't going to be happy about eating a $300,00 stop loss claim and they'll get that money back with interest when they set premiums at the next renewal (medical plans renew annually).

The insurance carrier knows they can charge anything they want next year because no one will touch a group that gets mauled by basilisks without lasering the large claimants. A laser is a separate deductible for high risk people. For this group, they'll pay all claims for every member up to $200,000 per member, but the high risk claimant with the laser will have a separate deductible, let's say $1,000,000, that the plan will have to pay before insurance kicks in. No one likes lasers, they are risky and they destroy moral.

"Oh look, it's Xerxes from accounting. I heard he has a $1,000,000 laser and that's why our medical plan is so expensive. Let's put a basilisk in his office."

Because sick people never, ever get better, it's important for groups to be aware of their past large claims. It helps them pick the right specific deductible level for their group, set a budget, and it's also a lot of fun to look at the de-identified large claim data and try to guess who it is.

When I started working at my office, we didn't have a chart for the large claim history, only a data table. I'm a visual person, or at least I convinced myself that I was after reading a few Edward Tufte books, so I decided to create a large claim chart that would tell the story of their large claims visually.

Next post, I'll show the evolution of the chart. After that, I'll briefly explain how I made the chart in Excel and then you can laugh about how inelegant my solution is and tell me how I could have done it ten times faster.

-David

New Blog

2009-09-13T10:48:00.000-07:00

I finally gave in and started a new blog. I guess I just like talking about myself too much.

I'm going to try to keep the tone of this blog a little more professional. We'll see how that goes.

When time permits, I'd like to do a series of posts about my large claim chart. Feedback would be appreciated, but I'll be shocked if anyone I don't know reads this blog.