How to Create Running Totals and Resettable Mileage Calculations in Excel
Question details
The user needs to set up Excel formulas to track grant expenditures, calculate remaining balances, and maintain a running total for accumulated mileage that resets to zero after a reimbursement payment is issued.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Managing financial tracking for grant spending and travel expenses, requiring continuous cumulative sums alongside conditionally resetting totals.
- Observed behavior
- The user wants to implement cumulative SUM logic for spending and balance, and integrate conditional logic (like a payment indicator) to break and restart the mileage accumulation period.
Ensure your data is organized into clear columns (e.g., Date, Expense Amount, Miles Driven, Payment Issued) starting from the top row. A clean, tabular layout without merged cells is essential for running total formulas to calculate correctly.
Create a Running Total for Expenditures and Remaining Balance
Use a mixed reference SUM formula to continuously add expenditures and subtract them from a starting grant balance as you add new rows.
A standard running total relies on locking the first cell in a range while allowing the second part of the range to expand as you copy the formula down.
Create columns for 'Date', 'Grant Amount' (place your starting balance in the first data row), and 'Expenditure'.
In the 'Total Spent' column (e.g., cell D2), enter =SUM($C$2:C2) where Column C is the Expenditure column. The dollar signs lock the starting cell. Drag this formula down the column to calculate the cumulative spend.
In the 'Remaining Balance' column (e.g., cell E2), subtract the running total from your initial grant amount by entering =$B$2-D2 (assuming B2 holds the total grant). Drag this down to see the balance decrease over time.
Set Up a Resettable Mileage Calculation using IF Formulas
Combine the IF function with basic addition to reset your accumulated mileage to zero whenever a payment flag is indicated in the spreadsheet.
Track Expenses and Mileage Effortlessly with WPS Spreadsheet
WPS Office Spreadsheet provides full support for advanced data functions including SUM, IF, and mixed references. You can seamlessly build robust expense trackers and resettable running totals with a familiar interface.
- 1. Open your data: Launch WPS Spreadsheet and create a new workbook or open your existing expense tracking document.
- 2. Apply running totals: Use the exact same =SUM($A$1:A1) and IF formula logic for running totals, as WPS supports all standard Excel formula syntax.
- 3. Utilize built-in templates: Click 'New' on the homepage and search for 'Expense Tracker' in the template library to find pre-built tracking sheets with totals already configured.
Frequently Asked Questions
Why is my running total formula showing an error or incorrect sum when I drag it down?
This usually happens if you forget to lock the first cell reference. Ensure you use absolute referencing for the first cell in your SUM range, like =SUM($C$2:C2). The dollar signs keep the starting point fixed while the second part of the range expands as it moves down the column.
Can I reset my running total based on a specific date instead of typing 'Yes'?
Yes, you can modify the IF formula to evaluate dates. For example, you can check if the month of the current row differs from the previous row using the MONTH() function (e.g., =IF(MONTH(A3)<>MONTH(A2), B3, D2+B3)). This will automatically reset the mileage total at the start of a new month.
How can I calculate the remaining balance if expenditures are on different sheets?
You can reference cells across different sheets by clicking on the target sheet while writing the formula. For example, to subtract a running total on 'Sheet2' from a grant balance on 'Sheet1', your formula would look like: =Sheet1!$B$2 - Sheet2!D2.
