Microsoft Office Tutorials and References

In Depth Information

**Making Decisions — The IF Function**

To Enter the Remaining July Formulas

Replacing a Formula

with a Constant

By doing the following,

you can replace a formula

with its result so that

the cell value remains

constant: (1) click the cell

with the formula; (2) press

F2
or click in the formula

bar; (3) press
F9
to display

the value in the formula

bar; and (4) press the

ENTER
key.

The July commission expense in cell B19 is equal to the revenue in cell B13 times

the commission assumption in cell B3 (5.75%). The July marketing expense in cell B20 is

equal to the projected July revenue in cell B13 times the marketing assumption in cell B5

(15.00%). Similar formulas determine the remaining July expenses in cells B21 and B22.

The total expenses value in cell B23 is equal to the sum of the expenses in the range

B18:B22. The operating income in cell B25 is equal to the gross margin in cell B15 minus

the total expenses in cell B23. The formulas are short, and therefore, they are typed in the

following steps, rather than entered using Point mode.

1

Select cell B19. Type
=b13*$b$3
and then press the
DOWN
ARROW
key to enter the

formula in the selected cell. Type
=b13*$b$5
and then press the
DOWN
ARROW
key

to enter the formula in the selected cell. Type
=b13*$b$6
and then press the
DOWN

ARROW
key to enter the formula in the selected cell. Type
=b13*$b$8
and then press

the
DOWN
ARROW
key to enter the formula in the selected cell.

2

With cell B23 selected, click the Sum button (Home tab | Editing group) twice to insert a

SUM function in the selected cell. Select cell B25 to prepare to enter the next formula.

Type
=b15–b23
and then press the
ENTER
key to enter the formula in the selected cell.

3

Press
CTRL
+
ACCENT
MARK
(`)
to display the formulas version of the worksheet (Figure 3 – 31).

4

When you are i nished viewing the formulas version, press
CTRL
+
ACCENT
MARK
(`)
to display

the values version of the worksheet.

Why should I view the formulas version of the worksheet?

Viewing the formulas version (Figure 3 – 31) of the worksheet allows you to check the

formulas assigned to the range B14:B25. Recall that formulas were entered in lowercase.

You can see that Excel converts all the formulas from lowercase to uppercase.

in formulas version,

Excel displays

formulas for July

Figure 3–31