how to elimate #N/A?

[cjbyf]

Hello,

How can I alter this formula so I do not get a #N/A in a cell if no value is returned? I am trying to average the column and it throws it off.

Here's my current formula:

=VLOOKUP("boston",'1.26.05'!$A$1:$B$50,2,FALSE)

I would appreciate any help I could get.

Thanks,
Chris

 

[ken2step]

Without seeing your data this should work....

=if(isna(VLOOKUP("boston",'1.26.05'!$A$1:$B$50,2,FALSE),"",VLOOKUP("boston",'1.26.05'!$A$1:$B$50,2,FALSE))

 

[cjbyf]

Thanks Ken,

I plugged your suggestion in, however the dialogue box pops up telling me there's an error in the formula and the proceeds to highlight.

Here is the formula broken up:

=if(isna(VLOOKUP("boston",'1.26.05'!$A$1:$B$50,2,FALSE)

,"", <---- excel highlights this part of the formula

VLOOKUP("boston",'1.26.05'!$A$1:$B$50,2,FALSE))

Now I know the "" is the key to this but not sure why the error.

Any thoughts why that is?

I appreciate it very much.

Chris

 

[NdNoviceHlp]

Ken is missing a bracket after "False". Trial this. Dave

IF(ISNA(VLOOKUP("boston",'[1.26.05]1.26'!$A$1:$B$50,2,FALSE)),"",VLOOKUP("boston",'[1.26.05]1.26'!$A$1:$B$50,2,FALSE))

 

[cjbyf]

I don't know that doesn't seem to be working either... what am I doing wrong?

 

[KenWright]

Assuming you currently have a working formula, Syntax is as follows:-

=IF(ISNA(Your_Formula),"",Your_Formula)

Just plug your formula in this

 

[tactps]

Following on from Ken:
=IF(ISNA(VLOOKUP("boston",'[1.26.05]1.26'!$A$1:$B$50,2,FALSE) ),"",VLOOKUP("boston",'[1.26.05]1.26'!$A$1:$B$50,2,FALSE))

 

[Todd Bardoni]

Somehow [] got into the past few formulas. Try:-

=IF(ISNA(VLOOKUP("boston",'1.26.05'!$A$1:$B$50,2,FALSE)),"",VLOOKUP("boston",'1.26.05'!$A$1:$B$50,2,FALSE))