Hide #DIV/0! in Excel Chart

[mjg060468]

I am currently compiling some data and transfering that data into various charts.

I am currently using the =IF(SUM(E9,E19,E29,E39),SUM(E9,E19,E29,E39),NA()) statement to hide 0 values or blank cells in the chart.

However on the chart I need to add a line that shows an average of values, so I entered

=IF(AVERAGE(B12,B22,B32,B42),AVERAGE(B12,B22,B32,B42),NA()), which works, however where there is no data it is showing as a #DIV/0! rather than the #N/A. It transfers the #DIV/0! to a 0 value on the chart, how can I change that to not show up on the chart <!-- / message --><!-- BEGIN TEMPLATE: ad_showthread_firstpost_sig --><!-- END TEMPLATE: ad_showthread_firstpost_sig -->

<!-- controls -->

 

[DonkeyOte]

x-posted: http://www.excelforum.com/excel-charting/745194-hide-div-0-in-excel-chart.html

(FWIW, Andy Pope on the case above)