Choose the formula “Average” in the area “Function name.”

This is shown in Figure 46.

Execute the dialog by clicking on the button OK.

63

Statistical Analysis with Excel

Figure 46: Choosing a function name

The dialog (user-input form) for the “Average” function opens.

For a pictorial reproduction of this, see Figure 47.

Figure 47: The dialog of the chosen function

Step 3 for inserting a function ” defining the data

arguments/requirements for the function

Figure 48: Selecting the cell references whose values will be the inputs into the function

64

Chapter 4: Inserting functions

You have to tell Excel which cells contain the data to which you want to

apply the function “AVERAGE.” Click on the right edge of the text-box

“Number1”6. (That is, on the red“blue“and“white corner of the cell.) Go

to the worksheet that has the data you want to use and highlight the

range “C2 to E3.” Click on the edge of the text-box. (For a pictorial

reproduction of this, see Figure 48.)

You will be taken back to the “Average” dialog. Notice that ” as shown in

Figure 49 ” the cell reference “C2:E3” has been added.

Furthermore, note that the answer is provided at the bottom (see the line

“Formula result = 9973333.333”).

Execute the dialog by clicking on the button OK.

Figure 49: The completed function dialog

If you want to use non-adjacent ranges in the formula, then use the text-box “Number

6

2” for the second range. Excel will add more text-boxes once you fill all the available

ones. If the label for a text-box is not in bold then it is not essential to fill that text-

box. In the AVERAGE dialog shown in Figure 402, the label for the first text-box

(“Number 1”) is in bold”so it has to be filled. The label for the second text-box

(“Number 2”) is not in bold ” so, it can be left empty.

65

Statistical Analysis with Excel

The formula is written into the cell and is shown in Figure 50.

Figure 50: The function is written into the cell

Press the ENTER key and the formula will be calculated.

You can work with this formula in a similar manner as a simple formula

” copying and pasting, cutting and pasting, writing on multiple

worksheets, etc.

If you remember the function name, you do not have to use

INSERT/FUNCTION. Instead, you can simple type in the formulas using

the keyboard. This method is faster but requires that you know the

function.

FUNCTIONS THAT NEED MULTIPLE RANGE

4.3

REFERENCES

Some formulas need a multiple range reference. One example is the

correlation formula (“CORREL“). Assume, in cell J1, you want to

calculate the correlation between the data in the two ranges: “D2 to D14”

and “E2 to E14.”

Activate cell J1. Select the option INSERT/FUNCTION. Choose the

function category “Statistical.” In the list of functions that opens in the

right half of the dialog, choose the function “CORREL“ and execute the

dialog by clicking on the button OK.

66

Chapter 4: Inserting functions

Figure 51: Choosing the function CORREL

The CORREL dialog (shown in the next figure) opens. The function needs

two arrays (or series) of cells references. (Because the labels to both the

text-box labels are bold, both text-boxes have to be filled for the function

to be completely defined.) Therefore, the pointing to the cell references

has to be done twice as shown in Figure 53 and the next two figures.