Читать книгу Excel Formulas and Functions For Dummies - Bluttman Ken - Страница 6

Okay, time to get to the nitty-gritty of what Excel is all about. Sure, you can just enter data and leave it as is, and even generate some pretty charts from it. But getting answers from your data, or creating a summary of your data, or applying what-if tests – all of this takes formulas.

To be specific, a formula in Excel calculates something or returns some result based on data in the worksheet. A formula is placed in cells and must start with an equal sign (=) to tell Excel that it is a formula and not data. Sounds simple, and it is.

All formulas start with an equal (=) sign.

Look at some very basic formulas. Table 1-1 shows a few formulas and tells you what they do.

Table 1-1 Basic Formulas

I use the word return to refer to what displays after a formula or function does its thing. So saying “The formula returns a 7” is the same as saying “The formula calculated the answer to be 7.”

Entering your first formula

Ready to enter your first formula? Make sure Excel is running and a worksheet is in front of you, and then follow these steps:

1. Click an empty cell.

2. Type = 10 + 10.

3. Press Enter.

That was easy, wasn’t it? You should see the result of the formula – the number 20.

Try another. This time you create a formula that adds the value of two cells:

1. Click any cell.

2. Type any number.

3. Click another cell.

4. Type another number.

5. Click a third cell.

This cell will contain the formula.

6. Type =.

7. Click the first cell.

This is an important point in the creation of the formula. The formula is being written by both your keyboard entry and your clicks of the mouse. The formula should look about half complete, with an equal sign immediately followed by the address of the cell you just clicked. Figure 1-14 shows what this looks like. In the example, the value 15 has been entered into cell B3 and the value 35 into cell B6. The formula was started in cell E3. Cell E3 so far has =B3 in it.

8. Type +.

9. Click the cell that has the second entered value.

In this example, this is cell B6. The formula in cell E3 now looks like this: =B3 + B6. You can see this in Figure 1-15.

10. Press Enter.

This ends the entry of the function. All done! Congratulations!

Figure 1-14: Entering a formula that references cells.

Figure 1-15: Completing the formula.

Figure 1-16 shows how the example ended up. Cell E3 displays the result of the calculation. Also notice that the Formula Bar displays the content of cell E3, which really is the formula.

Figure 1-16: A finished formula.

Understanding references

References abound in Excel formulas. You can reference cells. You can reference ranges. You can reference cells and ranges on other worksheets. You can reference cells and ranges in other workbooks. Formulas and functions are at their most useful when you’re using references, so you need to understand them.

And if that isn’t enough to stir the pot, you can use three types of cell references: relative, absolute, and mixed. Okay, one step at a time here. Try a formula that uses a range.

Formulas that use ranges often have a function in the formula, so use the SUM function here:

1. Enter some numbers in many cells going down one column.

2. Click another cell where you want the result to appear.

3. Type =SUM( to start the function.

4. Click the first cell that has an entered value, hold the left mouse button down, and drag the mouse pointer over all the cells that have values.

5. Release the mouse button.

The range address appears where the formula and function are being entered.

6. Type ).

7. Press Enter.

8. Give yourself a pat on the back.

Wherever you drag the mouse to enter the range address into a function, you can also just type the address of the range, if you know what it is.

Excel is dynamic when it comes to cell addresses. If you have a cell with a formula that references a different cell’s address, and you copy the formula from the first cell to another cell, the address of the reference inside the formula changes. Excel updates the reference inside the formula to match the number of rows and/or columns that separate the original cell (where the formula is being copied from) from the new cell (where the formula is being copied to). This may be confusing, so try an example so you can see this for yourself:

1. In cell B2, type 100.

2. In cell C2, type =B2 * 2.

3. Press Enter.

Cell C2 now returns the value 200.

4. If C2 is not the active cell, click it once.

5. Press Ctrl + C, or click the Copy button in the Clipboard category on the Home Ribbon.

6. Click cell C3.

7. Press Ctrl + V, or click the Paste button in the Clipboard category on the Home Ribbon.

8. If you see a strange moving line around cell C2, press the Esc key.

Cell C3 should be the active cell, but if it is not, just click it once. Look at the Formula Bar. The contents of cell C3 are =B3 * 2, and not the =B2 * 2 that you copied.

Did you see a moving line around a cell? That line’s called a marquee. It’s a reminder that you are in the middle of a cut or copy operation, and the marquee goes around the cut or copied data.

What happened? Excel, in its wisdom, assumed that if a formula in cell C2 references the cell B2 – one cell to the left – the same formula put into cell C3 is supposed to reference cell B3 – also one cell to the left.

When you’re copying formulas in Excel, relative addressing is usually what you want. That’s why it is the default behavior. Sometimes you do not want relative addressing, but absolute addressing. This is making a cell reference fixed to 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.