Excel Min If and Max If Functions

Unfortunately, the only way to do this is with array formulas, which are pretty intimidating beasts. Here is a simple sumif function:

=sum(if(((a1=b1:b5)),1,0))

Then hit ctrl+shift+enter (CSE: some call these CSE formulas). See here for more on array formulas.

Have a look at the attached file (“array_formulas“). It looks for matches between a1 and whatever’s in b. It sums the values from the matching rows but in the c column. Hard to explain in words, have a look at the attached.

What’s cool is that you can do this with an unlimited number of criteria and use max or min. Like this:

=max(if(((a1=b1:b5)),c1:c5,0))

=min(if(((a1=b1:b5)),c1:c5))

Note with min, you can’t set the FALSE condition of the If statement to 0 because 0 will always be the min!

Next version is to set the multiple criteria using * signs:

=max(if(((a1=b1:b5)*(a1=d1:d5)),c1:c5,0))

And can set or conditions using a + sign:

=max(if(((a1=b1:b5)+(a1=d1:d5)),c1:c5,0))

What these are really doing is giving you some database functionality in excel. If you work with datasets smaller than 50,000 rows or so the pain of putting something into a database isn’t worth the extra speed for searching. And excel’s function system is much more powerful than SQL for even moderately advanced analysis.

Excel gives you database query functionality with the pivot tables, but I find these a pain to work with and usually just build my own pivots with array formulas.

One big note of caution: array formulas are super duper resource hogs. Eventually you’ll bog your system down in endless calculations if you use them too much. Which I do all the time.

Knock, Knock

Here’s an interesting article about how the FBI came a-knockin’ on a company’s door one day.

Some things that I found interesting:

– having the FBI breathing down your neck is incredibly distracting. It’s no wonder companies in countries where arbirary law enforcement is the norm can’t get anything done. Serving your customers takes a complete back seat.
– If the FBI does come along and take your server, have a backup ready in case they never give it back. Prevents downtime!
– Wow, encryption works
– The fact that the FBI gave the server back a few days later, and went to some lengths to reinstall it, suggests that, perhaps, government employees actually want to do the right thing here. The culture of the civil service matters.
– The people who run this shop are clearly bible-thumping free speech fundamentalists, which is fine. The question is whether a lesser (more naiive?) ideologue might have gotten better treatment. Unlikely perhaps.

Where The Skills Are

Here’s a graph to think about:

My goodness!

It would be really interesting to see this broken down by industry, which I’ve had a quick look into but have been stymied by the BLS website for now. I’m sure the data is there but blogging is taking a firm back seat to studying until I’m past my exam later this week.

Baby boomers aren’t going to retire like their parents did because they can’t. And that’s very good for the economy because they’re a damn productive bunch for the most part.

I’ve forgotten where I saw this graph first as it’s been sitting in draft limbo for a few days…

Pencils, Pins and “Dope Tracks” — How Dead White Guys Run The Music Business

Specialization is one of the three or four absolutely fundamental concepts in economics. And there are some iconic discussions of its virtues: try Adam Smith’s pin factory story for one:

Those ten persons could make among them upwards of forty-eight thousand pins in a day… But if they had all wrought separately and independently… they certainly could not each of them have made twenty, perhaps not one pin in a day;

And how about Leonard E. Read’s famous I, Pencil story. “Who can make me?”, asks the Pencil of his readers. Trick question:

…not a single person on the face of this earth knows how to make me. This sounds fantastic, doesn’t it? Especially when it is realized that there are about one and one-half billion of my kind produced in the U.S.A. each year.

How can we make millions of pins and pencils when no person can make even one? Specialization: each person can make some part or machine that can make the final good.

Interesting enough, but rather dry stuff, which is why I delighted in reading this article about the music business. Who would have thought creative works would so easily succumb to improvements by division of labor and specialization?

Let’s start with a familiar player: the much-derided but hugely successful model of singer-not-songwriter:

Rihanna is often described as a “manufactured” pop star, because she doesn’t write her songs, but neither did Sinatra or Elvis. She embodies a song in the way an actor inhabits a role—and no one expects the actor to write the script. In the rock era, when the album was the standard unit of recorded music, listeners had ten or eleven songs to get to know the artist, but in the singles-oriented business of today the artist has only three or four minutes to put her personality across. The song must drip with attitude and swagger, or “swag,” and nobody delivers that better than Rihanna, even if a good deal of the swag originates with Ester Dean.

Who is Ester Dean? She’s one of the best hook writers (“top-liners”) in the business. Here’s a peek into her process:

