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)
Search JabSto ::

Custom Search