Naming names: Making spreadsheet inputs play nice

One big difference between the spreadsheets that decision makers use and those that bean counters use is the desire and ability to play “what-if.” That means you’ll have inputs that you’ll want to change to see how that affects things. These are unknowns either because they represent a choice you have to make or some factor outside your control.

Take an example. A couple of posts back, the one about bucket brigade formulas, I showed a spreadsheet to figure how much money you’d have each year if you started with $10,000 and it grew at 4% each year. You might ask, “What if I invested $20,000? What if it grew only 3% per year?” Make these kinds of numbers inputs so you can change them and see what happens. You can control the amount you invest, so try different amounts and see what you’ll get. You can’t control what your investments pay, so try different amounts and visualize possible future outcomes depending on luck.

Here you see a spreadsheet to do that. The inputs are on the top. Naming your inputs makes them play nice, as you’ll see. Here’s how to do it. Select the numbers and their descriptions (cells A1:B2 in this example). Then from the Insert menu, select Name > Create… You’ll get a dialog box asking if the names are on the left, which they are, so click OK. Do this before you write any formulas.

Now when you write your formulas by clicking, you’ll automatically get the names instead of the cell numbers. This makes your formula easier to read and interpret. Even better, when you AutoFill the formula down the column, your formulas won’t get confused. They’ll always use the right input. (If you want to get technical, Excel makes all the named inputs absolute references so you don’t have to. See http://theexcelphile.wordpress.com/2012/10/14/absolute-relative-referencing/) Notice that the AutoFilled formula in B7 does exactly what you want it to do.

Now you’re ready to play what-if. Change the input numbers in cells B2 or B3 and watch the results.

What kinds of inputs have you used for your what-if spreadsheets?

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?

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.

Send in the clones: AutoFill in Excel

AutoFill is absolutely my favorite Excel feature. It’s because I’m lazy. I can fill a couple cells and then use AutoFill to turn it into a big, useful spreadsheet.

You can use it for header rows or columns. Say you want to number your rows 1, 2, 3,… You can look at this and figure out what comes next, and so can Excel. Put 1 in the first cell and 2 in the one below it. Then select both cells and move your cursor to the lower right corner until you see a fill handle. It might look like a plus sign or a square. Drag it down and Excel fills in 3, 4, 5, and so on, as far as you want to go. You can use AutoFill for less obvious sequences, such as times, days of the week, dates and text-number mixes like Topic1, Topic2,…  or 1st Phase, 2nd Phase,…

I recommend always giving it at least two cells to work with. Excel makes its best guess as to what you want, but it doesn’t always do what you expect. Even if you want all the cells to be the same you may or may not get it if you give it just one cell to start from.

This is convenient, but the real power comes from using AutoFill with formulas. Excel copies the formula and adjusts it for each new location.

Check out autofill.xls from http://www.decisionspreadsheets.com/book-spreadsheets/, pictured here. It’s a list of orders. One formula computes the sales tax and another takes the total for each order. Let’s look at that total. You can write that formula by clicking on the cells you want to add, with plus signs in between. Just think and click, “=436.19 + $6.73 + $32.71” and you’ll get the formula shown. Now grab the fill handle for that cell and drag it down. It automatically calculates the sum for all the other orders.

I’m astonished how little attention standard Excel books pay to this powerful technique. I have one popular Excel book, more than 800 pages long, that devotes only one paragraph to the topic, and then just mentions it as a way to get a series of incremental values, something that you can do easily without formulas. Another popular book calls it “quirky” and doesn’t even mention that you can use it for formulas. It makes me wonder what other essential decision making techniques are out there that we don’t hear about.

Do you know of any other hidden powerful decision making features of Excel?

Learning to function in Excel

Hooked by your credit cardI’ve been writing about Microsoft Excel as a great tool for decision makers. You need more than graphs of static data. You need to think about the future and balance priorities. You need to build custom spreadsheets around the hard questions you have to answer.

You need to write your own formulas. Excel can help you with that. They’ve packaged some of the most useful formulas for you as functions. You’ve already used one of the functions if you’ve ever used the summation button to total a column. When you do, you get something like =SUM(B2:B15). Sum is a function. It’s followed by stuff in parentheses, called the arguments, that tell the function what to use. Here it says to sum everything in cells B2 through B15. The equal sign tells Excel that this is a formula you want it to compute.

Here’s an example of using a function. Say you have a credit card balance of $2000 that you’d like to pay off over the next year. The interest rate is 18% or 1.5% per month. (No wonder you want to pay it off!) How much should you pay each month? There’s a function for that. PMT can answer this question as well as figuring such things as mortgage payments. The arguments are the interest rate, the number of payments and the starting amount. There are also two optional arguments that you don’t need here.

We’ll do this in months, since we’re making a monthly payment. The arguments are 1.5% interest rate, 12 months of payments for the year, $2000 owing. As far as percentages, you can enter it as 1.5% (yes, with the “%”) or as 0.015. Here’s the formula:

=PMT(1.5%, 12, 2000)

Enter this in some cell in Excel and you get $183.36 as the amount you need to pay each month. You might get this answer in parentheses or red, meaning negative, but that’s just indicating that you’re paying it out rather than receiving it.

There are a lot of other useful financial functions, as well as specialized functions for mathematics, statistics and engineering. There are functions for working with dates or text, searching data and checking conditions. You can browse the functions by clicking the fx button next to the formula bar.

Here are some of my favorite functions.

SUM, AVERAGE, MIN and MAX make sense out of a list of numbers.

IF, SUMIF and COUNTIF give you answers that depend on conditions anywhere in the spreadsheet.

SUMPRODUCT gives you a weighted average.

RAND gives you random numbers, which lets you model all those things you don’t know, for visualizing possible futures or doing risk assessment.

What are your favorite Excel functions?