Excel Formulas and Functions For Dummies. Bluttman Ken

an absolute cell address so that it does not change when the formula is copied.

      In an absolute cell reference, a dollar sign ($) precedes both the column letter and the row number. You can also have a mixed reference in which the column is absolute and the row is relative, or vice versa. To create a mixed reference, you use the dollar sign in front of just the column letter or row number. Here are some examples:

Copying formulas with the fill handle

      As long as I’m on the subject of copying formulas around, take a look at the fill handle. You’re gonna love this one! The fill handle is a quick way to copy the contents of a cell to other cells with just a single click and drag.

      The active cell always has a little square box in the lower-right side of its border. That is the fill handle. When you move the mouse pointer over the fill handle, the mouse pointer changes shape. If you click and hold the mouse button, you can drag up, down, or across over other cells. When you let go of the mouse button, the contents of the active cell automatically copy to the cells you dragged over.

A picture is worth a thousand words, so take a look at Figure 1-17, which shows a worksheet that adds some numbers. Cell E4 has this formula: =B4 + C4 + D4. This formula needs to be placed in cells E5 through E15. Look closely at cell E4. The mouse pointer is over the fill handle, and it has changed to what looks like a small, black plus sign. I am about to use the fill handle to drag that formula to the other cells. Clicking and holding the left mouse button down and then dragging down to E15 does the trick.

       Figure 1-17: Getting ready to drag the formula down.

Figure 1-18 shows what the worksheet looks like after the fill handle is used to get the formula into all the cells. This is a real time saver. Also, you can see that the formula in each cell of column E correctly references the cells to its left. This is the intention of using relative referencing. For example, the formula in cell E15 ended up with this formula: =B15 + C15 + D15.

       Figure 1-18: Populating cells with a formula by using the fill handle.

Assembling formulas the right way

      There’s a saying in the computer business: Garbage in, garbage out. And that applies to how formulas are put together. If a formula is constructed the wrong way, it returns an incorrect result or an error.

      Two types of errors can occur in formulas. In one type, Excel can calculate the formula, but the result is wrong. In the other type, Excel is not able to calculate the formula. Check out both of these.

      A formula can work and still produce an incorrect result. Excel does not report an error because there is no error for it to find. Often, this is the result of not using parentheses properly in the formula. Take a look at some examples:

      All of these are valid formulas, but the placement of parentheses makes a difference in the outcome. You must take into account the order of mathematical operators when writing formulas. Here's the order of precedence:

      1. Parentheses

      2. Exponents

      3. Multiplication and division

      4. Addition and subtraction

      This is a key point of formulas. It is easy to just accept a returned answer. After all, Excel is so smart. Right? Wrong! Like all computer programs, Excel can do only what it is told. If you tell it to calculate an incorrect but structurally valid formula, it will do so. So watch your p’s and q’s – er, your parentheses and mathematical operators – when building formulas.

      The second type of error occurs when a mistake in the formula or in the data the formula uses prevents Excel from calculating the result. Excel makes your life easier by telling you when such an error occurs. To be precise, it does one of the following:

      ✔ Excel displays a message when you attempt to enter a formula that is not constructed correctly.

      ✔ Excel returns an error message in the cell when there is something wrong with the result of the calculation.

First, look at what happened when I tried to finish entering a formula that had the wrong number of parentheses. Figure 1-19 shows this.

       Figure 1-19: Getting a message from Excel.

      Excel finds an uneven number of open and closed parentheses. Therefore, the formula cannot work (it does not make sense mathematically), and Excel tells you so. Watch for these messages; they often offer solutions.

      On the other side of the fence are errors in returned values. If you got this far, the formula’s syntax passed muster, but something went awry nonetheless. Possible errors include

      ✔ Attempting to perform a mathematical operation on text

      ✔ Attempting to divide a number by 0 (a mathematical no-no)

      ✔ Trying to reference a nonexistent cell, range, worksheet, or workbook

      ✔ Entering the wrong type of information into an argument function

      This is by no means an exhaustive list of possible error conditions, but you get the idea. So what does Excel do about it? There are a handful of errors that Excel places into the cell with the problem formula.

      Chapter 4 discusses catching and handling formula errors in detail.

      Using Functions in Formulas

      Functions are like little utility programs that do a single thing. For example, the SUM function sums numbers, the COUNT function counts, and the AVERAGE function calculates an average.

      There are functions to handle many needs: working with numbers, working with text, working with dates and times, working with finance, and so on. Functions can be combined and nested (one goes inside another). Functions return a value, and this value can be combined with the results of another function or formula. The possibilities are nearly endless.

      But functions do not exist on their own. They are always a part of a formula. Now, that can mean that the formula is made up completely of the function or that the formula combines the function with other functions, data, operators, or references. But functions must follow the formula golden rule: Start with the equal sign. Look at some examples:

      Ready to write your first formula with a function in it? Use the following steps to write a function that creates an average:

      1. Enter some numbers in a column’s cells.

      2. Click an empty cell where you want to see the result.

      3. Type =AVERAGE( to start the function.

       Note: Excel presents a list of functions that have the same spelling as the function name you type. The more letters you type, the shorter the list becomes. The advantage is, for example, typing the letter A, using ↓ to select the AVERAGE function and then pressing the Tab key.

      4. Click the first cell with an entered value and, while holding the mouse button, drag the mouse pointer over the other cells that have values.

      An alternative is to enter the range of those cells.

      5. Type).

      6. Press