More stupid Excel tricks: A secret power of IF

The hardest part about working with data, especially in large sets, is the people who input it in the first place. The reason they make it so difficult is because they’re inconsistent, not only in their day-to-day habits, but between one or more different people all entering info into the same database.

When you’re creating something solely for yourself, then by all means be as inconsistent or idiosyncratic as you want. But if it’s a group project creating information that someone like me is going to have to derive useful information from at some point in the future, inconsistency can make my job infinitely more difficult.

This is the reason why things like style guides were created — and they don’t just exist for the written word. Accounting and data management have their own style guides. So does computer programming, although that field has the advantage, because the program itself won’t let you get it wrong. Excel is the same way, although it won’t always tell you how to make it right.

Little things can cause problems and cost a business money. Sally may prefer to spell out words in addresses, like Avenue or Boulevard, while Steve likes to abbreviate with Ave or Blvd. Sam is also big on abbreviations, but always with periods. Seems innocuous, doesn’t it?

It does until the only way to make sure that a massive mailing doesn’t go to the same household at the same address twice is to compare the addresses to each other. That’s because, to a computer, 1234 Main Street, 1234 Main St, and 1234 Main St. are all completely different addresses. There’s no easy way to fix that because computers don’t have a “kinda sorta look the same” function.

Garbage in, garbage out

It’s also important that a database be designed properly. For example, names should always be entered as separate units — title/prefix, first name, middle name, last name, suffix. They can be combined later when necessary. A lot of good databases do this, but it’s completely worthless if somebody enters the first and middle names in the first name field or adds the suffix to the last name. You may have heard the expression “garbage in, garbage out,” and this is a prime example of that. All of the right fields were there, but if used improperly, it doesn’t matter.

Of course, the proper fields aren’t always included. One example I had to wrestle with recently in a former career was a database showing the various insurance policies people had with the agency. Now, that is useful and necessary information, as well as something that legally needs to be maintained. And it’s all right that a person gets one row of data for each policy that they’ve had. Some people will have one or two rows, others might have a dozen or more.

So what’s the problem? This: There are no data flags to indicate “this is the policy currently in effect.” This was doubly complicated since it’s Medicare related health insurance, so someone can have up to two active policies at a time, one covering prescription medications and the other a Medicare supplement. Or a policy may have expired after they decide to drop an MAPD and go back to “original” Medicare but the only way to know that is to look for an ending or termination date — if it was ever entered.

The secret power of “IF”

This is where one of my “stupid Excel tricks” came into it. You may or may not be familiar with some of the numeric functions dealing with columns or rows of numbers, but they basically operate on a whole range. They include functions like SUM, MAX, MIN, and AVG. The usual usage is to apply them to a defined range or series of cells and they have no operators, so you get things like:


Here’s the fun trick, though. If you add one or more “IF” statements within any of these functions, you can perform the operation on a sub-range of data defined by certain criteria. In the example I’m giving, it would look at all of the insurance effective dates for one person and determine the most recent one, which is usually a good indicator of which policy is in effect.

Generally, each item you’re evaluating is in the form of [DataRange]=[CellValue], or in actual Excel terminology, it might look like “$A$1:$A$470=A12” for the version entered in row 12. After the criteria ranges, you enter the range that you want to perform the operation on, close out the parenthesis, then enter.

So let’s say that we have last name in column B, first name in column D, and the dates we want to look at to find the latest are in column N. Our formula would look like this, assuming that the first row has the field headers and the data starts in row two:


If you’ve entered it right, the formula should be displaying the right number. In effect, you’ll have created a column down the far right side in which the value opposite any particular person’s name equals the maximum date value, meaning the latest. Then you can do an advanced filter (oh, google it!) to pull out just the unique name data and date, then use that to do an INDEX and MATCH to create a dataset of just the most recent plan and effective date. (I covered those two functions in a previous post.)

Or… the original database administrator could have just put those current plan flags in the data in first place, set them to automatically update whenever a newer plan of the same type was added, and voilà! Every step since I wrote “This is where one of my stupid Excel tricks came into it” 396 words ago would have been unnecessary. Time and money saved and problem solved because there was never a problem in the first place.

The art of improv in Excel

On the other hand… solving these ridiculous problems of making large but inconsistent datasets consistent with as little need as possible to look at every individual record just lets me show off my ninja skills with Excel.

It’s really no different than improv. Badly entered data keeps throwing surprises at me, and I have to keep coming up with new and improved ways to ferret out and fix that bad data. In improv, this is a good thing, and one of our mottos is, “Get yourself in trouble,” because that creates comedy gold as things in the scene either get irredeemably worse or are suddenly resolved. Damn, I miss doing Improv, and long for the days when we can finally return to the stage, which has been seeming even more remote by the day. But I do digress…

