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 |
