Microsoft Office Tutorials and References

In Depth Information

**Formatting Calculated Values**

◗

6.
In the Clipboard group on the Home tab, click the
Copy
button

. The formulas

are copied to the Clipboard.

◗

7.
Scroll down and select the range
C26:D26
. These are the cells in which you want

to paste the copied formulas.

◗

8.
In the Clipboard group, click the
Paste
button . The formulas to calculate

the total annual revenues are pasted in the selected range, displaying totals of

872437.2 in 2011 and 933661.3 in 2012. See Figure 2-7.

Figure 2-7

Formulas calculate the total units sold and total revenue

SUM f
unction

sum of units sold

per year

sum of sales

revenue per year

The formula results are difﬁ cult to read in their current form. You’ll correct that

problem shortly.

To calculate the net change in units sold and revenue from 2011 to 2012, you’ll

subtract the 2011 ﬁ gures from the 2012 ﬁ gures. To calculate the percent change, you’ll

divide the net change by the 2011 units sold and revenue ﬁ gures for each sales region as

well as across all sales regions.

To calculate the net and percent change from 2011 to 2012:

◗

1.
In cells E6 and E17, enter the label
Increase
, and then, in cells F6 and F17, enter

the label
% Increase
.

◗

2.
In cell E7, enter the formula
=D7–C7
to calculate the increase in units sold from

2011 to 2012.

◗

3.
In cell F7, enter the formula
=E7/C7
to calculate the percent increase in units sold

from 2011 to 2012. When calculating a percent increase, always divide the amount

of increase by the starting value and not the ending value.

◗

4.
Select the range
E7:F7
. Rather than reenter the formulas in cells E7 and F7, you’ll

copy and paste them into the rest of the worksheet.