Back to the point: In real life, not so much for easy resolution. It’s a pain in the ass to have to fix the curveballs tossed at us by other people’s laziness and lack of diligence — unless we approach it like a game and an interesting challenge. Then, real life becomes improv again in the best sense.

And I’ll find it forever amusing that the same rules can apply to both a spontaneous, unplanned, free-wheeling art form, and an un-wielding, rigid and unforgiving computer program. They both have their rules. Only the latter won’t allow them to be bent. Okay, some improv games have rules that are not supposed to be bent. But half the fun is in bending those rules, intentionally or not.

With Excel and data-bashing, all of the fun is in following Excel’s rules, but getting them to do things they were never intended to.

Image source: Author, sample output from a completely randomized database generator in Excel used to create completely artificial test data for practicing functions and formulae without compromising anyone’s privacy. Maybe I’ll write about this one some day, if there’s interest.

Sunday nibble #49: Adverse selection

If you want to buy most kinds of insurance, you can do it whenever you want. Need to start or change your auto, homeowner’s, renter’s, fire, disaster, liability, or life insurance? You can pretty much do that at any time.

But there’s one huge exception, and that’s health insurance. You can only sign up or change it during certain designated annual or open enrollment periods.

President Biden recently extended the annual enrollment period under the Affordable Care Act (ACA) so that more people could get insured, but you’re probably wondering, “Why do they have these restrictions at all?

On the surface, they do sound stupidly arbitrary. Full disclosure: I work for a broker who only deals with Medicare insurance, it works like this. If you want to change your prescription coverage, you can only do it from October 15 to December 7 every year. You can only change a Medicare Supplement plan from your birthday to 60 days after, and only change MAPDs or dump an MAPD to go back to “original” Medicare (Parts A & B) from January 1 to March 31.

Okay, technically, you can change any of these plans at any time, but outside of these periods, you have to answer health questions and are subject to underwriting, meaning you could wind up paying a lot more if you have a pre-existing condition.

But, as if to emphasize the apparent arbitrariness, some health insurance providers will suddenly say, “Hey, we’re having an underwriting holiday, so you’ve got an extra three months to switch your Part D, or you can change your supplement no matter when your birthday is!”

So… it’s clearly not a hard and fast Medicare rule. In effect, it’s a system to confuse the hell out of people, and every commercial you see on TV referring to Medicare exploits that big time. For example, when it’s only the time to change your prescription coverage (Part D), you’ll be bombarded with commercials trying to sell you Medicare Supplement Plans.

These unscrupulous advertisers also like to pitch the idea that “You, yes, you, can get $144 back every month  (hey, that’s four bucks less than the whole Part B premium!) if you call now.

Except, you can’t, unless you qualify for Medicaid/MediCal, meaning you have no assets and earn a ridiculously tiny amount each month.

These ads are designed one goal in mind, because they’re not selling you a product, they’re looking to turn you into the product.

You (i.e. being Boomer over 65) call the number on the screen. You give them info and then they lower the boom, based on your birthday. If it’s in the 60 day window, great — they’re going to hard sell you a supplement plan, but it’s going to be the one plan from the one carrier the company who ran the commercial represents.

And that is not necessarily the best option, since the specifics of coverage can vary.

Now, if it’s not within 60 days after your birthday, congrats. After the person on the phone takes down enough info to be able to contact you again, you become a “prospect,” and you are going to get pounded with ad material, again trying to sell you that one plan that may or may not be the best for you.

Are there differences? Oh, you betcha, and my boss bases his entire business on it, especially when it comes to Part D coverage. He’s one of, if not the only, agent who makes it a point to annually review the current coverage and updated prescriptions of each of his clients to find out whether they’re on the best plan or not — and this can make a huge difference.

Why? Because different insurance providers offer different rates for the same medications. Company A might consider all of your meds to be Tier 1, which are pretty much mostly covered with little or no copay. Company B might put one or two of them on higher Tiers, which cost more. And beware the company that declares one of your meds not covered, because then you get to pay full retail.

In the case of you being stuck with a brand name drug that has no generic, this can get really pricey. Since I’m the one who runs those drug comparisons in Medicare, I’ve seen nightmare situations in which somebody with a couple of not covered meds could have prescription insurance, and yet still pay more than $50K per year out of pocket just for the drugs, not including the monthly premium for the insurance.

Yeah, it’s nuts. But this does circle us around to why enrollment periods exist, and why getting us to Medicare-for-All or a Single Payer plan is so important.

Here’s an analogy. You decide that you don’t need car insurance, and you drive around without it. Then, one day, you get in an accident that’s decidedly your fault. So you call up an insurance broker on your phone to buy insurance. Yeah, sure, they’ll sell it to you, but good luck with that claim.

