Statistical Analysis with Excel For Dummies. Joseph Schmuller

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.

Creating Your Own Array Formulas

      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.

      Using data analysis tools

      Excel has a set of sophisticated tools for data analysis. They reside in the Analysis ToolPak. This ToolPak isn't available for the iPad, but a similar package, the XLMiner Analysis ToolPak, is. I mention it in the next section.

Table 2-1 lists the ToolPak tools I cover. (The one I don’t cover, Fourier Analysis, is extremely technical.) Some of the terms in the table may be unfamiliar to you, so I define them throughout this book.

TABLE 2-1 Excel's Data Analysis Tools

Tool	What It Does
Anova: Single Factor	Performs analysis of variance for two or more samples.
Anova: Two-Factor with Replication	Performs analysis of variance with two independent variables, and multiple observations in each combination of the levels of the variables.
Anova: Two-Factor without Replication	Performs analysis of variance with two independent variables, and one observation in each combination of the levels of the variables; It’s also a repeated measures analysis of variance.
Correlation	With more than two measurements on a sample of individuals, calculates a matrix of correlation coefficients for all possible pairs of the measurements.
Covariance	With more than two measurements on a sample of individuals, calculates a matrix of covariances for all possible pairs of the measurements.
Descriptive Statistics	Generates a report of central tendency, variability, and other characteristics of values in the selected range of cells.
Exponential Smoothing	In a sequence of values, calculates a prediction based on a preceding set of values and on a prior prediction for those values.
F-Test Two-Sample for Variances	Performs an F-test to compare two variances.
Histogram	Tabulates individual and cumulative frequencies for values in the selected range of cells.
Moving Average	In a sequence of values, calculates a prediction which is the average of a specified number of preceding values.
Random Number Generation	Provides a specified amount of random numbers generated from one of seven possible distributions.
Rank and Percentile	Creates a table that shows the ordinal rank and the percentage rank of each value in a set of values.
Regression	Creates a report of the regression statistics based on linear regression through a set of data containing one dependent variable and one or more independent variables.
Sampling	Creates a sample from the values in a specified range of cells.
t-Test: Two Sample	Provides three t-test tools that test the difference between two means: One assumes equal variances in the two samples; another assumes unequal variances in the two samples; the third assumes matched samples.