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

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

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

=IF(ISNA(your_formula),0,your_formula)
--
David Biddulph

Max wrote:
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

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

Excel 2003 use
=IF(ISNA(Vlookup(...),0,Vlookup(...)))
replace Vlookup(...) with your formula.

Excel 2007 use
=IFERROR(Vlookup(...),0)
"Max" wrote:

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

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

That is perfect - thanks for your help! My excel education is a work in
progress...

Max

"Sheeloo" wrote:

Excel 2003 use
=IF(ISNA(Vlookup(...),0,Vlookup(...)))
replace Vlookup(...) with your formula.

Excel 2007 use
=IFERROR(Vlookup(...),0)
"Max" wrote:

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