Why? Because the loss clearly predated the coverage, so you’re SOL.

This is also why most jurisdictions require people to have auto insurance as a condition of being a licensed driver. Those other forms of insurance from the first graf, not so much.

Why? Because you can do a lot of damage to someone else or to property as a driver getting into an accident, and that asset called your car isn’t going to cover it. On the other hand, homeowner’s insurance isn’t required (although it’s a damn good idea) because the equity in your home would probably cover anything. Just too bad, so sad, if you have to dip into it when somebody trips and breaks their ankle on your badly maintained front walk.

But here is the really logical reason that there are set periods during which people can change their particular health insurance plans, and it’s a statistics game, really.

In the business, allowing health insurance without underwriting at any time is called Adverse Selection, and it goes like this.

You’re generally healthy, so you think, “Why do I need to pay a ton of money for insurance I don’t need?” So you don’t, and you slide without it, and then one day your doctor says, “Hey, you’ve got cancer!”

So you run to the nearest broker and say, “I need health insurance,” but you’re not lucky enough for it to be during an enrollment period. So, he can get you insurance, but you have to answer health questions, and one of those is going to ask whether you’ve been diagnosed with cancer.

Oops. You can get insurance, but you’re going to pay premiums out your ass. in America, cancer be costly.

Not to play devil’s advocate, but the entire point of insurance companies is that they gamble the premiums of people who will never make claims in order to cover the costs of people who do. It’s why auto insurers give good driver discounts.

There’s also an entire skilled profession of people called actuaries, who make it their business to calculate exact risks. So they will tell the insurance company, “In order to not lose money because X% of people will develop conditions that will cost you $Y per year in benefits, you need to have Z many people likely to have no claims signed up and paying premiums as well.”

Hence, enrollment periods. They are designed to greatly reduce the odds that people who are going to suck more out of the system than they pay in are able to sign up.

Side note: This does not explain at all why young, healthy people pay so goddamn much for health insurance. Given their numbers and their health, they should be paying practically nothing. That’s not what I’ve seen or experienced first hand.

The good news? I’ve seen some really lucky people come through our system, like the ones who are diagnosed with cancer a week before Annual Enrollment, or develop some chronic condition with expensive meds right after their birthday.

But those are rare, and probably factored into the system.

Here’s the deal, though. Single-payer eliminates all that shit for one simple reason. If every single citizen were signed up for healthcare, with most of that being covered by a combination of employer contributions and employee deductions, with children under 26 and non-working spouses also being covered, there would be no need to worry about Adverse Selection. Everybody is already covered. Boom. Done.

It’s called Economy of Scale. If everyone is covered, no one becomes a suck on the system. Everyone benefits. Ooh… scary Socialism. Although that’s exactly what Socialism is.

Hey — driven on an Interstate lately? Or, more to the point, have auto insurance in a state that requires it? That’s exactly why it’s fairly cheap, and you can change your coverage at any time. When everybody has it, everybody pays less.

A/B test

Linguists have long debated the topic of whether the language you speak affects and changes the way you think, or indeed creates it, but Stanford researcher Lera Boroditsky believes that it does, and about a decade ago her studies did indicate some surprising things about how language can change the way a person perceives space, time, and relative location.

I won’t go into them here in detail since that’s not the point of this post, but there is an aboriginal tribe in Australia that gives spatial directions in absolute terms, based on compass directions — “What are you holding in your northwest hand?” Consequently, not only are they always aware of their location relative to the compass points, but they think of time this way as well. Ask them to arrange a series of photos in chronological order, and they will do it from east to west no matter which way they’re facing.

If you think about it, that makes perfect sense: Time measured from sunrise to sunset; from dawn to dusk.

She did also notice some cognitive changes when they taught English speakers to use the same kind of terms as other languages. For example, they’d ask their subjects to think of durations not as “long” and “short,” but in terms common to Greek and Spanish speakers: little, a lot, and big. They also had English speakers think of time the way Mandarin speakers do — not horizontally and left to right, but vertically, from top to bottom. Yesterday is up and tomorrow is down. Once they started to think in these terms, English speakers started to perceive time vertically as well.

Different languages can change personalities, too. Someone who is shy and reserved in one language may be outgoing in another, and their degree of fluency may also affect the type and depth of change. It’s also a matter of whether someone is bilingual but monocultural, or bilingual and bicultural. In the case of the former, it’s generally a speaker of language A learning language B in their own A-speaking country, largely free of cultural influence from B. For example, a native-born American studying Japanese, but only in the U.S. in school.

