Image: Screenshot
The article How to calculate conditional subtotals in an Excel revenue sheet uses simple expressions, a function, and a conditional format to display a daily balance in a revenue sheet. The result provides a lot of information, and you don’t have to work very hard for it.
In this article, we’ll tackle a similar problem—a conditional running total. A running total is also common in such a sheet. First, we’ll design a simple sheet and add a running total. Then, we’ll complicate things by adding conditions. If that sounds complicated, don’t worry. It isn’t.
SEE: How to add a drop-down list to an Excel cell (TechRepublic)
I’m using Microsoft 365 on a Windows 10 64-bit system, but you can use earlier versions. For your convenience, you can download the demonstration .xlsx file. The browser edition will support the functions and expressions used in this article. This article assumes you have basic Microsoft Excel skills.
How to make a basic credit-debit sheet
We’ll start with the simple sheet shown in Figure A. Each transaction (row or record) has a total in column E, the result of the following expression:
E3: =C3-D3
This simple expression subtracts the debit from the credit. In some situations, this information would be helpful, but as is, for most of us, it’s not particularly relevant. Some of the transactions show a deficit that doesn’t truly exist (although it could). A transaction deficit is not the same as the revenue dropping below $0. It depends on how you’re using the data to generate meaningful information. The first thing we’ll add is a simple running total.
Figure A
How to add a running total
A simple running total has no conditions and can be handled by adding a second expression:
=credit–debit+previous balance
Figure B shows the running total in column F. F3 contains the same simple expression as E3—subtract debit from credit. F4 and the remaining cells add the transaction total to the previous record:
F4: =C4-D4+C3
Figure B
In a nutshell, the expression subtracts the debit from the credit and adds the previous running total. Consequently, the last record displays the current balance of $2,873.96. Notice that there’s no deficit, although there could be, and in this case, it would be more meaningful than it was in the first sheet if at some point, the revenue truly went below $0. Again, how you’re using the data matters, and that’s where conditions come in. Let’s add a running total that stops calculating at the end of each day.
How to add a conditional running total
If you’re wondering how a “running total that stops calculating at the end of the day” differs from a daily balance, you’re probably in good company. The difference is this: A daily balance is one result that returns all transactions for the day; a running total builds on each transaction, returns a daily total, and then starts over when the date changes. One isn’t better than the other; they’re different.
What we’re after now is a running total that starts with the first transaction of each day, evaluates all transactions for that day, and ends with the final running total for that day. That sounds like a lot of requirements to satisfy, and a simple expression won’t get the job done.
First, let’s add a new column for the Daily Running Total to column G. As always, the first row needs the first simple expression we used in both earlier examples:
G3: =C3-D3
Then, add the following function to G4 and copy to the remaining cells in the data set:
G4: =IF(B3=B4,C4-D4+G3,C4-D4)
When the current date and the date in the next record, are the same, the function evaluates the running total expression. When the date’s aren’t the same, the function evaluates the simple expression that evaluates only the current record. As you can see in Figure C, the daily running balance provides a lot of information about all of the days’ transactions, but it can be misleading; the deficits belong to a day’s transactions, and not the account balance. Every day starts with a balance of 0. For instance, on April 29, the conditional running total shows a deficit of $135 but the simple running total shows an account balance of $1,658.96.
Figure C
Deciding if you need a daily running total or a simple running total depends on the way you use the data, but let’s consider another possibility. Let’s suppose you want to know the running total for credits and debits.
How to add a running total for credits and debits
Columns C and D store the credit and debit values, respectfully. If you’re using a Table object, you can enable the Totals row and see the total debit and credits for the entire sheet. But let’s suppose you want to see a running total for credit and debit values. We’ll add two more columns, Credit Running Total and Debit Running Total to columns H and I, respectively. Can you guess the expressions you’ll use in H3 and I3? They’re the simplest of all at this point:
H3: =C3
I3: =D3
You’re starting with the first credit and debit values. The next step is to add the expressions that add current debit and credit values to previous credit and debit values:
H4: =C4+H3
I4: =D4+I3
Copy to the remaining cells in the data set. Figure D shows the running credit and debit values, and the last row shows the total for each. If you subtract the total debits from credits, the result should equal the daily balance in F12.
Figure D
You could stop at this point, but there’s one thing you might want to do. As is, the daily running total gets lost in all that data.
How to add a conditional format
If you want the daily running balance to stand out, as shown in Figure E, use conditional formatting. Begin by selecting that column (G3:G12). Then, on the Home tab, click Conditional Formatting in the Styles group and choose New Rule. In the resulting dialog, select the last item in the top pane, Use a Formula…, then add the formula below:
=$B3<>$B4
Click Format and then click Fill. From the palette, choose a color—I choose a medium green—and click OK. Figure E shows the rule and the format. Click OK to return to the sheet shown in Figure F. This last step isn’t necessary, but if the daily running total is important, it’s easy to make it stand out.
Figure E
Figure F
Don’t neglect the date order
The records in our demonstration file are in date order, which makes sense. Because the date is the condition applied, it’s important that the records be sorted by date. If not, the expressions and function in this article will return erroneous values. When applying this to your own work, your records may not be sorted or grouped by the condition, so you might need to check that before reporting results.
