Statiscal Analysis With Excel for Dummies (Notes) - Creating Array Formulas & Descriptive Statistics Tool

Creating your own Array Formulas:

In addtion to Excel's built-in array formulas, you can create your own.  The help things along you can incorporate named arrays.

The image below shows two named arrays, A and B.  If you want each cell in column K to hold the product of the corresponding  cell in column H and column I, follow the steps below the image:

1.  Select the array that will hold the answers to the array formula.

2  Into the selected array, type the formula A*B

3.  Press Ctrl-Shift-Enter (Not Enter)

Note the formula in the formula bar has curly brackets which indicate an Array Formula.

Descriptive Statistics Tool:

The image below shows an array of numbers in cells B2 through B9 with a column header in B1.

2.  Click Data | Data Analysis (all the way to the right) to open the Data Analysis dialog box.

3.  Click descriptive statistics and click ok.

4.  Identify the data array.  In the Input Range box, enter the cells that hold the data.  For this example, that's B1 through B9.

5.  Select the Columns radio button to indicate that the data are organized by columns.

6.  Select the Labels in First Row check box, because the Input Range includes the column heading

7.  Select the New Worksheet Ply radio button.  This tells Excel to create a new tabbed sheet within the current worksheet, and to send the results there.

8.  Click the Summary Statistics checkbox and leave others unchecked.  Click OK