In the latter case, the speaker of A will grow up either in the original country of the A language and culture before moving to learn the B language in the B culture, or will grow up in the B country with parents and possibly grandparents who grew up in the culture of A. For example, someone who was either born in Turkey or born to Turkish immigrants to Germany, who either learned only Turkish during their early schooling and then German after emigrating, or who grew up in a household in Germany where Turkish was the primary language, but learned German in school.

I know from my own experience that my personality changes when I speak Spanish. Me hace mucho más coqueto. It makes me a lot more flirtatious. And while I’m technically bilingual but monocultural, the culture of Southern California is so heavily influenced by Latin America in the first place that it takes actual effort to be monocultural here. Then again, the western third of the U.S. used to be Mexico before we manifest-destinied the shit out of it, and two whole continents belonged to the natives and their expansive empires before the Spaniards and Brits toddled along and screwed that up.

Yeah, in English, I tend to digress to lecture a lot. I don’t do that in Spanish so much, either, unless it’s explaining some fascinating thing I discovered about the language to a fellow learner.


Another aspect of language is the one that creates group personalities, and part of successfully joining any particular group is picking up on their own specific terminology and slang. Not knowing the terms will immediately peg a person as an outsider. This is very true of improv, and at ComedySportz we jokingly say “We are not a cult,” because some of our warm-up games certainly sound like we are.

But if you eavesdropped on a conversation between a couple of improvisers and had no experience, you’d be totally left in the dark hearing terms like Bunny Bunny, 185, Canadian cross, heel and face, “lean into it,” space work, VAPAPO, Harold, scene game, jump out game, head-to-head, brown-bag, groaner, piano torture, and (#) things.

Some of those terms are even unique to ComedySports and improvisers from other companies might not know them unless they’ve seen CSz shows. Now, if you’ve read my previous post, you probably know where this is going.

Since I started working in the field of health insurance, I’ve been learning a completely different set of words and expressions, a lot of them initialisms or acronyms, and by now I can reel them off by memory: AEP, Part D, MAPD, Plan F, Plan G, effective date, “Original” Medicare, Med Sup, HIPAA, ePHI, open enrollment, re-shopping, CMS (with a whole different meaning than in the internet world), guaranteed issuance, birthday rule, SEP, and on and on.

In all likelihood, unless you’ve ever been on Medicare, worked in a related field, or have helped an older relative navigate its rapids, you probably don’t know what many or any of those terms mean. I sure didn’t just over a month ago. Now, I’m rattling them off fluently with my co-workers.

But, at the same time, I’m now taking on more and more responsibility for explaining the things that I legally can to clients who phone in (I’m not an agent, so can’t recommend plans, or quote prices, or that kind of thing), and the calls are becoming more frequent since we just sent out a massive mailing to let everyone know that it’s time to re-evaluate their Medicare Part D, which is the insurance that covers their prescriptions. Long story short, insurance companies change their formularies, or lists of drugs that they cover, every year, and announce the changes effective January 1st on October 15th. These can make huge differences in cost, especially if a plan suddenly drops a particular drug, or another one has a price increase for a certain tier.

Thus begins the AEP, or annual enrollment period, which runs from October 15th to December 7th. Have I bored the hell out of you yet? It’s actually a lot more fascinating than it might sound, and for me it’s a good insight into the monster we’d be up against with any attempt to make Medicare for All work, especially if it maintains its weird four-part structure.

This brings me back to the language thing, though. In essence, I’m helping people understand a foreign language that I’m only just learning myself, and when I’m on the phone I can already feel my personality change. For one thing, I speak a lot more slowly than I usually do, and my entire manner slips much more into friendly but neutral customer service voice.

And yes, it’s a lot different than my phone personality when I was doing customer service for the Dog Whisperer’s website or when I’m dealing with customers who call the ComedySportz L.A. office or box office because, again, those are different worlds and different languages.

I’ve also quickly learned to become much blunter with people who aren’t clients. It’s amazing how many sales calls the office gets, especially with sales people who try to do so in the guise of already having some sort of business or client relationship with the boss, and he taught me a great question to ask: “Are you calling to buy something from him, or to sell him something that will increase his business?”

Not that this will get them through, but at least I’ll take a message instead of hang up on them.

The real trick, though, is to not get caught up in the confusion that a lot of callers have — and they’re totally right to be confused, since this is either entirely new to them if they’re just turning 65, or because every so often there’s one sudden big change (like this year) and I’m dealing with a number of people anywhere from their mid-70s to mid-90s. A lot of them at that age don’t like change, so they just try to shut it out. Plenty of them don’t mind change and don’t shut it out, of course, but I don’t seem to get those calls.

The end result of it all, though, is that I find myself in the same split-personality world I was in way back during my first office job right out of college, before I went into that almost-exclusive entertainment-related career: normal person by day, creative freak show by night. Bilingual and bipersona, to coin a phrase. The secret is being able to switch back and forth.