Running Average

Copper Contributor

Help!

 

I am creating a budget spreadsheet where I can enter my expenses for each month into a separate worksheet on excel.  I would like to keep a running average cost of different expenses.  I was wondering if there was a way to program it to automatically take the average of the cell on the current sheet as well as that same cell in all following sheets.  I know how to do it manually by adding the current month every time I create a new sheet, but I was wondering if there was a formula or code that would be just generic of "average of current and all following" sheets. Thanks!

4 Replies

@emmaballer 

 

Since you're in (I trust) the early stages of creating this budget spreadsheet, I'm going to suggest you change your approach and let Excel do the "heavy lifting" of giving you your monthly averages, your monthly totals, etc., by category.

 

To do that, have just a single sheet into which you enter the date for each check or credit card purchase (or download those from your banks), an indication of the expense category, etc.  Then use the Pivot Table capability of Excel to do that "heavy lifting" that you want it to do.

 

I'm attaching a very simple example just to show you how that can work. There are two Pivot Tables in this workbook.

  • The one labeled "Annual Summary" displays the sum total of expenses in each category for the year selected, as well as the averages for that year.
  • The one labeled "Monthly Summary" displays the totals for each category for each month in the years 2019 and 2020. That could be modified so it only does one year. In fact, it can be modified in all sorts of ways.

 

Let me encourage you to play around with this way of doing it. Add some expenses. Add some expense codes in the "Codes" tab... Then when you're in the Pivot Table tabs, just click on the "Refresh" button (in the image below, it's under the word "Design") to see how the Pivot Table automatically adds in those new pieces of information.

mathetes_0-1593550777569.png

 

 

Thank you @mathetes.  I will do some editing and see if I can make something similar to that work!

@emmaballer 

 

Feel free to post a copy of whatever you come up with if you continue to have questions. Part of the key to making that approach work dynamically is in having the ongoing expenses recorded in an Excel Table. And the same is true for the Budget Category list -- as you add things to it (or modify it), those changes are automatically included in Pivot Tables, etc, once you hit "Refresh"

 

@emmaballer 

It is also possible to create 3D range references of the form

=Sheet1:Sheet4!$B$2

In the following, I have used a defined name 'Values' to refer to the range, allowing

= SUM(Values)

= AVERAGE(Values)

=COUNT(Values)

The number of functions that work with 3D ranges is very limited though, so care is needed before you commit to such a strategy.