Microsoft Office Tutorials and References

In Depth Information

**Computing a cumulative sum**

The
SUM
function is versatile, but it’s also inconsistent when you use logical values (
TRUE
or
FALSE
). Logical values

stored in cells are always treated as
0
. However, logical
TRUE
, when used as an argument in the
SUM
function, is

treated as
1
.

Computing a cumulative sum

You may want to display a cumulative sum of values in a range — sometimes known as a

“running total.” Figure 17.11 illustrates a cumulative sum. Column B shows the monthly

amounts, and column C displays the cumulative (year-to-date) totals.

FIGURE 17.11

Simple formulas in column C display a cumulative sum of the values in column B.

The formula in cell C2 is:

=SUM(B$2:B2)

Notice that this formula uses a
mixed reference
— that is, the ﬁ rst cell in the range

reference always refers to the same row (in this case, row 2). When this formula is copied down

the column, the range argument adjusts such that the sum always starts with row 2 and

ends with the current row. For example, after copying this formula down column C, the

formula in cell C8 is:

=SUM(B$2:B8)

You can use an
IF
function to hide the cumulative sums for rows in which data hasn’t been

entered. The following formula, entered in cell C2 and copied down the column, is: