06-30-2020 11:05 AM
06-30-2020 11:05 AM
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!
06-30-2020 02:00 PM - edited 06-30-2020 02:01 PM
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.
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.
06-30-2020 02:57 PM
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"
06-30-2020 03:10 PM
It is also possible to create 3D range references of the form
In the following, I have used a defined name 'Values' to refer to the range, allowing
The number of functions that work with 3D ranges is very limited though, so care is needed before you commit to such a strategy.