Useful spreadsheets from a single formula: “Bucket brigade” formulas in Excel

Check out the “quickies” at http://www.decisionspreadsheets.com/free-spreadsheets/, useful spreadsheets each built using a single formula and AutoFill. Compute your checking balance, predict investment growth, calculate loan payments or figure the present value of future income. This is why I love AutoFill (see last post). Write one good formula and AutoFill it down the column to build a powerful spreadsheet in no time.

Here’s how you do investment growth. Say you have $10,000 invested and you expect to earn 4% on it. You want to know how much you’ll have each year in the future. Put the years in the first column. Remember you can do this using AutoFill. In the second column put the amount $10,000 for this year. Below that enter your one formula, by clicking, to figure out how much you’ll have the following year. Start with “=”, then the amount $10,000 that you have from the previous year (click on it). Add to that (“+”) the interest you’ll earn, which is $10,000 (click on it) times 4% (type “ * .04 “). Believe me, this is much easier to do than to explain in writing! You’ll get a formula that looks something like the one pictured. You’ll get an answer of $10,400, which makes sense.

Now the magic happens. Grab the Fill Handle in the lower right corner of the cell with the formula and drag it down as far as you want to go. Now you’ve predicted the amount you’ll have every year in the future.

Click on some of the formulas you created using AutoFill. You’ll see that each makes use of the answer in the line just above it. Each formula is doing a little of the work and then passing it on to the next formula below it, all working together to produce the answer at the bottom of the column. It’s like an old-fashioned bucket brigade, fire fighters who pass buckets of water down a line to the fire.

There are several advantages of this “bucket brigade” approach:

  • Formulas are simpler to write and easier to understand than those that give you the answer in one step.
  • You know that the formulas are doing exactly the same thing in every row.
  • Seeing intermediate values helps you to check that your spreadsheet is working properly.
  • The spreadsheet is easy to modify, since you are using only a single, simple formula that you can change and re-clone.
  • Many complex problems can be solved only in this way.

The computer may be doing a lot of work, but that’s OK if it makes things easier for you.

I’d love to hear about how you’ve used this powerful technique.

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