After several minutes of nonsense singing, the song began to coalesce. Almost imperceptibly, the right words rooted themselves in the rhythm while melodies and harmonies emerged in Dean’s voice. Her voice isn’t hip-hop or rock or country or gospel or soul, exactly, but it could be any one of those. “I’ll come alive tonight,” she sang. Dancing now, Dean raised one arm in the air. After a few more minutes, the producers told her she could come back into the control room.

“See, I just go in there and scream and they fix it,” she said, emerging from the booth, looking elated, almost glowing.

“And they fix it”. There are more involved, you see:

Stargate went to work putting Dean’s wailings into traditional song structure. As is usually the case, Eriksen worked “the box”—the computer—using Avid’s Pro Tools editing program, while Hermansen critiqued the playbacks. Small colored rectangles, representing bits of Dean’s vocal, glowed on the computer screen, and Eriksen chopped and rearranged them, his fingers flying over the keys, frequently punching the space bar to listen to a playback, then rearranging some more. The studio’s sixty-four-channel professional mixing board, with its vast array of knobs and lights, which was installed when Roc the Mic Studios was constructed, only five years ago, sat idle, a relic of another age.

Within twenty minutes, Dean’s rhythmic utterances had been organized into an intro, a verse, a pre-chorus (or “pre”), a chorus, and an “outro”; all that was missing was a bridge. (Friday, the final day of the sessions, was reserved for making bridges.) Delaine, the engineer, who hadn’t said a word thus far, sat down at the computer and began tweaking the pitch of Dean’s vocal. Dean went back into the booth and added more words: “Give me life . . . touch me and I’ll come alive . . . I’ll come alive tonight . . .”

This is a high performing team that cranks out hits which then get picked up by established artists. They transact in a marketplace which, astonishingly for an industry obsessed with copyright, has poorly defined property rights:

The top-liner is usually a singer, too, and often provides the vocal for the demo, a working draft of the song. If the song is for a particular artist, the top-liner may sing the demo in that artist’s style. Sometimes producers send out tracks to more than one top-line writer, which can cause problems. In 2009, both Beyoncé and Kelly Clarkson had hits (Beyoncé’s “Halo,” which charted in April, and Clarkson’s “Already Gone,” which charted in August) that were created from the same track, by Ryan Tedder. Clarkson wrote her own top line, while Beyoncé shared a credit with Evan Bogart. Tedder had neglected to tell the artists that he was double-dipping, and when Clarkson heard “Halo” and realized what had happened she tried to stop “Already Gone” from being released as a single, because she feared the public would think she had copied Beyoncé’s hit. But nobody cared, or perhaps even noticed; “Already Gone” became just as big a hit.

It’s an interesting phenomenon that all of these various artists and musicians specialize increasingly narrow tasks. In every other industry this is called progress and the resulting products are inarguably better then their predecessors.

Can we say, then, that the music of today is in some way objectively superior to that of the past? As in the way that bridges and sprinters are better? If the comparison is possible, surely you have to say yes.

Still In The Woods

I go straight to Calculated Risk for all my ‘real’ economic news, by which I mean the data and basic commentary. Their graphs are outstanding.

And those graphs are telling all kinds of still-nasty stories about the downturn we are still in.

Look at the housing starts:

Hopefully it’s becoming clear that the economic story is not about ‘we built too many houses’. It’s about lots of stuff (debt deleveraging, etc). Have a look at this. The single family housing starts are down, sure, but so are owner built and built for rent sales, which didn’t really pick up in the boom.

And I like the graph below because I’ve long had the impression that most apartment buildings were built in the 70s and 80s. And it’s true!*

When people talk about “infrastructure spending” think about all of the low hanging fruit that’s already been picked.

Let’s build some highways. Got ’em.

Let’s build some airports. Got them, too.

Ok, how about apartment buildings? Done, and, in any case, NIMBY!

Replacing these things are going to be much less accretive to growth than building them in the first place.

And of course the real story is employment.

*My wife and I recently moved and had trouble finding a place that would both let our two dogs in and was built in the last 10 years.

Mash of Links

They made their own clothes and built their own tools and worked on tiny farms. Lots of economic activity has moved from the home to the stock market since 1812. Via MR.

Next, I quote Yglesias, whose blog I’m really enjoying:

One of the most pernicious misunderstandings out there is that the prosperity of the United States in the postwar years indicates that there’s some meaningful alternative strategy for economic growth that doesn’t involve increased education and human capital. This idea is driven by the sense that back in the proverbial day there were great middle-class job opportunities out there for people who hadn’t gone to college, and so maybe what we really need to do is bring that kind of economy back…

America was far and away the best-educated country in the world during the postwar years

Great graph at the link.

My other favorite new blog is Science-Based Medicine. Here’s an excellent fact-filled rant:

