Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 18th 08, 01:15 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 94
Default Removing #N/A from formula results

Hi All,

Is there a way to remove #N/A as a result from a formula, for example to
return a blank or zero result instead?
--
Cheers,
Holly

  #2   Report Post  
Old January 18th 08, 01:21 AM posted to microsoft.public.excel.worksheet.functions
CLR CLR is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 594
Default Removing #N/A from formula results

Wrap your formula in an IF statement.....

=IF(ISNA(YourFormula),"",YourFormula)

Vaya con Dios,
Chuck, CABGx3


"Holly" wrote in message
...
Hi All,

Is there a way to remove #N/A as a result from a formula, for example to
return a blank or zero result instead?
--
Cheers,
Holly



  #3   Report Post  
Old January 18th 08, 01:26 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 8,856
Default Removing #N/A from formula results

Something along these lines:

=IF(ISNA(your_formula),"",your_formula)

This returns a blank instead of #N/A - change the "" to 0 if you
prefer a zero.

Hope this helps.

Pete

On Jan 18, 1:15*am, Holly wrote:
Hi All,

Is there a way to remove #N/A as a result from a formula, for example to
return a blank or zero result instead?
--
Cheers,
Holly


  #4   Report Post  
Old January 18th 08, 01:28 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 94
Default Removing #N/A from formula results

My Formula is a v lookup as follows:

=VLOOKUP(D223,Cashflow!P:R,3,FALSE)
--
Cheers,
Holly


"CLR" wrote:

Wrap your formula in an IF statement.....

=IF(ISNA(YourFormula),"",YourFormula)

Vaya con Dios,
Chuck, CABGx3


"Holly" wrote in message
...
Hi All,

Is there a way to remove #N/A as a result from a formula, for example to
return a blank or zero result instead?
--
Cheers,
Holly




  #5   Report Post  
Old January 18th 08, 01:34 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 8,856
Default Removing #N/A from formula results

Then change it to this:

=IF(ISNA(VLOOKUP(D223,Cashflow!P:R,3,FALSE)),"",VL OOKUP(D223,Cashflow!
P:R,3,FALSE))

Hope this helps.

Pete

On Jan 18, 1:28*am, Holly wrote:
My Formula is a v lookup as follows:

=VLOOKUP(D223,Cashflow!P:R,3,FALSE)
--
Cheers,
Holly



"CLR" wrote:
Wrap your formula in an IF statement.....


=IF(ISNA(YourFormula),"",YourFormula)


Vaya con Dios,
Chuck, CABGx3


"Holly" wrote in message
...
Hi All,


Is there a way to remove #N/A as a result from a formula, for example to
return a blank or zero result instead?
--
Cheers,
Holly- Hide quoted text -


- Show quoted text -




  #6   Report Post  
Old September 6th 12, 11:10 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2012
Posts: 2
Default Removing #N/A from formula results

On Thursday, January 17, 2008 8:15:01 PM UTC-5, Holly wrote:
Hi All,

Is there a way to remove #N/A as a result from a formula, for example to
return a blank or zero result instead?
--
Cheers,
Holly


Awesome!! Big help!! Thank you very much!!
  #7   Report Post  
Old September 6th 12, 11:10 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2012
Posts: 2
Default Removing #N/A from formula results

On Thursday, January 17, 2008 8:21:08 PM UTC-5, CLR wrote:
Wrap your formula in an IF statement.....

=IF(ISNA(YourFormula),"",YourFormula)

Vaya con Dios,
Chuck, CABGx3


"Holly" wrote in message
...
Hi All,

Is there a way to remove #N/A as a result from a formula, for example to
return a blank or zero result instead?
--
Cheers,
Holly


Awesome!! Big Help! Thank you very much!
  #8   Report Post  
Old March 4th 17, 04:58 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2017
Posts: 1
Default Removing #N/A from formula results

Hi, what if my formula a

=VLOOKUP(E4,'Hidden Local Database'!$L$1:M$5000,2,FALSE)

=C4*G4

=sum(I4:I300)

Can you write me a formula returning 0 instead of #N/A. Thanks alot.
  #9   Report Post  
Old March 4th 17, 01:28 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2016
Posts: 5
Default Removing #N/A from formula results

If you have XL2007 or later, you could just wrap your formula in IFERROR()

=IFERROR(your formula,0)


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Need formula help! Removing a neg# & #DIV/0! j5b9721 Excel Discussion (Misc queries) 4 September 2nd 07 01:43 PM
Clear Cell Without Removing Formula Scott Excel Worksheet Functions 4 March 27th 06 12:07 AM
keep number when removing formula Cindy B Excel Discussion (Misc queries) 2 March 1st 06 06:30 PM
How do I view formula results intead of formula in excel? davidinatlanta Excel Worksheet Functions 4 February 7th 06 03:02 PM
Cannot enter formula in a cell after removing a circular formula Big Corona Excel Worksheet Functions 0 April 5th 05 06:07 PM


All times are GMT +1. The time now is 08:12 PM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017