IFERROR() result such as it is ignored by AVERAGE()?

[siamond]

Hi there.

Is there a way to make IFERROR(x, t) return a "t" result that would ignored by AVERAGE()?

I tried this: AVERAGE(IFERROR(x, ""), IFERROR(y, "")), but unfortunately this doesn't work as expected. There is a subtle difference between inputting "" to the AVERAGE() function and referencing a blank cell...

If there is no direct solution, is there a somewhat indirect -yet simple- approach? I tried to play with AGGREGATE() or AVERAGEIF(), but I couldn't find a proper answer.

Thanks in advance for your help!

PS: "x" and "y" are actually long expressions, I'd rather not duplicate them in the formula. I also would like to proceed in one single step (one formula, no intermediate cell).

 

[siamond]

=IFERROR(SUM(IFERROR(x,0),IFERROR(y,0))/COUNT(x,y),"")

Yup, I thought about that, this is certainly a tad more elegant than what I currently do, but I was trying to avoid repeating"x" and "y" which are fairly long VLOOKUP expressions.

 

[siamond]

Actually, Scott made me think that, when limited to two values, I could write:
=IFERROR(AVERAGE(IFERROR(x, y), IFERROR(y, x)), NA())

This doesn't answer my own question (since it repeats 'x' and 'y'), but there is a certain elegance to it!

 

[siamond]

Back to this... Maybe I can ask the question in another way...

Any way to make an array from the output of discrete formulas? Something akin to { VLOOKUP(...); VLOOKUP(...) }?

If there is an answer to that, then I should be able to use AVERAGIF() to solve my little problem.