A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Replacing #N/A with a '0' (zero)



 
 
Thread Tools Display Modes
  #1  
Old April 19th 05, 11:33 AM
neilcarden neilcarden is offline
Junior Member
 
First recorded activity by ExcelBanter: Apr 2005
Posts: 26
Default Replacing #N/A with a '0' (zero)

Hi all,

I'm using a Vlookup to pull figures over from another sheet. Is there a way that when there isn't any data to pull across, instead of displaying #N/A it could display a zero?

Thanks in advance...

Neil
Ads
  #2  
Old April 19th 05, 01:45 PM
Bob Phillips
external usenet poster
 
Posts: n/a
Default

=IF(ISNA(lookup_formula),0,lookup_formula)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"neilcarden" > wrote in message
...
>
> Hi all,
>
> I'm using a Vlookup to pull figures over from another sheet. Is there a
> way that when there isn't any data to pull across, instead of displaying
> #N/A it could display a zero?
>
> Thanks in advance...
>
> Neil
>
>
> --
> neilcarden



  #3  
Old April 19th 05, 01:46 PM
Niek Otten
external usenet poster
 
Posts: n/a
Default

=IF(ISNA(YourFormula),0,YourFormula)

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"neilcarden" > wrote in message
...
>
> Hi all,
>
> I'm using a Vlookup to pull figures over from another sheet. Is there a
> way that when there isn't any data to pull across, instead of displaying
> #N/A it could display a zero?
>
> Thanks in advance...
>
> Neil
>
>
> --
> neilcarden



  #4  
Old April 19th 05, 01:53 PM
Vinaya
external usenet poster
 
Posts: n/a
Default


IF(ISERROR(VLOOKUP(C1,$A$1:$B$4,2,0)),0,(VLOOKUP(C 1,$A$1:$B$4,2,0)))

neilcarden wrote:
> Hi all,
>
> I'm using a Vlookup to pull figures over from another sheet. Is there a
> way that when there isn't any data to pull across, instead of displaying
> #N/A it could display a zero?
>
> Thanks in advance...
>
> Neil
>
>


  #5  
Old April 20th 05, 10:19 AM
neilcarden neilcarden is offline
Junior Member
 
First recorded activity by ExcelBanter: Apr 2005
Posts: 26
Default

Thanks again you guys!!! Excellent...
  #6  
Old April 20th 05, 02:49 PM
galimi
external usenet poster
 
Posts: n/a
Default

=if(isna(cell_ref),0,cell_ref)

Replace cell_ref with the one cell range being referenced

http://HelpExcel.com

"neilcarden" wrote:

>
> Thanks again you guys!!! Excellent...
>
>
> --
> neilcarden
>

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
replacing #N/A with 0 Mark Excel Discussion (Misc queries) 3 April 7th 05 10:02 PM
Replacing characters OhhAhh Excel Worksheet Functions 10 March 4th 05 12:28 AM
replacing data Tammy Excel Discussion (Misc queries) 4 March 3rd 05 07:33 PM
Replacing text in Excel files on disk? Desperate Don Excel Discussion (Misc queries) 0 March 1st 05 04:23 PM
Filtering and replacing values through a macro vic Excel Worksheet Functions 1 January 27th 05 09:53 PM


All times are GMT +1. The time now is 02:42 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.