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

[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.

 

[Frank Kabel]

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

 

[Bob Phillips]

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)

 

[Jerry W. Lewis]

=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

 

[Guest]

Thanks everyone! :O)

I need to enrol on an advanced excel course.

 

[Norman Harker]

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.

 

[Chip Pearson]

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

 

[Chip Pearson]

Oops, replied to the wrong message. Ignore my reply.


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