# Calculating an Average based on changing values

Copper Contributor

# Calculating an Average based on changing values

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 For Jan Income Left Feb Income Left Mar Income Left Apr Income Left May Income Left Jun Income Left Groceries 100 xxxx 200 xxxx 150 xxxx 500 xxxx 600 xxxx 0 xxxx