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

C

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?
 
C

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
 
J

joeu2004

Claus Busch said:
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.
 
C

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?
 
C

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top