Charts that ignore certain data

[Mark R]

I have a chart that takes data from a list. Some of the data fields have #NA.

How do I get i to ignore those details so it only plots based upon the true
figures

Abbey 0
First National1
RBS 1
#N/A #N/A
#N/A #N/A
#N/A #N/A
#N/A #N/A

 

[Jon Peltier]

Generate dynamic names to reference the data ranges used by the charts. If
you've got this data in columns A and B, you could define these names
(Insert menu > Names > Define)

Name: MyVals
RefersTo:
=OFFSET($B$1,0,0,COUNT($B$1:$B$65536),1)

Name: MyCats
RefersTo:
=OFFSET(MyVals,0,-1)

Use these names as the source data of the chart series. Go to Chart menu >
Source Data > Series, select the appropriate series, for X Values (or
Category Labels) enter =Sheet1!MyCats (or if there's a space in the sheet
name, ='Sheet 1'!MyCats), and for Y Values, enter =Sheet1!MyVals (or ='Sheet
1'!MyVals).

For more information and examples:
http://peltiertech.com/Excel/Charts/DynamicColumnChart1.html
http://peltiertech.com/Excel/Charts/Dynamics.html

- Jon