PDA

View Full Version : Solved: Not displaying N/A but showing a blank ""



MNJ
12-14-2006, 02:42 AM
Is there a way not to show the term " #N/A" ? Instead I would wish to display a blank cell.

Thanks

Carina33
12-14-2006, 03:03 AM
Hi,

I have also problem with this but have you tried putting "" instread of for instance, 0 in a the condition. If you do this than the cell should show blank rather than #N/A.
For example rather than this
=IF(D17>F17,0,D17

Try this

=IF(D17>F17,"",D17

Not sure if this helps but hope so.

Rgs
C

moa
12-14-2006, 03:04 AM
I use this to test if the fields I am referencing are giving errors:

=IF(OR(ISNA(J19),ISNA(J31)),"",J19+J31)

johnske
12-14-2006, 03:09 AM
Check out the ISERROR function, e.g. =IF(ISERROR(AVERAGE(A1:A4)),"",AVERAGE(A1:A4))

Bob Phillips
12-14-2006, 04:29 AM
Correct the error, why ignore it?

moa
12-14-2006, 04:34 AM
sometimes it not an option.

Bob Phillips
12-14-2006, 05:16 AM
For example?

moa
12-14-2006, 05:43 AM
I dunno... Maybe if you are ranking suppliers and you want to exclude some who don't offer a certain option. You could force an error (if a cell = ?0 then cell with subtotal = NA()) then the final total will be #NA and all you need to do is test (isna()) and return an empty string then it is not ranked. I'm sure there are other options (which is probably what you are getting at) but it's pretty handy without being too time consuming.

Bob Phillips
12-14-2006, 12:33 PM
That is not a case of '... not an option ...', but a specific design decision. And a bad design decision IMO, to deliberately create an error that could mix with real errors compounds an alraedy bad problem of ignoring errors. In my experience, ignoring errors will inevitably mean that it will come back and bite you one day, when something is ignored that shouldn't have been , and a bad analysis/business decision is made.

malik641
12-14-2006, 01:20 PM
For example?
User ignorance.

Bob Phillips
12-14-2006, 03:30 PM
User ignorance.

I wouldn't have thought so, we are talking developer here not user. If some user actions causes a NA then that is a design/coding problem.

johnske
12-14-2006, 04:46 PM
I agree with Bob here, IMO it's always better to use data validation to exclude any errors than to ignore them (even tho that's clearly an option) :)

MNJ
12-14-2006, 05:50 PM
:)

I've got this formula :


=IF(ISNA(J33),"", VLOOKUP(9.99999999999999E+307,K33:K41,1))


I want to omit the N/A is because when my reference " K33:K41" is blank, I don't wish to see the display.

This is what i tried. But I've got this circular reference and therefore a zero instead of null.

:help !

Thanks alot.

Bob Phillips
12-14-2006, 05:59 PM
:)

I've got this formula :


=IF(ISNA(J33),"", VLOOKUP(9.99999999999999E+307,K33:K41,1))

I want to omit the N/A is because when my reference " K33:K41" is blank, I don't wish to see the display.

This is what i tried. But I've got this circular reference and therefore a zero instead of null.

:help !

Thanks alot.

The point that Johnske and I are making is that you should fix the formula in J33 so that it doesn't resolve to NA. Far better IMO to make an explicit decision there that stops an NA being created than to try ignoring it down the line.

malik641
12-14-2006, 06:15 PM
If some user actions causes a NA then that is a design/coding problem. I agree with that. When developing / designing I try to think of every possible error that could come up and the best way to avoid / solve it.

Zack Barresse
12-14-2006, 06:25 PM
Agreed, it is always better to take care of the error at its source rather than taking care of it down the road. This can prove difficult and involved in some cases. An excellent write-up on these circumstances can be found on a post by Aladin Akyurek at the MrExcel board: http://www.mrexcel.com/board2/viewtopic.php?t=62102

MNJ
12-14-2006, 06:44 PM
Ok. Thanks. Will try.