Microsoft Office Tutorials and References

In Depth Information

**The ROUND Function and Entering Formulas in the Template**

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

rounded.

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.

Accuracy

The result of an

arithmetic operation,

such as multiplication

or division, is accurate

to the factor with

the least number of

decimal places.

Fractions

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

Cell

Description

Formula

Entry

D5

Total Cost

Units On Hand x Average Unit Cost

=B5 * C5

E5

Average Unit Price

ROUND(Average Unit Cost / (1-.42), 2)

=ROUND(C5 / (1–.42), 2)

F5

Total Value

Units On Hand x Average Unit Price

=B5 * E5

G5

Proﬁ t Potential

Total Value – Total Cost

=F5 – D5

Changing Modes

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

Excel window.

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).