Prime12357
New Member
- Joined
- Dec 29, 2019
- Messages
- 2
- Office Version
- 365
- Platform
- Windows
Hello World. I am working on updating spreadsheet to track expenses year over year. One of the updates is to include the ability to selectively average data from a variable range of years (user selected). The monthly sum data is presented in a table, where the first column has a date (i.e 2015-02-01) and subsequent columns are the value in that month for various expenses, i.e. rent, utilities, groceries etc.). In the averages summary table, the data is broken down by month in the first column, and then each subsequent column is a given category of expense. The goal, is to allow the user to enter a number of years to average, and display the average category cost for the months in that range. That is to say, the user may enter "3" years, and the average groceries (and all categories) for each month between 2016 and 2019 would be calculated into the averages summary table. For this to work, the formula has to intelligently identify the year of the date code in the monthly sum data to be in the correct range, and that the month of the date code to match that of the line in the averages summary table.
After several attempts, the closest I have been able to come is:
{=AVERAGE(IF(AND(Totals[Date]>=$E$20,Totals[Date]<=$G$20,MONTH(Totals[Date])=MONTH($B22&1)),Totals[Rent]))}
Where
Totals[Date] is the first column of the monthly sum data - being the date code
$E$20 is the generated start date which counts back the user entered number of years from today's date (i.e. 2019-12-29 today becomes 2016-12-29 in E20).
$G$20 is today's date
$B22 is the Month header in the averages summary table, and would be the text of "January" etc.
When evaluated, the results from the conditional statements all return as expected, that is to say, that the dates which fall in the appropriate time period, and are of the appropriate month all appear as true, and the others all false. However, when the overall formula evaluates, it evaluates as false. I am at a loss as to where to proceed from here.
Totals Table
Averages Table
After several attempts, the closest I have been able to come is:
{=AVERAGE(IF(AND(Totals[Date]>=$E$20,Totals[Date]<=$G$20,MONTH(Totals[Date])=MONTH($B22&1)),Totals[Rent]))}
Where
Totals[Date] is the first column of the monthly sum data - being the date code
$E$20 is the generated start date which counts back the user entered number of years from today's date (i.e. 2019-12-29 today becomes 2016-12-29 in E20).
$G$20 is today's date
$B22 is the Month header in the averages summary table, and would be the text of "January" etc.
When evaluated, the results from the conditional statements all return as expected, that is to say, that the dates which fall in the appropriate time period, and are of the appropriate month all appear as true, and the others all false. However, when the overall formula evaluates, it evaluates as false. I am at a loss as to where to proceed from here.
Totals Table
Date | Rent | Utilities | Groceries |
2014-01-01 | $ | $ | $ |
2014-02-01 | $ | $ | $ |
2014-03-01 | $ | $ | $ |
[...] | |||
2019-12-01 | $ | $ | $ |
Averages Table
Month | Rent | Utilities | Groceries |
January | |||
February | |||
March | |||
[...] | |||
December |