Microsoft Office Tutorials and References
In Depth Information
The ROUND Function and Entering Formulas in the Template
The next step is to enter the four formulas for the ﬁ rst DVR company (JVC) in
the range D5:G5. When you multiply or divide decimal numbers that result in an answer
with more decimal places than the format allows, you run the risk of the column totals
being off by a penny or so. For example, as shown in the worksheet sketch in Figure 6–3a
on page EX 422, columns C through G use the Currency and Comma style formats
with two decimal places. And yet, the formulas used to calculate values for these columns
result in several additional decimal places that Excel maintains for computation purposes.
For this reason, it is recommended that you use the ROUND function on formulas that
potentially can result in more decimal places than the format displays in a given cell. The
general form of the ROUND function is
=ROUND (number, number of digits)
where the number argument can be a number, a cell reference that contains a number, or
a formula that results in a number; and the number of digits argument can be any positive
or negative number used to determine the number of places to which the number will be
The following is true about the ROUND function:
1. If the number of digits argument is greater than 0 (zero), then the number is
rounded to the speciﬁ ed number of digits to the right of the decimal point.
2. If the number of digits argument is equal to 0 (zero), then the number is rounded
to the nearest integer.
3. If the number of digits argument is less than 0 (zero), then the number is rounded
to the speciﬁ ed number of digits to the left of the decimal point.
Table 6–1 shows the four formulas to enter in the template in the range D5:G5.
The ROUND function is used to round the value resulting from the formula assigned to
cell E5 to two decimal places.
The result of an
such as multiplication
or division, is accurate
to the factor with
the least number of
The forward slash (/)
has multiple uses. For
example, dates often are
entered using the slash.
In formulas, the slash
represents division. What
about fractions? To enter
a fraction, such as ½, type
.5 or 0 ½ (i.e., type zero,
followed by a space, fol-
lowed by the number 1,
followed by a slash, fol-
lowed by the number 2).
If you type ½ without the
preceding zero, Excel will
store the value in the cell
as the date January 2.
Table 6–1 Formulas Used to Determine Proﬁ t Potential
Units On Hand x Average Unit Cost
=B5 * C5
Average Unit Price
ROUND(Average Unit Cost / (1-.42), 2)
=ROUND(C5 / (1–.42), 2)
Units On Hand x Average Unit Price
=B5 * E5
Proﬁ t Potential
Total Value – Total Cost
=F5 – D5
You change from Enter
mode or Edit mode to
Point mode by typing the
EQUAL SIGN (=) followed
by clicking a cell or click-
ing the Insert Function
box on the formula bar,
selecting a function, and
then clicking a cell. You
know you are in Point
mode when the word
Point appears on the
left side of the status
bar at the bottom of the
The most difﬁ cult formula to understand in Table 6–1 is the one that determines
the average unit price, which also is called the average selling price. To make a net proﬁ t,
companies must sell their merchandise for more than the unit cost of the merchandise
plus the company’s operating expenses (taxes, rent, upkeep, and so forth).
To determine what selling price to set for an item, companies often ﬁ rst establish a
desired margin and then determine a selling price. Most companies look for a margin of
30% to 75%. NextDVR, Inc., for example, tries to make a margin of 42% on each of its
digital cameras. The formula for the average unit price in Table 6–1 helps the company
determine the price at which to sell an item so that it ends up with a 42% margin. For
example, if an item costs NextDVR $2.00 (the unit cost), then the company must sell it for
$3.45 [$2.00 / (1–.42)] to make a 42% margin. Of this $3.45, $2.00 goes to pay the unit
cost of the item; the other $1.45 is the gross proﬁ t potential (42% x $3.45 = $1.45).