How do I change '#N/A' to a value so I can sum a column?

  • Thread starter Thread starter John D
  • Start date Start date
J

John D

After using VLOOKUP, I have a number of #N/A's. Can
someone please advise as to what I need to do to convert
them to '0' so I can sum the column.

Many thanks.
 
Hi
change your VLOOKUP formula to something like
=IF(ISNA(your_vlookup_formula),0,your_vlookup_formula)
 
Hi John,

=IF(ISNA(vlookup_formula),0,vlookup_formula)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
=IF(ISNA(formula),0,formula)

Alternately, you could use "" instead of 0 if you are summing with the
SUM() function instead of +, "" would be ignored by AVERAGE(), unlike 0.

Jerry
 
Hi!

Re: "I need to enrol on an advanced excel course"

No need! Just hang around here and try and contribute your own
answers.

It's by far the best and most enjoyable way of improving your Excel
skills.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
You can use comments to do this. To make the comments visible at
all times, go to the Tools menu, choose Options, then the View
tab. There, select the "Comment And Indicator" option in the
Comments section.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Back
Top