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 fi 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:
Search JabSto ::




Custom Search