Running total or cumulative sum can be calculated by using the SUM function using a combination of absolute and relative cell references.
Syntax=SUM($Column$Row:ColumnRow)ColumnRow – Cell number of the value, eg. $B$2
The dollar signs indicate absolute reference–they stay fixed; relative without it.
Sample Usage=SUM($B$3:B3)
//This returns the number in cell B3. On dragging the formula to other cells, the running total will be returned.
What is running balance in Google Sheets?
Running balance or running total is a total of transactions already made, or in other words, a cumulative total. Let’s say, we go to a shopping mall to buy some groceries. We purchase a list of items such as soaps, shampoo, etc. Then we go to the billing counter to purchase the selected items. As the cashier scans the items, a running balance is generated with the total increasing each time an item is added to the list.
Consider the list below.
Let’s say that the first item scanned is shampoo, whose price is Rs. 100. So the running balance right now is Rs. 100. Then the cashier scans the soap priced at Rs. 50. Now the running balance is Rs. 150 ( Rs. 100 + Rs. 50 ). Thereafter, he scans Tissue Roll priced at Rs. 30. So the running balance becomes Rs. 180 ( Rs.100 + Rs.50 + Rs.30 ). To summarise, the running balance is the total of all the transactions already made.
Why use running balance in Google Sheets?
Running balance is the simplest way to manage individual accounts. One significant benefit of using the running balance in Google Sheets is that we stay up to date with the balance of that account. Running balance in Google Sheets helps businesses track accounts based on addition or deletion of individual items. Therefore, if there is any discrepancy in the final total, one can refer to the running balance and look for outliers or incorrect entries.
How to calculate running balance in Google Sheets
We’ll learn how to calculate running total in Google Sheets using two different methods.
Calculate running balance in Google Sheets using SUM formula and cell referencing
Let us consider the following data sets to calculate the running total in Google Sheets.
To calculate the running total of the above items, we’ll use the SUM function in Google Sheets.
The formula for running balance in Google Sheets is as given below.
=SUM($Column$Row:ColumnRow)Here, the “$” sign is used for referencing the cell to make the cell reference absolute.
- So, for the first cell, the formula is =SUM($B$2:B2).
- Now drag down the selection till the last cell of items.
- If you copy the formula down to other cells, the formula will always start with cell B2 as we have applied Absolute cell reference to cell B2.
- This will help us calculate the running total till every new item is added.
- Since the other parameter (B2) is not referenced, it will change as soon as you drag down the selection. This is called relative referencing.
- Relative references change when a formula is copied to another cell. Absolute references, on the other hand, remain constant no matter where they are copied.
- If you drag the selection to cell C4, the formula for cell C4 is =SUM($B$2:B4).
Here’s a closer look at the formula for every cell for calculating running balance.
Calculate running balance in Google Sheets using ARRAYFORMULA
Let us consider the following income and expenditure statement to calculate running total in Google Sheets using the ARRAYFORMULA.
Let us calculate the running balance for the entire month. To calculate the running balance, we need to add income entries and subtract expenditure entries.
The formula to calculate the running balance using the ARRAYFORMULA function is:
=ArrayFormula(if(len(A11:A),(SUMIF(ROW(C11:C),"<="&ROW(C11:C),C11:C)-SUMIF(ROW(B11:B),"<="&ROW(B11:B),B11:B)),))- Here, (SUMIF(ROW(C11:C),”<=”&ROW(C11:C),C11:C) calculates the running balance by adding the income values.
- SUMIF(ROW(B11:B),”<=”&ROW(B11:B),B11:B)) adds the expenditure values.
- On entering the formula, we’ll get the running balance as shown below.
When use the SUM function to calculate running balance and when to use the ARRAYFORMULA
If you want to perform simple calculations, as explained in the first example, it is advisable to use SUM function with cell referencing to calculate the running total as it is quick and easy to perform. You can also use the ARRAYFORMULA for simple calculations, but the formula is a bit more complicated.
However, if you want to perform calculate the running balance using multiple columns, it is advisable to use the ARRAYFORMULA method. Let’s say you have a column of debit entry and a column of credit entry. You want to calculate the running balance for every entry. In this case, it is advisable to use the ARRAYFORMULA method as you can use functions such as SUMIF and MMULT to find running balance. In this case, if you use cell references, it would make the referencing very complicated as multiple columns are involved.
So based on your use case, you can use either one of the above-mentioned methods.
Conclusion
You can calculate the running balance in Google Sheets using cell referencing or by using ARRAYFORMULA. Running balance helps businesses track individual entries and look out for incorrect ones.
For more articles on Google Sheets, visit our blog.
See Also
How to Calculate CAGR in Google Sheets– Learn how to calculate the compound annual growth rate in Google Sheets in a couple of minutes.
How To Use The CUMIPMT Function In Google Sheets – Calculate the cumulative interest paid on a loan or investment (fixed principal) at a fixed rate of interest over a specified period.
How to group rows in Google Sheets: We will learn how to group rows in Google Sheets. We will also have a look at nested grouping.
Complete Guide on the REGEXEXTRACT Function in Google Sheets for 2022: Learn how to use the REGEXEXTRACT function in Google Sheets and its variations.