Do I use AVERAGEIF or AVERAGEIFS ???

[JohnZ1156]

I'm trying to get the average interest rate YTD based on the date in column A.
I have many more months and years in the spreadsheet besides only 2023.
If the month and year in column A is 2023, for example, average all the months with interest greater than 0.
NOTE: There are formulas for future months that right now return Zero.

I tried both AVERAGEIF and AVERAGEIFS and I'm getting #DIV/0!
Obviously, I'm doing something wrong.

 

[Joe4]

Try this:

=AVERAGEIFS(B5:B16,A5:A16,">=1/1/2023",A5:A16,"<=12/31/2023")

 

[JohnZ1156]

Yes, but that is including Oct., Nov., and Dec. in the average.
I'm only looking for the average YTD.

The average for Jan. - Sept. is 1.49%.
By including the whole year, your formula returns 1.11%.

The formula should include the average percentage for cells in that year not including any percentages that = zero.

 

[Joe4]

Yes, but that is including Oct., Nov., and Dec. in the average.
I'm only looking for the average YTD.

The average for Jan. - Sept. is 1.49%.
By including the whole year, your formula returns 1.11%.

The formula should include the average percentage for cells in that year not including any percentages that = zero.

Then simply add another condition to only includes values not equal to zero, i.e.

=AVERAGEIFS(B5:B16,A5:A16,">=1/1/2023",A5:A16,"<=12/31/2023",B5:B16,"<>0")

 

[JohnZ1156]

Hey Joe4,
Thanks, that seems to work fine. I was trying add another condition that said to omit percentages that were "<=" zero.
I did modify your formlla however,
=AVERAGEIFS('Sheet1'!F:F,'Sheet1'!A:A,">="&DATE(YEAR(TODAY()),1,1),'Sheet1'!A:A,"<="&DATE(YEAR(TODAY()),12,31),'Sheet1'!F:F,"<>0")
Thank you !!!

 

[Joe4]

You are welcome. Glad I was able to help!
Nice job figuring out how to apply it to the current year dynamically!