Microsoft Office Tutorials and References

In Depth Information

**Entering Formulas**

)

A formula in a cell that contains a reference back to itself is called a
circular reference
.

Excel often warns you when you create a circular reference. In almost all cases, circular

references are the result of an incorrect formula. A circular reference can be direct

or indirect. For example, placing the formula =A1 in cell A1 results in a direct circular

reference. An indirect circular reference occurs when a formula in a cell refers to another

cell or cells that include a formula that refers back to the original cell.

Employ the Excel built-in functions whenever possible.

(

Continued

Plan

Ahead

Automatic

Recalculation

Every time you enter

a value into a cell in

the worksheet, Excel

automatically recalculates

all formulas. You can

change to manual

recalculation by clicking

the Calculation Options

button (Formulas tab |

Calculation group) and

then clicking Manual.

In manual calculation

mode, pressing the
F9

key instructs Excel to

recalculate all formulas.

•

Excel includes prewritten formulas

called
functions
to help you compute a range of values and statistics. A function takes a

value or values, performs an operation, and returns a result to the cell. The values that

you use with a function are called
arguments
. All functions begin with an equal sign

and include the arguments in parentheses after the function name. For example, in the

function =AVERAGE(C4:C12), the function name is AVERAGE, and the argument is the

range C4:C12. Become familiar with the extensive number of built-in functions. When

you have the choice, always use built-in functions instead of writing and typing a formula

version of your mathematical expression. Such a practice reduces the possibility of errors

and simplii es the formula used in a cell, resulting in improved readability.

To Enter a Formula Using the Keyboard

The formulas needed in the worksheet are noted in the requirements document as follows:

1. Gross Pay (column F) = Hours Worked × Hourly Pay Rate

2. Federal Tax (column G) = 0.22 × (Gross Pay – Dependents × 24.32)

3. State Tax (column H) = 0.04 × Gross Pay

4. Net Pay (column I) = Gross Pay – (Federal Tax + State Tax)

5. Tax% (column J) = (Federal Tax + State Tax) / Gross Pay

The gross pay for each employee, which appears in column F, is equal to hours worked in column D times

hourly pay rate in column E. Thus, the gross pay for Emily Charvat in cell F4 is obtained by multiplying 65.25

(cell D4) by 20.50 (cell E4) or =D4*E4. The following steps enter the initial gross pay formula in cell F4 using

the keyboard.

1

•

With cell F4 selected, type

=d4*e4

in the cell to display the formula in

the formula bar and in the current

cell and to display colored borders

around the cells referenced in the

formula (Figure 2 – 5).

formula begins

with equal sign

What occurs on the worksheet as I

enter the formula?

The
equal sign
(
=
) preceding d4*e4

alerts Excel that you are entering

a formula or function and not

text. Because the most common

error when entering a formula is

to reference the wrong cell in a

formula mistakenly, Excel colors

the borders of the cells referenced

in the formula. The coloring helps

in the reviewing process to ensure

the cell references are correct.

The
asterisk
(
*
) following d4 is the

arithmetic operator that directs Excel to perform the multiplication operation.

as formula is typed, it

appears in formula bar

and in active cell F4

colored cell borders

indicate cells included

in formula in active cell

Figure 2–5