Microsoft Office Tutorials and References

In Depth Information

Making Decisions — The IF Function

EX 191

To Enter the Remaining January Formulas

The January commission expense in cell B10 is equal to the sales in cell B4 times

the commission assumption in cell B20 (3.25%). The January marketing expense in cell

B11 is equal to the projected January sales in cell B4 times the marketing assumption in

cell B22 (9.00%). Similar formulas determine the remaining January expenses in cells B12

and B13.

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

B9:B13. The operating income in cell B16 is equal to the gross margin in cell B6 minus

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

following steps, rather than entered using Point mode.

Replacing a Formula

with a Constant

You can replace a

formula with its result so

it remains constant. Do

the following: (1) Click

the cell with the formula;

(2) press
F2
or click in the

formula bar; (3) press
F
9

to display the value in

the formula bar; and

(4) press the
ENTER
key.

1
Click cell B10. Type
=b4*$b$20
and then press the
DOWN ARROW
key. Type
=b4*$b$22

and then press the
DOWN ARROW
key. Type
=b4*$b$23
and then press the
DOWN ARROW

key. Type
=b4*$b$25
and then press the
DOWN ARROW
key.

2
With cell B14 selected, click the Sum button on the Home tab on the Ribbon twice. Click

cell B16. Type
=b6-b14
and then press the
ENTER
key (Figure 3–30a).

3
Press
CTRL
+
ACCENT MARK
(`) to instruct Excel to display the formulas version of the

worksheet (Figure 3–30b).

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

instruct Excel to display the values version of the worksheet.

Why should I view the formulas version of the worksheet?

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

formulas assigned to the range B5:B16. Recall that formulas were entered in lowercase.

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

Excel displays

values for

January

in formulas

version, Excel

displays formulas

for January

(a) Values Version

(b) Formulas Version

Figure 3–30