We held the Fall meeting of the Performance Measurement Forum this week in Dallas, Texas; as usual, there was great discussion and much to learn. The highlight may have been a tour of the new Dallas Cowboys stadium.
One of the things I learned was a technique in Excel, which I suspect most people aren’t aware of. Let’s say you want to calculate the quarterly return in Excel based on monthly returns of 1%, 2%, and 3 percent. If you’re like me, you would add one to each of these numbers, multiply them together, and then subtract one; something like:
While this clearly works, it requires you to add an additional column, which at times might not be convenient. Here’s where the neat trick comes in:
- key in “=product(“
- select the cells you want to multiply together (in our case, the ones with the three monthly returns)
- key in “+1)-1”
- and then, instead hitting “enter,”
hold down the “shift” and “ctrl” keys and then hit “enter.”
Try it…it works! Pretty cool, I think. This process creates an “array,” which I had never heard of before. There are apparently other neat tricks you can do with this, too.
I credit my friend Neil Riddles for educating me on this.