Removing #N/A from an "If/Then" statement

[Collin Ulvund]

I'm doing some report making for my job, and pieced together this formula:

=IF(D9=0,NA(),D9) with base formula of =IF(hostcell=0,NA(),hostcell)

The formula has worked wonderfully, but it leaves "#N/A" in the cell and my supervisor would like to not see anything within the table, as they are being included in this report.

Does anyone know how this could be achieved?

 

[Claus Busch]

Hi Collin,

Am Thu, 9 May 2013 07:53:32 -0700 (PDT) schrieb Collin Ulvund:

=IF(D9=0,NA(),D9) with base formula of =IF(hostcell=0,NA(),hostcell)

The formula has worked wonderfully, but it leaves "#N/A" in the cell and my supervisor would like to not see anything within the table, as they are being included in this report.

try:
=IF(D9<>0,D9,"")


Regards
Claus Busch

 

[joeu2004]

Am Thu, 9 May 2013 07:53:32 -0700 (PDT) schrieb Collin Ulvund:

=IF(D9=0,NA(),D9) with base formula of =IF(hostcell=0,NA(),hostcell)
[....] it leaves "#N/A" in the cell and my supervisor would
like to not see anything within the table, as they are being
included in this report.

try:
=IF(D9<>0,D9,"")

There is no reason to change the logic from D9=0 to D9<>0. The following
will work just as well:

=IF(D9=0,"",D9)

The advantage: if you have a lot of these formulas, you can use
Find-and-Replace to change them en masse. It is easy if they are grouped
together or in large groups. Select the range of formulas to change, press
ctrl+F, enter NA() in the "Find what" field, enter "" (two double quotes
next to each other ) in the "Replace with" field, and click on Replace All.

Caveat: NA() might have been chosen for a reason; for example, if you use
the range of values in a chart. Also, other formulas might depend on the
Excel error result; for example, using ISNUMBER(E9) somewhere else. It
would be prudent to copy the Excel file before making the change.

 

[Collin Ulvund]

The previous two formulas have been helpful, but unfortunately within my graphs, the formulas send the line to the baseline of the chart, making the chart look incorrect. Is there any way that this could be achieved without a placeholder within the table?

 

[Claus Busch]

Hi Collin,

Am Thu, 9 May 2013 08:37:03 -0700 (PDT) schrieb Collin Ulvund:

The previous two formulas have been helpful, but unfortunately within my graphs, the formulas send the line to the baseline of the chart, making the chart look incorrect. Is there any way that this could be achieved without a placeholder within the table?

select your diagram => Diagram tools => Select data => Settings for
hidden data or null values => Connect data points with line

Regards
Claus Busch