Figure 52: The CORREL dialog

Choosing the first array/series

Click on the box edge of “Array1” (as shown in Figure 52.) Then go to the

relevant data range (D2 to D14 in this example) and select it.

Figure 53: Selecting the first data input for the function

67

Statistical Analysis with Excel

Repeat the same for “Array 2,” selecting the range “E2:E14” this time.

Figure 54: The first data input has been referenced

The formula is complete. The result is shown in the dialog in the area at

the bottom “Formula result.” Execute the dialog by clicking on the button

OK.

Figure 55: The second data input has also been referenced

Once the dialog closes, depress the ENTER key, and the function will be

written into the cell and its result evaluated/calculated.

Figure 56: The function as written into the cell.

WRITING A “FUNCTION WITHIN A FUNCTION”

4.4

I use the example of the CONFIDENCE function from the category

“Statistical.”

Choose the menu option INSERT/FUNCTION.

68

Chapter 4: Inserting functions

Choose the function category “Statistical.”

In the list of functions that opens in the right half of the dialog, choose the

function CONFIDENCE and execute the dialog by clicking on the button

OK.

Figure 57: Selecting the CONFIDENCE function

The Confidence dialog (user-input form) requires7 three parameters: the

alpha, standard deviation, and sample size. First type in the alpha

desired as shown in Figure 58. (An alpha of “.05” corresponds to a 95%

confidence level while an alpha value of “:.1” corresponds to a confidence

interval of 90 %.)

Figure 58: Dialog for CONFIDENCE

We know that all three are necessary because their labels are in bold.

7

69

Statistical Analysis with Excel

Press the OK button.

Figure 59: The first part of the function

Type a comma after the “.05” (see Figure 60) and then go to

INSERT/FUNCTION and choose the formula STDEV as shown in Figure

61.

Figure 60: Placing a comma before entering the second part

Choose the range for which you want to calculate the STDEV (for

example, the range “E:E”) and execute the dialog by clicking on the button

OK.

Figure 61: Using STDEV function for the second part of the function

The formula now becomes:

Figure 62: A function within a function

The main formula is still CONFIDENCE. The formula STDEV provides

one of the parameters for this main formula. The STDEV function is

nested within the CONFIDENCE function.