Calculating an Average based on changing values

Copper Contributor

In my budget spreadsheet, I am trying to determine an "On Pace For" value for each spending category. I want this "On Pace For" value to take the average of the months that are not zero and multiply it by 12 (in the example below it would calculate ((100+200+150+500+600)/5)*12. When the June value becomes non-zero it would then update to include the June value in the average calculation.


I have tried "=AVERAGEIF((E5,G5,I5,K5,M5,O5,Q5,S5,U5,W5,Y5,AA5),">0")*12" but it gives me a #VALUE error. 

I think the issue is that each months value is determined by a SUMIF formula looking at a seperate sheet with just a dump from my bank accounts that I then categorize. Is there a way to make this "On Pace For" value work?


 On Pace ForJanIncome LeftFebIncome LeftMarIncome LeftAprIncome LeftMayIncome LeftJunIncome Left
Groceries 100xxxx200xxxx150xxxx500xxxx600xxxx0xxxx
1 Reply




If you do not have Microsoft 365 or Office 2021, confirm the formula by pressing Ctrl+Shift+Enter.