Microsoft Office Tutorials and References
In Depth Information
Making Decisions — The IF Function
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
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.
(a) Values Version
(b) Formulas Version