Add Scroll Bars to a Chart, Blog Stats

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:

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.

3 Common Census Problems and How to Fix Them

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.

Book Review Isn't Happening

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

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:

Useless Metrics and Scatter Plots

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.

Drawing Toolbar

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?