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 for 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’ve had to wrestle with recently is a database showing the various insurance policies people have 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 is 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:

=SUM([Range])
=MAX([Range])
=MIN([Cell1],[Cell2],[Cell3],...[Cellx])

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:

=MAX(IF($B$1:$B$525=B2,IF($D$1:$D:$D$525=D2,$N$1:$N$525))

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.

In real life, not so much. 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.

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.

Onward!

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.