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).

 

[Joe4]

Assuming that t is a numeric result, theoretically, you should be able to use AVERAGEIF by choosing some value for t that would be outside of the criteria range that you are selecting in your AVERAGEIF.

 

[siamond]

Assuming that t is a numeric result, theoretically, you should be able to use AVERAGEIF by choosing some value for t that would be outside of the criteria range that you are selecting in your AVERAGEIF.

AVERAGEIF() requires a range as input, I don't know how to make it work with discrete values (each of them being the result of a VLOOKUP formula)?

 

[Joe4]

Sorry, your original post was a bit generic/abstract.

Can you provide a bit more detail as to the exact specific as to what exactly you are trying to do?
Are you trying to average the results of a bunch of VLOOKUPS in one single equation?
How many VLOOKUPS are we talking about?
What are you returning in the case of an error (your "t" value)?

 

[siamond]

Sorry, your original post was a bit generic/abstract.

Can you provide a bit more detail as to the exact specific as to what exactly you are trying to do?
Are you trying to average the results of a bunch of VLOOKUPS in one single equation?
How many VLOOKUPS are we talking about?

Well, I was trying to get a general-purpose answer, but in this specific case, I have two VLOOKUPs to average, and either one of them may fail to return a proper value. And yes, I'd like to do that in one single equation, while keeping it fairly compact, so that I can still understand it one year from now...

What are you returning in the case of an error (your "t" value)?

This was my question. What could I return which would make the AVERAGE() function ignore such value? "" doesn't work, unfortunately. And there is no BLANK() function which would emulate a blank cell.

As I mentioned, I am open to another approach though. If this approach depends on the number of arguments to the AVERAGE() function, this would still be valuable to me. Thanks in advance!

 

[siamond]

PS. so far, I settled on something like IFERROR(IFERROR(AVERAGE(x, y), IFERROR(y, x)), NA()), but this is clunky given that "x" and "y" are VLOOKUP calls.

 

[Joe4]

Hmmm... I am not sure of a simple way to do what you want.
Sometimes, things can get really complicated if you try to do too much in one step. Not that there is anything wrong with that, it just might end up being a complicated formula.
I am sometimes inclined to create my own function in VBA via a User Defined Function to do things like that (so the function is simple in Excel, and all the heavy lifting happens behind the scenes, but that comes with a price too, as now you have to have VBA enabled.

If it was me, I would be inclined to have the results of the IFERROR/VLOOKUPs returned to some cells, and then probably write my average formulas on those (and then you could use things like AVERAGEIF on them). Sometimes I will even make the user of "hidden" helper cells if I do not want to expose them to users.

 

[siamond]

If it was me, I would be inclined to have the results of the IFERROR/VLOOKUPs returned to some cells, and then probably write my average formulas on those (and then you could use things like AVERAGEIF on them). Sometimes I will even make the user of "hidden" helper cells if I do not want to expose them to users.

I hear you. In general, I do like to do thing one small step at a time, if only for maintenance reasons. But in this case, I have a large table full of such equations, then multiple tabs formatted in a similar fashion, so I'd rather keep it compact. Thanks for your feedback though, appreciated. I might come back to your idea if nobody volunteers a compact approach...

 

[Joe4]

I hear you. In general, I do like to do thing one small step at a time, if only for maintenance reasons. But in this case, I have a large table full of such equations, then multiple tabs formatted in a similar fashion, so I'd rather keep it compact. Thanks for your feedback though, appreciated. I might come back to your idea if nobody volunteers a compact approach...

There's a chance someone might. There are some rather brilliant people here who put my skills to shame!

 

[Scott Huish]

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