Removing #N/A

[Anto111]

Hi guys,

I have applied the following formula:

{=INDEX($K$7:$K$34,MATCH(1,($C$7:$C$34=H86)*($AR$7:$AR$34=$E$64),0))}

When the number in AR7:AR34 does not match the number in E64 it returns #N/A.

I need to change this #N/A to a blank so as I can get an average of the
cells that meet the specific criteria.

Kind regards and many thanks in advance,

Ant

 

[Bob Phillips]

IF(ISNA(MATCH(1,($C$7:$C$34=H86)*($AR$7:$AR$34=$E$64),0)),"",INDEX($K$7:$K$34,MATCH(1,($C$7:$C$34=H86)*($AR$7:$AR$34=$E$64),0)))

 

[Anto111]

Bob, you're a star. Thats a few of times you have helped me out now.

Really is appreciated.

Kind regards,

Ant

 

[Anto111]

Sorry to be a pest.....

I was just thinking, is there a way to say, If the value in E64 = 0 then
return the value in cell k7 but if does not then go ahead with the original
formula? This being:

IF(ISNA(MATCH(1,($C$7:$C$34=H86)*($AR$7:$AR$34=$E$64),0)),"",INDEX($K$7:$K$34,MATCH(1,($C$7:$C$34=H86)*($AR$7:$AR$34=$E$64),0)))

Thanks again for all your help.

Ant

 

[Bob Phillips]

Should just be

=IF(E64=0,K7,
IF(ISNA(MATCH(1,($C$7:$C$34=H86)*($AR$7:$AR$34=$E$64),0)),"",
INDEX($K$7:$K$34,MATCH(1,($C$7:$C$34=H86)*($AR$7:$AR$34=$E$64),0))))