Excel formulas: Easier to write than to read

Have you ever tried to understand an Excel spreadsheet by looking at the formulas? It’s nearly impossible to figure out. (Hint: If you double click on the cell holding the formula, if will highlight the cells being used. Press Esc when you’re finished looking at it.) I love Excel, but it’s really obscure to read.

Surprisingly, it’s easier to write an Excel formula than it is to read it. That’s because you simply find the numbers you want to use in the formula and click on them.

I recommend using descriptive formulas to develop your spreadsheets. This is especially helpful for bucket brigade formulas (see my previous post). You can’t enter descriptive formulas directly into Excel, but they guide you as you enter your formula and serve as documentation of what you’ve done.

This is easiest to see from an example. Say you’re building a checkbook register. You’ve entered all of your checks and deposits, shown in blue. Now you want to write a single formula that will compute the balance for each row. Since the balance depends on the previous balance, this is a natural for a bucket brigade formula. Enter one good formula in cell G3 and use it for all the balances.

Think about what you want the formula to do with the numbers it uses. Although row 3 has only a check, think ahead to the rows with deposits. Your formula should work whether you’ve written a check or made a deposit.

You want to compute the balance by starting with the previous balance and subtracting the check or adding the deposit. Notice that adding or subtracting a blank cell doesn’t change anything, since Excel sees it as zero. So it’s OK to subtract the check and add the deposit every time.

Here’s your descriptive formula:

=Balance previous – Check + Deposit.

This tells you that, no matter which row you put the formula in, it uses the Check and Deposit numbers from that same row and the Balance from the row just above.

Enter this formula in cell G3 by clicking. Start with “=”. Click on the indicated cells, even if they’re empty, and type the arithmetical operations between them.

The formula in G3 should now read:

=G2 – E3 + F3.

Press Enter to accept it. Now grab the fill handle in the lower right corner of G3 and drag it down as many rows as you wish. This gives you a check register with just one formula. Check it to be sure that it did what it should, whether you have a check or a deposit. You can find a check register spreadsheet like this at http://www.decisionspreadsheets.com/free-spreadsheets/ under Quickies.

You can usually write descriptive formulas in your head, but I recommend writing out the more complex ones. It’s a clear documentation of what you’ve done and it makes it easy to use the formulas anywhere else.

What techniques do you use to write formulas in Excel?

Leave a reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s