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

[MNJ]

Is there a way not to show the term " #N/A" ? Instead I would wish to display a blank cell.

Thanks

[Carina33]

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]

I use this to test if the fields I am referencing are giving errors:

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

[johnske]

Check out the ISERROR function, e.g. =IF(ISERROR(AVERAGE(A1:A4)),"",AVERAGE(A1:A4))

[Bob Phillips]

Correct the error, why ignore it?

[moa]

sometimes it not an option.

[Bob Phillips]

For example?

[moa]

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]

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]

For example?

User ignorance.

[Bob Phillips]

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]

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]

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.

!

Thanks alot.

[Bob Phillips]

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.

!

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]

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]

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]

Ok. Thanks. Will try.