Set Intersection

I caught up with an old discussion on Chandoo’s site. The basic question was, “How can I find the number of numbers that intersect in ranges Start1..End1 and Start2..End2?” I had a similar challenge a few months ago in Excel and resorted to Excel’s Intersect method, resulting in this one-liner:

Function IntersectCount(S1 As Long, E1 As Long, S2 As Long, E2 As Long) As Long
' works on whole numbers only,
' parms must be in range [1..max rows allowed by your version of excel]
  IntersectCount = Application.Intersect(Range("A" & E1 & ":A" & S1), Range("A" & E2 & ":A" & S2)).Count
End Function

This works great for non-negative integer ranges with upper bounds < 2^20 (Excel 2007 and later) (great for everyday integers and dates!) I think Rick Rothstein posted a related method, but it did not produce a count.


Ideas for New Posts — Part the First

“I am not an actuary, but I play one on TV”.

I like to say this, even though it’s not true (the TV part). But I get some things about data that actuaries like to dig into that end up being fun challenges in Excel. One of the biggest learning experiences for me over the last few years has been “how to best query, organize, and analyze my_metric in the context of ‘root year’, calendar year, and ‘dimension x’ ?”

I have recently migrated my core preference from pivot table to (in Excel 2007 and hopefully later) Excel Tables, the entities formerly known as lists. Tables and structured references are not only viable replacements for the former, they have some syntactical qualities that make them superior. In the right context of course.

So, in keeping with the ambiguity of the title, “stay tuned” hah ha.

p.s. some typo fixes

Why Pie Charts are (Usually) a Bad Idea (Part 2)

This is part 2 on this topic. Click here for Part 1.

I concede, a pie can be an acceptable way to get a message across as long as the message is simple. There are other limitations as well.

Read more of this post

Why Pie Charts are (Usually) a Bad Idea (Part 1)

My mentors have already written well on this topic. Here, I share some bits that resonated with me.

Pie charts are among the most sought-after and brandished charts in the corporate world, and, I have noticed, in the marketing materials of information graphic software publishers as well.

However, among serious information graphic designers the overwhelming opinion about pie charts is they are at best a poor choice. The reasons for this are amply documented in the in the blogosphere, and someday I will post references.

Meanwhile, I find the persistent popularity of pie charts to be profoundly strange. I will talk about this in two posts.

Read more of this post