How to avoid displaying the empth rows and columns in a excel chart

[saran26]

Hi All,

I have a row and column in my excel which i am using to display a chart.

The values of row and column are populated through the formula, if the result of the formula contains nothing then i am displaying #NA in both the row and column.

using the formula like this

=IF(LEN(InternalRaFun!E2)>0,InternalRaFun!E2,NA())

if the row has a value and column contains #N/A, it gets skipped in the chart
, but if both row and column contains #N/A then the chart displays #N/A, how to avoid this.
Example:-

Working fine

Plan rate/hr
A 10
B #N/A
C 20

But it is displaying the #N/A in chart if it is like this

Plan rate/hr
A 10
#N/A #N/A
C 10

Thanks

Saran

Please help me out

[Andy Pope]

The use of NA will only surpress data points. It will not remove a complete series from the chart.

To do that you would need to use Autofilter or code