AVERAGEIF - If criteria not met problem

[Miodrag J]

Hello everyone!

I'm experiencing a problem with an AVERAGEIF formula. Let's say I have 10 columns and 5 rows with values ranging 1-5. What I want to achieve is the following: I want the average of values only from those rows where the value "1" isn't appearing. And for the rows where the value "1" is appearing, I want the result to be "FAILED".
I have tried with various formulas', but this one should've done the job for me, because it seems the most logical: =IFERROR(AVERAGEIF($A1:$E1,"<>1",$A1:$E1),"FAILED") . However, it seems that this formula only excludes the value "1" from the average formula. Basically, if there is value "1" appearing once across 5 rows, it only counts the average of the other 4 values.

I hope this wasn't too confusing.

I look forward to your answers and possible solutions.

Best regards,

Miodrag J.

 

[Jonmo1]

Perhaps

=IF(COUNTIF($A1:$E1,1),"Failed",AVERAGE($A1:$E1))

 

[wideboydixon]

Try this:

Book1
	A	B	C	D	E	F	G
1	1	2	3	4	5		FAILED
2	2	3	4	5	6		4
3	3	4	1	6	7		FAILED
4	7	7	8	9	9		8
Sheet2

Cell Formulas
Range	Formula
G1	=IF(IFERROR(MATCH(1,$A1:$E1,0),0)=0,AVERAGE($A1:$E1),"FAILED")

WBD

 

[wideboydixon]

Or the "obvious" solution that I missed of course. Sledgehammer to crack a walnut and all that ...

WBD

 

[Jonmo1]

@WBD
Don't worry, I 'almost' posted it like this
=IF(COUNTIF($A1:$E1,1),"Failed",AVERAGEIF($A1:$E1,"<>1",$A1:$E1))

 

[Miodrag J]

Perhaps

=IF(COUNTIF($A1:$E1,1),"Failed",AVERAGE($A1:$E1))

You've done it. Thank you very much for the fast solution.

Thanks a lot @WBD for your efforts, too.

 

[Jonmo1]

You're welcome.