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 Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

How do i change cell display from #N/A to 0?



 
 
Thread Tools Display Modes
  #1  
Old January 18th 07, 10:12 PM posted to microsoft.public.excel.misc
osubigdog
external usenet poster
 
Posts: 1
Default How do i change cell display from #N/A to 0?

when the result of a formula is displayed in a cell, i want it to read "0"
not "#N/A" so that i can use it to link to other cells. how do i accomplish
this?
Ads
  #2  
Old January 18th 07, 10:17 PM posted to microsoft.public.excel.misc
JE McGimpsey
external usenet poster
 
Posts: 4,624
Default How do i change cell display from #N/A to 0?

One way:

=IF(ISNA(<your formula>),0,<your formula>)

In article >,
osubigdog > wrote:

> when the result of a formula is displayed in a cell, i want it to read "0"
> not "#N/A" so that i can use it to link to other cells. how do i accomplish
> this?

  #3  
Old January 20th 07, 03:29 AM posted to microsoft.public.excel.misc
Chip Pearson
external usenet poster
 
Posts: 7,247
Default How do i change cell display from #N/A to 0?

Just for the record, in Excel 2007 you can use the new IFERROR function.
E.g.,

=IFERROR(your_formula, result_if_error)



"JE McGimpsey" > wrote in message
...
> One way:
>
> =IF(ISNA(<your formula>),0,<your formula>)
>
> In article >,
> osubigdog > wrote:
>
>> when the result of a formula is displayed in a cell, i want it to read
>> "0"
>> not "#N/A" so that i can use it to link to other cells. how do i
>> accomplish
>> this?



  #4  
Old January 20th 07, 06:00 AM posted to microsoft.public.excel.misc
JE McGimpsey
external usenet poster
 
Posts: 4,624
Default How do i change cell display from #N/A to 0?

And, if you'd like, it's easy to implement IFERROR() in previous
versions, though usually less efficiently:

Public Function IfError(ByRef vTest As Variant, _
Optional ByRef vDefault _
As Variant = vbNullString) As Variant
If IsError(vTest) Then
IfError = vDefault
Else
IfError = vTest
End If
End Function


In article >,
"Chip Pearson" > wrote:

> Just for the record, in Excel 2007 you can use the new IFERROR function.
> E.g.,
>
> =IFERROR(your_formula, result_if_error)
>
>
>
> "JE McGimpsey" > wrote in message
> ...
> > One way:
> >
> > =IF(ISNA(<your formula>),0,<your formula>)

 




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
Using an offset formula for the reference in a relative reference Cuda Excel Worksheet Functions 6 November 15th 06 05:12 PM
How do I display the result of one cell each time a change anothe. Mike S Excel Worksheet Functions 1 September 8th 06 08:17 AM
Help with this conditional IF statement C-Dawg Excel Discussion (Misc queries) 3 May 15th 06 06:01 PM
Cell Change Color - Need Help alani New Users to Excel 3 June 29th 05 03:50 PM
GET.CELL Biff Excel Worksheet Functions 2 November 24th 04 07:16 PM


All times are GMT +1. The time now is 06:15 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.