Save time and space with Array Formulas

Array Formulas represent one of the least used of Microsoft Excel’s many robust features.  I’m not sure if most business people even know about them.  But they should.  And after this you will not be amongst the naive.

While a typical formula works on one cell at a time, array formulas provide a way to work with a range (or array) of data all at once, performing in one step what you would normally do in several.

Here’s a perfect example from a client:

Figure 1: Sales Data for 2009 and 2010

The Company sells custom phone products at kiosks in malls across the United States.  The owner has just closed the books on 2010 and wants to know how sales compared with 2009.  Specifically, she’d like to know the average growth amongst the stores.  She has collected the data shown in Figure 1, comparing 2009 versus 2010 sales data for all of her stores.

To get an average increase across all of the stores, most would do what?  Well, most would probably add another column (D) to calculate the difference (Col. C-Col. B).  Then take the average (using the average formula) of that new column.  Not bad.  But you can do all of that in one formula, an array formula:

=average(C2:C6-B2:B6)

Now, HERE IS THE TRICK:

Array formulas must be entered a little differently than regular formulas, so that Microsoft Excel doesn’t think you’re referring to individual cells.  Instead of just hitting the <Enter> key, you must hold down the <Shift> <Ctrl> keys while hitting the <Enter> key.  When done correctly, you will see fancy brackets around the entire formula:

{=average(C2:C6-B2:B6)}

And the cell will return the same figure you get with the other, longer method.  Possibly as importantly, this method will also speed up the processing of the worksheet, something that can be a problem if your datasets get large or unwieldy.

Three Keys to Great Leadership

There is much written about leadership.  What makes a great leader and what doesn’t.  Whether or not they are born or created.  On and on.  And being an avid reader (See my Reading List) and one who is always seeking to learn and grow, I’ve read my good share of them.  The more I read, the more I believe that we complicate things.  Really, good (and bad) leadership comes down to success (or failure) in three areas:

1. Clearly Defined Expectations
2. Tools for Success
3. Means for Reinforcement

Let me explain further. Read more of this post

Algebra Again?!?

I have a loan that I totally forgot about.  The lender, although very understanding, just called and asked when we’d be making the next payment.  Unfortunately, I can’t locate the original paperwork, if we ever had any.  All I have is record of one payment made for the interest accrued to that point.  How do I figure out what the interest rate on the loan is, then book and track (create amortization table) it so this doesn’t happen to us again?

Once again, my high school Algebra teacher is yelling in my ear. Read more of this post