how do i get my chart to ignore the cells where the formula has returned #n/a

[missydanni]

Hi,

I have a chart based on a data series which will dynamically update based upon the selection of a data validation list within the worksheet.
I need the data series which populates the chart to remain the same as the table size depends upon the option selected from the drop down.

I have created the formulas so that is populates cells where the formula would return a zero or #DIV/0! with #n/a however my graph continues to show theses cells within the chart but without any data plotted against them.

I have tried to show you what I mean below, however if anyone can shed any light as to how to get my chart to ignore the #n/a then I would be very grateful.

Thanks in advance ,

Option 1 Data Series
A B C D
1 Total % *** %
2 Jan 391 0.7% 0.7%
3 Feb 2673 4.6% 5.3%
4 Mar 3812 6.6% 11.8%
5 Apr 82 0.1% 12.0%
6 May 9930 17.1% 29.0%
7 Jun 3554 6.1% 35.2%
8 Jul 164 0.3% 35.4%
9 Aug 6454 11.1% 46.5%
10 Sep 676 1.2% 47.7%
11 Oct 736 1.3% 49.0%
12 Nov 563 1.0% 49.9%
13 Dec 279 0.5% 50.4%
14 Jan 14722 25.3% 75.7%
15 Feb 6480 11.1% 86.9%
16 Mar 405 0.7% 87.6%
17 Apr 317 0.5% 88.1%
18 May 1839 3.2% 91.3%
19 Jun 383 0.7% 91.9%
20 Jul 3288 5.7% 97.6%
21 Aug 1125 1.9% 99.5%
22 Sep 278 0.5% 100.0%
22 58151

Option 2 Data Series
A B C D
1 Total % *** %
2 Jan 10321 17.7% 17.7%
3 Dec 900 1.5% 19.3%
4 Jun 3554 6.1% 25.4%
5 Apr 317 0.5% 26.0%
6 May 1839 3.2% 29.1%
7 Aug 487 0.8% 30.0%
8 Oct 1225 2.1% 32.1%
9 Jan 25898 44.5% 76.6%
10 May 13610 23.4% 100.0%
11 #N/A #N/A
12 #N/A #N/A
13 #N/A #N/A
14 #N/A #N/A
15 #N/A #N/A
16 #N/A #N/A
17 #N/A #N/A
18 #N/A #N/A
19 #N/A #N/A
20 #N/A #N/A
21 #N/A #N/A
22 58151

[missydanni]

Sorry that data copy didn't work and I am unable to upload an example from my work computer

[Andy Pope]

The #N/A approach suppress data markers or plots columns/bars as zero.

If you want Months with #N/A to actually be excluded from the chart you will either need to hide those rows (manually/autofilter/VBA code) and have chart not display hidden cells.
Or use dynamic named ranges to adjust the information displayed in the chart.