Microsoft Office Tutorials and References
In Depth Information
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.
Drag the ﬁ ll handle down through cell J13 to copy the formula in cell J12 to cell J13
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
after copying cell
J12 to cell J13
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
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
Excel usually considers
a blank cell to be equal
to 0. The statistical
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.
3/6/07 3:28:23 PM
3/6/07 3:28:23 PM