Performance Perspectives Blog

A cool tool!

by | Dec 3, 2010

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.

Free Subscription!

The Journal of Performance Measurement

The Performance Measurement Resource.

Click to Subscribe