Hide #DIV/0! in Excel Chart

**mjg060468** · 09-10-2010, 09:03 AM

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

**Andy Pope** · 09-10-2010, 10:55 AM

=if(iserror(average(b12,b22,b32,b42)),na(),average(b12,b22,b32,b42))

**DonkeyOte** · 09-10-2010, 11:02 AM

Welcome to the Board.

However, note, if you opt to x-post the same question on multiple forums disclose all links accordingly:

http://www.mrexcel.com/forum/showthread.php?t=494037

**mjg060468** · 09-10-2010, 11:25 AM

Originally Posted by Andy Pope

=if(iserror(average(b12,b22,b32,b42)),na(),average(b12,b22,b32,b42))

Brilliant, thank you for your help

**NoraG** · 03-13-2014, 05:49 AM

This works for hiding the 'cells' that have DIV/0, but still leaves my X-axis data showing - this data is week by week, ie 07/03/14 , then 14/03/14 , etc. So today I have a chart showing the data for 07/03/14 and previous weeks, but still showing weeks from 14/03/14 with empty y-axis. Is there some way to NOT show the X-axis until N/A changes to an actual figure in the next week?