Microsoft Office Tutorials and References

In Depth Information

**Working with Functions**

As you develop formulas and worksheets, you might want to quickly switch a cell

reference from relative to absolute or mixed. Rather than retyping the formula, you can

switch the reference in editing mode by selecting the cell reference and pressing the

F4 key. As you press the function key, Excel cycles through the different reference types,

starting by changing a relative reference to an absolute reference, then to a mixed

reference with the row absolute, then to a mixed reference with the column absolute, and

then ﬁ nally back to a relative reference.

You’ll use the F4 key to cycle through the different types of references as you enter the

remaining formulas with the income for the summer months.

To insert the remaining references to the couple’s monthly income:

◗

1.
Click cell
H19
, type
, and then click cell
. The formula =E5 appears in the cell,
E5

which remains in Edit mode. This formula enters Diane’s income for the summer

months.

◗

2.
Press the
key. The formula changes to =$E$5, which is an absolute reference.
F4

Trouble?
If the formula shows anything other than the absolute reference, you

probably pressed the F4 key too many times. Continue to press the F4 key to loop

through all of the cell reference types until the formula returns to =$E$5, which

contains the absolute reference.

◗

3.
Press the
Enter
key. The formula is entered and 2,100 (Diane’s monthly income in

the summer) appears in cell H19.

◗

4.
In cell H20, enter the formula
=$E$6
. This formula uses an absolute reference to

enter Glenn’s monthly income in the summer.

◗

5.
In cell H21, enter the formula
=H19+H20
. This formula adds Diane and Glenn’s

income for June; their combined monthly income in the summer is 4,700.

◗

6.
Copy the range
H19:H21
, and then paste the copied formulas into the

range
I19:J21
. The summer take-home pay values appear for the months of June

through August.

You’ll complete the monthly income values for the remaining school months.

◗

7.
Copy the range
C19:C21
, and then paste it into the range
K19:N21
. The couple’s

monthly income is entered for all months of the year.

Working with Functions

Excel functions provide a quick way to calculate summary data such as the total, average,

minimum, and maximum values in a collection of values. You’ll use these functions to

summarize Diane and Glenn’s income and expense data at the top of the Budget worksheet.

Understanding Function Syntax

Every function has to follow a set of rules, or
syntax
, which speciﬁ es how the function

should be written. The general syntax of all Excel functions is

FUNCTION
argument1
argument2
,ƒ...)
,ƒ