How do I get an #N/A field to return 0?

  • Thread starter Thread starter Max
  • Start date Start date
M

Max

I download data from some numerical data from an external source in to excel
and I am trying to some basic maths on it. The problem is that i never know
how many rows there will be from one download to the next and I am trying to
set up a sheet where by i can just dump data in one sheet and the second
sheet will default to synopsising the data, So far, my hatchet job solution
has jsut been to copy the formulae to more rows than I need. However, for one
of the cells, a VLOOKUP, anything below the data range returns #N/A which
messes up my attempts to put totals on etc. Is there anyway I can make the
#N/A in this particular field "0" or anyway i can get excel to only move the
correct amount of data across to the 2nd sheet. I'm currently moving it
across in a very simple "=A2" formula from 1 sheet to the next. The vlookup
comes from looking up various currency rates. Thanks
 
Excel 2003 use
=IF(ISNA(Vlookup(...),0,Vlookup(...)))
replace Vlookup(...) with your formula.

Excel 2007 use
=IFERROR(Vlookup(...),0)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VLOOKUP returning #N/A result 2
Return 0 instead of N/A 2
#N/A outcomes in formulas 2
Return 0 instead of #n/a 1
How to display a 0 value rather than #N/A 2
Replace #N/A with 0 1
VLOOKUP - 0 instead of # N/A 8
Formula returns #N/A 1

Back
Top