using averageifs and want to ignore cells w/ errors

[tdg]

I have a column of data that I want to find the average for. But I have the following two conditions for the cell to be included in the average: (1) it is a number and not an error and (2) the number is in a row where a specified cell has certain text. So I want something like this:

=AVERAGEIFS(T2:T67,T2:T67,"<>""#VALUE!",I2:I67,"pre-LEV")

I want to average the range t2:t67, but first make sure there are no errors in each cell and that the cell is in a row where "pre-LEV" exists.

Thanks!

 

[Jonmo1]

You will be better served by eliminating the error at the source.

What is the formula in column T that results in the #Value! error?
It should be adjusted to return a blank instead of the error.

 

[tdg]

Thanks. Yes, I can eliminate it at the source, but isn't there a straightforward way to do it with the averageifs command?

 

[Aladin Akyurek]

Thanks. Yes, I can eliminate it at the source, but isn't there a straightforward way to do it with the averageifs command?

Try...

=AVERAGEIFS(T2:T67,T2:T67,"<"&9.99E+307,I2:I67,"pre-LEV")

 

[Jonmo1]

Assuming all numbers are positive, you can test for >=0 instead of <>error

=AVERAGEIFS(T2:T67,T2:T67,">=0",I2:I67,"pre-LEV")

 

[tdg]

Awesome! Works perfectly.

 

[Aladin Akyurek]

Awesome! Works perfectly.

Great. Thanks for providing us feedback.