Cheshire cats, Excel and Frequency illusion

One day Enrico came to a fork... no, I'm not trying to be the new Lewis Carroll. In fairness, I really love the fact this author was not only a writer but even a mathematician and a logician. Perhaps, if he had been born in the 20th century like me, he could have been an Excel geek too.

What I was meant to say is we can't know all little details about Excel, as already mentioned in my previous article here, so it might happen that Google is a destination to look either for the syntax of a formula or more generally how to make the best use of any Excel feature. Usually, this story ends with me finding what I was looking for and going back to my workings. But that would make this article dramatically dull and short.

However, in some cases, there are two situations that might sound like I get stuck, but they're just two different ways of learning new things.

If you don't know what to ask precisely, anywhere can be your destination.

Sometimes it turns out that while following the white rabbit tail of an Excel Formula, I end up talking nonsense to a Cheshire cat in a tree, or, in layman's terms, I ask a question to Google and find so many different valuable inspiration sources I can't stop reading.

It might sound like a time-consuming activity as the infinite scrolling on Facebook, however, it can even become so valuable and exciting that it is definitely worth a try. That's what happened, for instance, when I had been looking for SUMPRODUCT.

SUMPRODUCT, per se, has a pretty straightforward and simple task, that is multiplying ranges and/or arrays together and returning the sum of products. However, it can be used as a more versatile function to count and sum, like COUNTIFS and SUMIFS.

I confess I've only used it to calculate weighted averages so far, but there's definitely more under the hood. For instance, let's assume I have a small portfolio and I want to calculate the duration of the portfolio. What I have always done has been multiplying the ranges of Duration and MV and dividing it by the total of MV. I'm perfectly aware that this portfolio duration calculation is quite accurate if and when the yield curve is flat, but it has often fitted my high-level information purposes. On the other hand, if I had wanted any specific duration of a subgroup of this portfolio, I should have had to either manually tweak the formula or create a different table with the detail, those with rating AA highlighted in light blue in below example.

What SUMPRODUCT can do here is act as a SUMIFS function, just as a reference I have calculated SUMIFS in cell G6 and below in cell G9 I have done the same with SUMPRODUCT, getting the same result.

The trick, used here and in more advanced Excel formulas, is adding a double negation [--] to transform the TRUE and FALSE values in the range into 1's and 0's [see the detailed steps in the rectangle]. So, simply put, the formula in this situation multiplies the MV with 0's and 1's resulting in a numerical amount, instead of multiplying by TRUE and FALSE, that would always get a result of 0.

What gets even better is that, including the embedded condition to the formula, I can even calculate in this simple example the duration of the sub-group as a division between two SUMPRODUCT functions:

=SUMPRODUCT(B4:B10,C4:C10,--(D4:D10=H9))/SUMPRODUCT(C4:C10,--(D4:D10=H9))

I may sound a bit too excited, but trust me on this: it is definitely worth a try.

Stumbling on some obscure Function and on your cognitive bias.

It can happen, otherwise, that I have been using COUNTIFS to create a frequency distribution because I didn't know there was a FREQUENCY formula too. After learning about it and testing it, it actually popped up three or four times in my daily readings. I got totally caught by the Baader-Meinhof phenomenon, also known, ironically, as the frequency illusion. Basically, this is a psychological effect that causes newly-discovered concepts to seem to appear in an observer's life with increased frequency (again this word...). If you have the chance, have a look at this video from the Nerdwriter to know more about this phenomenon, otherwise just imagine that your brain can play you tricks because of selective attention and because it suggests patterns when none actually exists.

As I was mentioning above, when I wanted to find and show a distribution of events, I used to make the best use of COUNTIFS and just setting the range of values with the classic greater than ">", less than "<", etc. for set values. Here on the right, I wanted to show the spread against interest rate benchmark of few securities and how it was allocated in four different bins. In this specific case, it was easy to spot the riskier one with a spread greater than 1000bps, but imagine a portfolio with several securities: a COUNTIFS and perhaps conditional formatting of the data set would make a good visual result.

Now getting to FREQUENCY: it would sound obvious generating frequency distributions using this function (nomen omen, the Latins said). In fact, it counts how often values occur in a set of data. This formula has some unusual characteristics, though: there's the need to enter it as an array formula (CTRL + SHIFT + ENTER after typing it) and it will actually return a vertical array of numbers that represent frequencies. The formula will show the curly brackets { } to show that this is not a normal formula, but one that works with arrays. There's also the need to create an extra cell to make room for either the smallest or the biggest bin, the data below 333 in this example. The FREQUENCY function actually returns values that are greater than one bin and less than or equal to the higher bin.

In fairness, I'm not a huge fan of array formulas (SUMPRODUCT, despite working with arrays, doesn't need the braces for instance) and all the above mentioned unusual situations will make me stick to keep using COUNTIFS when playing with frequency distributions. Nonetheless, I admit it has been good experimenting something new, looking for more advanced ways to reach the goals of my spreadsheets, sometimes with more flexibility, sometimes not.

In the case of SUMPRODUCT, I've been seduced and I'll get to use it more often. On the other hand, FREQUENCY and his illusion haven't made me fall for them, so I'll put them aside and keep working and sometimes getting lost again, or fighting with my personal Baader Meinhof*.

How about you? Have you ever got lost while googling some Excel question? Or have you been surprised by Baader-Meinhof? Any comments? Feel free to add your thoughts here below!

* The name of the phenomenon is named after a terrorist organization active during the 1970s. The person who conceived the name "Baader-Meinhof phenomenon" named the effect after hearing about the group's existence and later running into frequent mentions of their activities.

Disclaimer: I'm not affiliated with Microsoft in any way. Opinions are my own and not the views of anyone else. No spreadsheet has been harmed in the writing of this article.