Joseph Schmuller - Statistical Analysis with Excel For Dummies

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)

If you choose to, you can type the line exactly that way into the Formula bar, without using the dialog box or the drop-down list. When you don't use the dialog box, you have to supply the double quotes around the criteria.

FIGURE 2-20:Completing the Function Arguments dialog box for SUMIF.

The formula in the Formula bar is easier to understand than

= SUMIF(B2:B13,"North", C2:C13)

isn’t it?

Incidentally, the same cell range can be both the Range and the Sum:range. For example, to sum just the cells for which Revenue_Millions is less than 25, you’d use

=SUMIF(Revenue_Millions, "< 25", Revenue_Millions)

The second argument (Criteria) is always in double quotes.

What about SUMIFS? That one is useful if you want to find the sum of revenues for North but only for the years 2006 and 2007. Follow these steps to use SUMIFSto find this sum:

1 Select a cell for the formula result.The selected cell is C17.

2 Choose Formulas | Math & Trig.

3 From the pop-up menu that appears, choose SUMIFS.This step opens the Function Arguments dialog box.

4 In the Function Arguments dialog box, enter the appropriate values for the arguments, as shown in Figure 2-21.Notice that, in SUMIFS, the Sum Range argument appears first. In SUMIF, however, it appears last.Note that the formula in the Formula bar is now=SUMIFS(Revenue_Millions,Year,"<2008",Region,"North") FIGURE 2-21:The completed Function Arguments dialog box for SUMIFS.

5 Click OK.The answer, 46, appears in the selected cell.

With unnamed arrays, the formula would have been

=SUMIFS(C2:C13,A2:A13,"<2008",B2:B13,"North")

which seems much harder to comprehend.

A defined name involves absolute referencing. (See Chapter 1.) Therefore, if you try to autofill from a named array, you'll be in for an unpleasant surprise: Rather than autofill a group of cells, you copy a value over and over again.

Here’s what I mean. Suppose you assign the name Series_1 to A2:A11 and Series_2 to B2:B11. In A12, you calculate SUM(Series_1). Because you’re clever, you figure you’ll just drag the result from A12 to B12 to calculate SUM(Series_2). What do you find in B12? SUM(Series_1)— that's what.

Excel does not treat array names as case-sensitive. If the named array is Test, for example, SUM(Test), SUM(test), and SUM(tEST)all give you the same result.

You can't name an array in Excel on the iPad. If you name an array in a Windows or Mac Excel spreadsheet, however, and your Microsoft 365 account includes the iPad, you can open that spreadsheet on the iPad and the named array works just fine.

In addition to Excel’s built-in array formulas, you can create your own. (Again, not on the iPad.) To help things along, you can incorporate named arrays.

Figure 2-22 shows two named arrays, X and Y, in columns C and D, respectively. X refers to C2 through C5 ( not C1 through C5), and Y refers to D2 through D5 ( not D1 through D5). XY is the column header for column F. Each cell in column F stores the product of the corresponding cell in column C and the corresponding cell in column D.

FIGURE 2-22:Two named arrays and an array formula.

An easy way to enter the products, of course, is to set F2 equal to C2*D2 and then autofill the remaining applicable cells in column F.

Just to illustrate array formulas, though, follow these steps to work on the data in the worksheet (refer to Figure 2-22):

1 Select the cell to start the output array.That would be F2. (Figure 2-21 shows the selected cell.)

2 Into the selected cell, type the formula.The formula here is =X * Y

3 Press Enter.The answers appear in F2 through F5, as Figure 2-23 shows.

FIGURE 2-23:The results of the array formula =X * Y.

When you name a range of cells, make sure that the named range does not include the cell with the name in it. If it does, an array formula like {=X * Y} tries to multiply the letter X by the letter Y to produce the first value, which is impossible and results in the exceptionally ugly #VALUE!error.