|
Removing #N/A
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 |
Removing #N/A
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=H8 6)*($AR$7:$AR$34=$E$64),0)))
-- __________________________________ HTH Bob "Anto111" wrote in message ... 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 |
Removing #N/A
Bob, you're a star. Thats a few of times you have helped me out now.
Really is appreciated. Kind regards, Ant "Bob Phillips" wrote: 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=H8 6)*($AR$7:$AR$34=$E$64),0))) -- __________________________________ HTH Bob "Anto111" wrote in message ... 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 |
Removing #N/A
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=H8 6)*($AR$7:$AR$34=$E$64),0))) Thanks again for all your help. Ant "Anto111" wrote: Bob, you're a star. Thats a few of times you have helped me out now. Really is appreciated. Kind regards, Ant "Bob Phillips" wrote: 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=H8 6)*($AR$7:$AR$34=$E$64),0))) -- __________________________________ HTH Bob "Anto111" wrote in message ... 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 |
Removing #N/A
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)))) -- __________________________________ HTH Bob "Anto111" wrote in message ... 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=H8 6)*($AR$7:$AR$34=$E$64),0))) Thanks again for all your help. Ant "Anto111" wrote: Bob, you're a star. Thats a few of times you have helped me out now. Really is appreciated. Kind regards, Ant "Bob Phillips" wrote: 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=H8 6)*($AR$7:$AR$34=$E$64),0))) -- __________________________________ HTH Bob "Anto111" wrote in message ... 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 |
| All times are GMT +1. The time now is 12:43 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com