Название | Statistical Analysis with Excel For Dummies |
---|---|
Автор произведения | Joseph Schmuller |
Жанр | Программы |
Серия | |
Издательство | Программы |
Год выпуска | 0 |
isbn | 9781119844563 |
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.
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.
SUM(Test)
, SUM(test)
, and SUM(tEST)
all give you the same result.
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.
#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. |
|