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?

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