This chapter teaches the following topics:

” A SIMPLE FUNCTION

” FUNCTIONS THAT NEED MULTIPLE RANGE REFERENCES

” WRITING A “FUNCTION WITHIN A FUNCTION“

” NEW IN EXCEL XP

” RECOMMENDED FUNCTIONS IN THE FUNCTION WIZARD

” EXPANDED AUTOSUM FUNCTIONALITY

” FORMULA EVALUATOR

” FORMULA ERROR CHECKING

BASICS

4.1

Excel has many in“built functions. The functions may be inserted into a

formula.

Accessing the functions dialog/wizard

(a) select the menu path INSERT/FUNCTION, or

(b) click on the function icon (see Figure 42)

60

Chapter 4: Inserting functions

Figure 42: The Function icon

The “Paste Function” dialog (or wizard, because it is a series of dialogs)

opens. The dialog is shown in Figure 43.

Figure 43: Understanding the PASTE FUNCTION dialog

1 2

3

The equivalent dialog in the XP version of Excel is called INSERT

FUNCTION. (It is reproduced in the next figure below.) The dialog has

one new feature”a “Search for a function” utility. The “Function

category” is now available by clicking on the list box next to the label “Or

select a category.”

61

Statistical Analysis with Excel

Figure 44: The equivalent dialog in the XP version of Excel is called INSERT FUNCTION

This dialog has three parts:

(1) The area “Function category” on the left half shows the labels of

each group of functions. The group “Statistical” contains

statistical functions like “Average” and “Variance.” The group

“Math & Trig” contains algebra and trigonometry functions like

“Cosine.” When you click on a category name, all the functions

within the group are listed in the area “Function name.”

(2) The area “Function name” lists all the functions within the

category selected in the area “Function category.” When you

click on the name of a function, its formula, and description is

shown in the gray area at the bottom of the dialog.

(3) The area with a description of the function

Step 2 for using a function in a formula

Click on the “Function category” (in area 1 or the left half of the dialog)

62

Chapter 4: Inserting functions

that contains the function, then click on the function name in the area

“Function name” (in area 2 or the left half of the dialog) and then execute

the dialog by clicking on the button OK.

A SIMPLE FUNCTION

4.2

In my first example, I show how to select and use the function “Average”

which is under the category “Statistical.”

Choose the category “Statistical” as shown in Figure 45.

Figure 45: Choosing a function category