August 15, 2017 - by Bill Jelen
This episode shows three ways to do running totals.
A running total is, for a list of numeric values, a sum of the values from the first row to the row of the running total. Common uses of a running total are in a checkbook register or an accounting sheet. There are many ways to create a running total—two of which are described below.
The simplest technique is to, on each row, add the running total from the row above to the value in the row. So the first formula in row 2 is:
The reason we use the SUM function is because, in the first row, we are looking at the header in the row above. If we use the simpler, more intuitive formula of
=D1+C2 then an error will be generated because the header value is text versus numeric. The magic is that the SUM function ignores text values, which are added as zero values. When the formula is copied down to all of the rows in which a running total is desired, the cell references are adjusted accordingly:
The other technique also uses the SUM function but each formula sums all of the values from the first row to the row displaying the running total. In this case we use a dollar sign ($) to make the first cell in the reference an absolute reference which means it is not adjusted when copied:
Both techniques are unaffected by sorting and deleting rows but, when inserting rows, the formula has to be copied into the new rows.
Excel 2007 introduced the Table which is a re-implementation of the List in Excel 2003. Tables introduced a number of very useful features for data tables such as formatting, sorting, and filtering. With the introduction of Tables we were also provided a new way of referencing the parts of a Table. This new referencing style is called structured referencing.
To convert the above example into a Table, we select the data we want to include in the Table and press Ctrl + T. After displaying a prompt asking us to confirm the Table's range and whether or not there are existing headers, Excel converts the data into a formatted Table:
Note that the formulas we entered earlier remain the same.
One of the useful features Tables offers is automatic formatting and formula maintenance as rows are added, removed, sorted, and filtered. It is the formula maintenance in particular that we will focus on and which can be problematic. To keep Tables working while they are manipulated, Excel utilizes calculated columns which are columns with formulas such as column D in the above example. When new rows are inserted are added to the bottom, Excel automatically populates the new rows with the “default” formula for that column. The problem with the above example is that Excel gets confused with standard formulas and does not always handle them correctly. This is made apparent when new rows are added to the bottom of the Table (by selecting the bottom right cell in the Table and pressing TAB):
This deficiency is resolved by using the newer structured referencing. Structured referencing eliminates the need to reference specific cells using the A1 or R1C1 referencing style and instead uses column names and other keywords to identify and reference the parts of a Table. For example, to create the same running total formula used above but using structured referencing we have:
In this example we have a reference to the column name, “Sales”, along with the at sign (@) to reference the row in the column in which the formula is located which is also known as the current row.
To implement the first example above where we added the running total value in the preceding row to the sales amount in the current row, you can use the OFFSET function:
If the amounts used to calculate the running total are in two columns, for example one for “Debits” and one for “Credits”, then the formula is:
=SUM(INDEX( [Credit],1):[@Credit])- SUM(INDEX( [Debit],1):[@Debit])
Here we are using the INDEX function to locate the first row’s Credit and Debit cells, and summing the entire column up to and including the current row’s values. The running total is the sum of all credits up to and including the current row less the sum of all the debits up to and including the current row.
When I asked readers to vote for their favorite tips, tables were popular. Thanks to Peter Albert, Snorre Eikeland, Nancy Federice, Colin Michael, James E. Moede, Keyur Patel, and Paul Peton for suggesting this feature. Peter Albert wrote the Readable References bonus Tip. Zack Barresse wrote the Running Totals bonus tip. Four readers suggested using OFFSET to create expanding ranges for dynamic charts: Charley Baak, Don Knowles, Francis Logan, and Cecelia Rieb. Tables now do the same thing in most cases.
- This episode shows three ways to do running totals
- The first method has a different formula in Row 2 than all the other rows
- The first method is =Left in row 2 and =Left+Up in rows 3 through N
- If you try to use the same formula, you get a #Value error with =Total+Number
- Method 2 uses
=SUM(Previous Total,This Row Amount)
- SUM ignores Text so you don't get a VALUE error
- Method 3 uses an expanding range:
- Expanding ranges are cool but they are slow
- Read the Charles Williams whitepaper on Excel Formula Speed
- The third method is a problem when you use Ctrl + T and add new rows
- Excel can't figure out how to write the formula
- The workarounds require some knowledge of structured referencing in Tables
- Workaround 1 is the slow
- Workaround 2 is the volatile
- [@Qty] refers to Qty on this row
- [Qty] refers to all Qty values
Download the sample file here: Podcast2004.xlsx
Title Photo: skeeze / pixabay