Microsoft Office Tutorials and References

In Depth Information

EX 98

Excel Chapter 2
Formulas, Functions, Formatting, and Web Queries

To Determine the Total Percent Gain/Loss

With the totals in row 13 determined, the next step is to copy the percent gain/loss

formula in cell J12 to cell J13 as performed in the following steps.

1
Select cell J12 and then point to the ﬁ ll handle.

2

Drag the ﬁ ll handle down through cell J13 to copy the formula in cell J12 to cell J13

(Figure 2–15).

Why was the formula I13/F13 not copied to cell J13 earlier?

The formula, I13/F13, was not copied to cell J13 when cell J4 was copied to the range J5:J12

because both cells involved in the computation (I13 and F13) were blank, or zero, at the

time. A
blank cell
in Excel has a numerical value of zero, which would have resulted in an

error message in cell J13. Once the totals were determined, both cells I13 and F13

(especially F13, because it is the divisor) had nonzero numerical values.

formula is =I12/F12

formula is =I13/F13

Auto Fill Options

button appears

after copying cell

J12 to cell J13

Figure 2–15

Entering Functions

You can drag the Function

Arguments dialog box

(Figure 2–20 on page

EX 101) out of the way in

order to select a range.

You also can click the

Collapse Dialog button to

the right of the Number 1

box to hide the Function

Arguments dialog box.

After selecting the range,

click the Collapse Dialog

button a second time.

Using the AVERAGE, MAX, and MIN Functions

The next step in creating the Silver Dollars Stock Club Portfolio Summary worksheet is

to compute the average, highest value, and lowest value for the number of shares listed in

the range D4:D12 using the AVERAGE, MAX, and MIN functions in the range D14:D16.

Once the values are determined for column D, the entries can be copied across to the

other columns.

Excel includes prewritten formulas called functions to help you compute these

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(D4:D12), the function name is AVERAGE, and the

argument is the range D4:D12.

With Excel, you can enter functions using one of ﬁ ve methods: (1) the keyboard or

mouse; (2) the Insert Function box in the formula bar; (3) the Sum menu; (4) the AutoSum

command on the Formulas tab on the Ribbon; and (5) the Name box area in the formula

Statistical Functions

Excel usually considers

a blank cell to be equal

to 0. The statistical

functions, however,

ignore blank cells. Excel

thus calculates the

average of 3 cells with

values of 7, blank, and 5

to be 6 or (7 + 5) / 2 and

not 4 or (7 + 0 + 5) / 3.

C6161_EXC_02.4c.indd 98

C6161_EXC_02.4c.indd 98

3/6/07 3:28:23 PM

3/6/07 3:28:23 PM