Statistical Analysis with Excel For Dummies. Joseph Schmuller
Чтение книги онлайн.
Читать онлайн книгу Statistical Analysis with Excel For Dummies - Joseph Schmuller страница 23
Название: Statistical Analysis with Excel For Dummies
Автор: Joseph Schmuller
Издательство: John Wiley & Sons Limited
Жанр: Программы
isbn: 9781119844563
isbn:
Cannot duplicate any cell reference in the worksheet.
Otherwise, Excel gives you up to 255 characters to get creative with the name.
Here’s how to define a name:
1 Add a descriptive name at the top of a column (or to the left of a row) you want to name.(Refer to Figure 2-10.)
2 Select the range of cells you want to name.In this example, that’s cells C2 through C13. Why not include C1? I explain in a second.
3 Right-click the selected range.This step opens the pop-up menu shown in Figure 2-13.FIGURE 2-13: Right-clicking a selected cell range opens this pop-up menu.
4 From this pop-up menu, choose Define Name.This selection opens the New Name dialog box. (See Figure 2-14.) As you can see, Excel knows that Revenue_Millions is the name of the array and that Revenue_Millions refers to cells C2 through C13. When presented with a selected range of cells to name, Excel looks for a nearby name — just above a column or just to the left of a row. If no name is present, you get to supply one in the New Name dialog box. (You can also open the New Name dialog box by choosing Formulas | Define Name.)FIGURE 2-14: The New Name dialog box. When you select a range of cells, like a column, with a name at the top, you can include the cell with the name in it and Excel attaches the name to the range. I strongly advise against doing this, however. Why? If I select C1 through C13, the name Revenue_Millions refers to cells C1 through C13, not C2 through C13. In that case, the first value in the range is text, and the others are numbers.For a formula such as SUM (or SUMIF or SUMIFS, which I discuss next), this doesn't make a difference: In those formulas, Excel just ignores values that aren’t numbers. If you have to use the whole array in a calculation, however, it makes a huge difference: Excel thinks the name is part of the array and tries to use it in the calculation. You see this in the next section, on creating your own array formulas.
5 Click OK.Excel attaches the name to the range of cells.
Now I have the convenience of using the name in a formula. Here, selecting a cell (like C14) and entering the SUM formula directly into C14 opens the boxes shown in Figure 2-15.
As the figure shows, the boxes open as you type. Selecting Revenue_Millions and pressing the Tab key fills in the formula in a way that Excel understands. You have to supply only the close parenthesis (see Figure 2-16) and press Enter in order to see the result.
Using the named array, then, the formula is
=SUM(Revenue_Millions)
which is more descriptive than
=SUM(C2:C13)
FIGURE 2-15: Entering a formula directly into a cell opens these boxes.
FIGURE 2-16: Completing the formula.
A couple of other formulas show just how convenient this naming capability can be. These formulas, SUMIF and SUMIFS, add a set of numbers if specific conditions in one cell range (SUMIF) or in more than one cell range (SUMIFS) are met.
To take full advantage of naming, I name my table's column A (Year) and column B (Region) in the same way I named column C.
To keep track of the names in a worksheet, choose Formulas | Name Manager from the main menu to open the Name Manager box, shown in Figure 2-17. (The nearby buttons in the Defined Names area of the Ribbon — Define Name, Use in Formula, and Create from Selection — come in handy, so keep them in mind for the future.)
FIGURE 2-17: Managing the Defined Names in a worksheet.
Next, I sum the data in column C, but only for the North region. That is, I consider a cell in column C only if the corresponding cell in column B contains North. To do this, I follow these steps:
1 Select a cell for the formula result.My selection here is C15.
2 Choose Formulas | Math & Trig.
3 From the pop-up menu that appears, choose SUMIF.This selection opens the Function Arguments dialog box, shown in Figure 2-18.SUMIF has three arguments. The first, Range, is the range of cells to evaluate for the condition to include in the sum (North, South, East, or West, in this example). The second, Criteria, is the specific value in the range (North, for this example). The third, Sum:range, holds the values I sum.FIGURE 2-18: The Function Arguments dialog box for SUMIF.
4 In the Function Arguments dialog box, enter the appropriate values for the arguments.Here's where another Defined Names button comes in handy. In that Ribbon area, click the down arrow next to Use in Formula to open the drop-down list shown in Figure 2-19.Choosing from this list fills in the Function Arguments dialog box, as shown in Figure 2-20. I had to type North into the Criteria box. Excel adds the double quotes.
5 Click OK.The result appears in the selected cell. In this example, it’s 78.
FIGURE 2-19: The Use in Formula drop-down list.
In the Formula bar, this line appears:
=SUMIF(Region,"North", Revenue_Millions)
