A moving average of sorts

[Prime12357]

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

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

 

[AhoyNC]

Maybe:
Enter with CTRL-SHIFT-ENTER

=AVERAGE(IF(Totals[Date]>=$E$20,IF(Totals[Date]<=$G$20,IF(MONTH(Totals[Date])=MONTH($B22&1,Totals[Rent]))))

 

[Prime12357]

This worked, thank you! I had tried a similar average(if(and argument before to no avail, and never thought of nesting it.

Can you think of any reason the earlier proposed formula didn't work, when the arguments independently were found true?

 

[AhoyNC]

It's because the AND function does not return an array of values which you need in this case.

In the example below the AND formula in C2 just returns FALSE (cell D2).
The IF formula in C3 returns the array shown in cell D3.

Book1
	A	B	C	D
1	2		Formula	Returns
2	3		False	False
3	1		True	{TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;TRUE}
4	4
5	15
6	5
7	3
Sheet1

Cell Formulas
Range	Formula
C2	C2	{=AND(A1:A7>=1,A1:A7<=3)}
C3	C3	{=IF(A1:A7>=1,IF(A1:A7<=4,TRUE,FALSE))}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.