Excel Formulas: Scalar Functions

Excel has more than 300 functions you can invoke in your formulas, that do a great variety of calculations and other tasks: trigonometric functions, Bessel functions, statistics, string operations, date & time conversions, and table lookups, for example. Most of these take one or more input values (cell addresses or range addresses) and produce one output. A later post will examine some that produce multiple results.

Here, we introduce the Insert Function dialog. The Menu way to get it is Insert/Function..., but the easier way is to click the fx button right next to the Formula Bar in Excel 2002 and later.


Doing so opens up this dialog box. The exact locations of the elements depend on the Excel version. This is Excel 2003.

The "select a category" dropdown has quite a list of sections; there are eight initially, plus "All" and "Most Recently Used". The last category, below "User Defined" is "Engineering", and only shows up if you have the Data Analysis Toolpak enabled. The category "User Defined" only shows up if you have a VBA Add-in containing functions of your own—a more advanced skill!

In the "Financial" category, this example will show how the PMT function can be used to calculate a mortgage payment.

This shows the formula almost finished, just before adding the closing parenthesis. C5 is the monthly Rate (cell C5 contains the formula =C4/12), C6 is the number of months, and C3 is the principal, called pv for Present Value; it is negative because a loan is a liability, not an asset. The fv (Future Value) and type (Pre- or post-payment) parameters are optional, and we don't use them here.

Upon adding the closing paren and pressing Enter, we find the payment calculated. Excel took care of using a Currency format.

Such a mini-worksheet lets you test scenarios, for example different rates or terms (e.g. 15y or 180mo) .