It has been a stunning triumph of marketing and propaganda that many people believe that treatments that are ¡§natural¡¨ are somehow magically safe and effective (an error in logic known as the naturalistic fallacy). There is now widespread belief that herbal remedies are not drugs or chemicals because they are natural.

The other major fallacy spread by the ¡§natural remedy¡¨ industry is that if a product has been used for a long time (hundreds or thousands of years), then it must also be safe and effective because it has stood the test of time (this fallacy is referred to as the argument from antiquity)…

This first came to world-wide attention in the 1990s when a group of Belgian women who were taking Chinese herbs as part of a weight loss regimen developed end-stage kidney failure. The syndrome became known as Chinese Herbs Nephropathy, and it was soon discovered that aristolochic acid was likely the culprit…

It is also interesting to consider how aristolochia came to be used to aid in the birthing process – one of its most popular uses and the source of its name, which means “noble birth” in Greek. As with the traditional use of many herbs, it appears to be based entirely on sympathetic magic – the belief that a plant will be useful for an indication based upon what the plant looks like. In this case the flower of many aristolochia species looks like a birthing womb. The rest is anecdote, placebo effect, and confirmation bias – but no science.

Lest we get too self-congratulatory, scientific medicine isn’t always so scientific either.

Cringely on Best Buy, a company doomed to die:

Shopping at Best Buy last Christmas was a joke. Best Buy corporate was upset people were using their smart phones to do price comparisons in the stores. Think about that: Best Buy was upset that their customers were too smart, that they actually used the sort of technology Best Buy purported to sell. Worst of all, Best Buy completely missed the simple point that their prices were too high.

Review of *Jiro Dreams of Sushi*

My family used to go on long vacations when I was a kid. One summer we found ourselves in a little town out West watching some kind of small time rodeo/outdoor fair. We ate lunch watching a country band.

Culturally, we weren’t normally into that kind of thing and I remember asking my mom what the deal was. Her response: “They’re live and good at what they do. That’s always interesting to see.”

That about sums up what’s great about this movie. Jiro is good at what he does, maybe the best in the world. His restaurant is a case study in narrow but deep achievement. Only Sushi? No bathroom? 10 seats? Underground strip mall? Three Michelin Stars.

The secret is a workaholic monomania. Sushi is raw fish, rice and wasabi. Simple enough. You’ll be surprised, perhaps, to learn that Jiro’s apprentices progress on a geologic time scale: months of squeezing towels to start, 10 years before you’re allowed to cook the eggs.

Tyler Cowen sees some kind of employer cartel among Sushi Chefs: extorting labour from skilled apprentices for years with the promise of trade secrets at the end.

I prefer the explanation given in the opening sequence. There are no secrets, says Jiro’s son and heir Yoshikazu, being a great sushi chef simply requires enough tolerance for mind-numbing routine to never, ever lose focus. Squeezing towels? The apprentices might as well just train by staring contest.

Deep skills can be learned through vaguely related, trivial tasks, as we know:


Jiro isn’t rushed. Like all masters of craft, he has been doing it forever (75 years) and feels he’s learning all the time. He’s released many apprentices to open their own restaurants, even his younger son (there’s only room for one dauphin). Anyway, the greatest masters never stop being apprentices, Jiro included. That lesson, too, takes time to learn.

The length of this apprenticeship stands in meta irony to the production of the film itself. The director, David Gelb, is only 28. I’m no cinophile so this comment sits a bit awkwardly in my mind, but I really enjoyed the camerawork in the film. The NYT reminds me of a particularly delightful scene, and it seems our young gun got some help:

Toward the end of the documentary, which comes out in New York on March 9, there is a spellbinding “concerto of sushi” in which nearly every item on the omakase menu at Sukiyabashi Jiro, Mr. Ono’s sliver of a restaurant, is captured in a loving close-up while Mozart soars on the soundtrack.

It looks simple enough — point a camera at the fish and start rolling — but it wasn’t.

Mr. Ono’s standards are so obsessively high that he wanted to shoot each handmade masterwork at “the supreme moment of deliciousness,” Mr. Gelb said, which happened to be the precise instant of its creation.

“It was important to Jiro that the sushi looked the way it was supposed to,” Mr. Gelb said.

This went beyond the fresh glistening hue of the fish. In the chef’s eyes, the scene had to incorporate even the gentle settling and merging of the fish and rice and sauce as each piece was placed onto a plate.

Take too long, Mr. Gelb said, and “we would’ve lost the soft landing, because it had already landed.”

The film touches on some of those lessons that are simple but hold enduring appeal. #1: the greatest are incalculably greater at their craft than the rest.

#2: But they don’t get that way by magic. There is not a single shot of Jiro’s home or life outside of the restaurant. And that is probably because, not being sushi, they don’t matter much to him.