Microsoft Office Tutorials and References

In Depth Information

**Creating and Editing Formulas**

The most common of all formulas—so useful it gets its own button on both the Home tab

and the Formulas tab in Excel—is AutoSum. If you select a cell (empty or not) and click the

AutoSum button, Excel inserts the =SUM formula. If Excel detects numbers in the column

above or the row to the left of the active cell (even if some blank cells separate the range

from the SUM function), it ills in that range as the argument. You can accept the default

argument or adjust it as needed.

Click the AutoSum arrow to choose from a menu of other functions—Average, Count

Numbers, Max, and Min—to automatically ill in those functions.

INSIDE OUT
Use names in place of formulas

Although names are most commonly used as cell or range references (as we described

earlier in this chapter in “Using Cell Addresses and Range Names” on page 349), you can

also assign a name to a formula. The named formula can refer to cells in the current

worksheet or workbook, or it can stand on its own. To assign a name to a formula, click

the Formulas tab and click Define Name. In the New Name dialog box, enter a name,

and then type the formula itself in the Refers To box. For example, you could create the

name End_Of_Next_Month and apply it to the formula =EOMONTH(TODAY(),1). That

formula checks today’s date and then returns the last day of the next month. To use

the name as part of a formula, click the Formulas tab, click Use In Formula, and select

End_Of_Next_Month from the list of available names.

Making Comparisons

Comparison operators allow you to compare two values and return a logical result of TRUE

or FALSE. This type of comparison is usually combined with a logical function such as IF,

which allows you to perform one calculation or return a specific value if the result of the

logical test is TRUE and do something different if the result is FALSE. Table 11-3 lists all

comparison operators that Excel supports.

Table 11-3 Comparison Operators Supported in Excel Formulas

Operator

Usage

=

Equal to (A1=B1, C3=0)

>

Greater than (A1>B1, C3>0)

<

Less than (A1<B1)

>=

Greater than or equal to (A1>=B1)

<=

Less than or equal to (A1<=B1)

<>

Not equal to (A1<>B1)