9.5. Advanced StatisticsExcel offers a smorgasbord of number- crunching tools; in fact, it has more than 50 advanced statistical functions that this chapter hasn't even mentioned. Many of these functions will appeal only to those who already understand concepts like standard deviation, theoretical distributions, and dimensionless Pearson product moment correlation coefficients (a subject of keen interest to all six people in the world who currently use such numbers ). But even if you don't understand any of those concepts (and if you're still wondering whether your inner mathematician may yet emerge), Excel offers two advanced statistical functionsFORECAST( ) and TREND( )that give you a taste of all that awaits the advanced statistician. While both FORECAST( ) and TREND( ) rely on some complex, behind-the-scenes computations , you can use them quite easily, even if you're not a specialist. The FORECAST( ) and TREND( ) functions predict missing values based on existing data. These "missing" values may represent experimental data you didn't collect, or future sales numbers for dates that haven't occurred yet. Excel examines the data you have so far (the more the better), and uses a mathematical technique known as the method of least squares to generate a trend line. Excel then uses this trend line to generate the other values you've requested . Both FORECAST( ) and TREND( ) work similarly and produce the same answers. However, FORECAST( ) is designed to calculate a single value. The TREND( ) function, on the other hand, is an array function that gives you multiple values. It works a little like the FREQUENCY( ) function described in Section 9.2.8. The FORECAST( ) and TREND( ) analyses work by trying to spot a relationship between two sets of numbers, designated as the x-axis and y-axis. To predict a new value in the FORECAST( ) function, for instance, you supply the function with a new x value. It then uses the known relationship between the x and y values to guess the corresponding y value. The syntax is as follows : FORECAST(x, known_y_values, known_x_values) For example, imagine you're trying to study the relationship between the number of product inquiries received during one month and the actual sales recorded in the next month. You have plenty of historical data, and you'd like to use that to predict how many sales you should expect next month (based on the number of inquiries you've received during the current month). The existing inquiry numbers are the first set of known values, so they make up the x-axis values (the cells A2:A9, as shown in Figure 9-12). The second set of known values is the actual recorded sales in the following month (the y-axis values, shown in cells B2:B9). You want to predict a new y-axis value for expected sales based on the current inquiries ( cell A12). Here's the final formula: =FORECAST(A12,B2:B9,A2:A9) Note: The FORECAST( ) and TREND( ) functions can always find some sort of relationship using your data. However, if your data doesn't have a real correlation, then the values they predict will be meaningless. For example, the number of product inquiries may not be an effective predictor of future sales, especially when you consider other factors. Determining where correlations exist and what relationships are statistically significant is a much more subtle and complex issue than just using the FORECAST( ) and TREND( ) functions. An understanding of basic statistics will set you down the right path . You can also use the TREND( ) function to generate expected sales based on multiple inquiry numbers (as opposed to the single sales value that FORECAST( ) generates). Using the previous example, you could theoretically use TREND( ) to try and predict sales for the next six months. In truth, that approach doesn't really make sense because, while you have previous sales data to draw from, you don't know how many inquiries you'll receive in future months.
You're better off using TREND( ) to project future sales based on a slightly different set of data. You still use previous sales, but rather than using inquiries as your second known factor, you use the progression of time . In effect, you're asking TREND( ) if your sales over periods 1, 2, and 3 were 100, 150, and 200, what would sales look like over periods 4, 5, 6? Without further ado, here's what you need to generate new, predicted sales figures using the TREND( ) function:
Excel provides many more advanced statistical functions. For the full list, refer to the Formulas Formula Library More Functions Statistical group. Table 9-3 outlines some of the more commonly used functions in this group . Table 9-3. Advanced Statistical Functions
|