Search code examples
excelexcel-formulaexcel-charts

Don't show #N/A in Excel cell but also don't treat it as a 0 value in a chart

I have a column whose content is conditioned upon another column:

=IF(B8<>"";B8/($J$2 * $J$2);"")

This column is the line data for a chart. It works fine when the IF condition is met. However, any ELSE "" value is considered as 0 in the chart and the line crashes down to the bottom.

The alternative is to use

=IF(B8<>"";B8/($J$2 * $J$2);NA())

But then it looks ugly in the spreadsheet with loads of #N/A in the column.

Any solution aside from putting a conditional formatting on top which makes the font color white when the value is #N/A?

Solution

  • You can plot with a Named Range which will evaluate 0 to #NA

    If the data you're plotting is in C3:C8:

    1. Create a Name for that Range which evaluates 0 as #NA (I've called the Range "PLOT", but you can give it any name you want): =IF(Sheet1!$C$3:$C$8=0,NA(),Sheet1!$C$3:$C$8)
    2. Use the Named range in the Chart: =SERIES(,,Book1!PLOT,1)

    This will allow you to keep the ELSE-cells as "" in your sheet, whilst still showing up as #NA to the Chart and hence not collapsing the line to zero

    NB: See here for notes on how to use Named Ranges in charts, particularly:

    If your references are Names (Named Ranges), you need to qualify the Name with the scope of the Name, that is, either its parent worksheet or the parent workbook.

    =SERIES(Sheet1!TheSeriesName,Sheet1!TheXValues,Sheet1!TheYValues,1)

    You can enter the Name qualified by the worksheet, and if the Name is scoped to the workbook, Excel will fix it for you.