Working with Functions and Formulas


Functions in Excel do most of the heavy work when it comes to complicated operations. Luckily, you can put away your trigonometric calculator and relax, because Excel has the know-how you need and the support to help you use it.

Checking Out the Formula Bar

You ll use the Formula Bar as you enter and edit Excel formulas and functions. The following graphic shows you the different parts of the bar:

click to expand

The Name box displays the location of the cell that s currently selected. You can use the Name box to move to other cells or select named ranges. The Cancel and Enter buttons abandon or accept the function you ve entered, and the Insert Function button displays the Insert Function dialog box so that you can insert it in the function box.

Deconstructing Formulas

A formula is an equation that carries out a particular operation on specified values. You ll use formulas to calculate sales projections, estimate taxes, average your expenses, total your income, and tally your inventory, among other things. A function is a preset formula that carries out a specific kind of operation. For example, you use the =SUM function to add a column of numbers ; you use =AVERAGE to display the average of a group of cell values. Excel functions always appear in this form:

= FUNCTION ( argument, argument )

Here s a step-by-step explanation:

  • The equals sign (=) tells Excel that what follows is a function. You always need to use the equals sign, or Excel won t know what to do with the data you enter and will display an error.

  • The word FUNCTION above is where the actual name of the function appears. The function might be SUM, AVERAGE, IF, COUNT, MAX, or something else.

  • The ( argument, argument ) text represents the part of the formula in which you tell Excel which values to use. Cell addresses are commonly used as arguments, but you can also use text, TRUE or FALSE, or numbers. For cases in which you ll need complex formulas (which is beyond our intention here), you can create nested formulas by including other formulas and functions as the arguments.

Entering a Formula

The process for creating a simple formula that uses a function is straightforward:

  1. Click in the cell that will contain the function you want to use.

  2. Click the Insert Function tool in the Formula bar. The Insert Function dialog box appears (as shown in Figure 7-3). Choose the function you want to use and click OK.

    click to expand
    Figure 7-3: The Insert Function dialog box gives you three ways to find the function you need.

  3. Choose the cells you want to include in the formula by entering them in the Function Arguments dialog box. Click OK to return to the worksheet.

start sidebar
Aha! ”When You re Comfortable with Formulas

The fastest way to enter a formula, assuming you know what you re doing, is to type it in the Formula Bar. Begin with an equals sign (=); then enter the function name followed by an open parenthesis, the range of cells separated by a colon (:), and a closing parenthesis. Press Enter to perform the calculation. One even faster way to get formulas into your spreadsheet is to copy them; just select the cell containing the formula, press Ctrl+C, and paste the formula (Ctrl+V) at the new location. Excel will do the math and change the cell references relative to the new position ”and the formula should function (no pun intended) just the way you want it to.

end sidebar
 

Special Formula Features

As your experience with Excel and with formulas begins to grow, there are a few features that will help you create accurate calculations. Remember to try the following features when you find yourself building more complex operations:

  • The AutoSum tool (available on the Standard toolbar) allows you to enter common functions by selecting them from the AutoSum menu. You can choose Sum, Average, Count, Max, or Min, or click More Functions to display the Insert Function dialog box and use the Function Wizard to find the function you need.

  • You can have Excel check your formulas for you to make sure there are no mistakes. To start the checker, choose Error Checking from the Tools menu. Excel will display any errors found in the formulas in your worksheet, and you can make corrections as needed in the formula bar.

  • Whenever an error occurs in a formula in the spreadsheet, the Trace Error button appears beside the formula in the cell. You can click the Trace Error button to choose options that will help you run down and fix the error in the formula.

  • You can watch a formula and keep an eye on its results by using the Watch Window feature. Start by selecting the cells with the formulas you want to watch, then point to Formula Auditing on the Tools menu and choose Show Watch Window. Next, click Add Watch, and then click Add to save the selected cells as the range you want to watch. You can then reposition and resize the Watch Window to keep it open in your workspace while you continue to work with the current worksheet. When any of the values or formulas in the watch range change, the value change is reflected in the Watch Window.




Faster Smarter Microsoft Office System
Faster Smarter Microsoft Office System -- 2003 Edition
ISBN: 0735619212
EAN: 2147483647
Year: 2003
Pages: 238

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net