I can get it to work with AVERAGE no problem: issue is I need it to ignore 0 and that's where it breaks.
My range is calculted by an AVERAGEIFS formula. I can either have three values, each calculated by an AVERAGEIFS, or I can calculte the values with one formula using curly brackets }. If I do this need to put the AVERAGEIFS into an IFERROR or I get a div/0 error.
So (if you're following!) my range is calculated like this:
=IFERROR(AVERAGEIFS(A1:A10,B1:B10, criteria ["1","2","3"}),0)
This works perfectly, though spills over 3 cells. When I put the above into an AVERAGE function to prevent spill, it works. Problem is sometimes my criteria will return an error (which counts as zero), and I need it to ignore zero. When I use AVERAGEIF(above range,"<>0") it's telling me there's something wrong with my formula and refusing to calculate.
Any ideas? I was tearing my hair out for about an hour yesterday.