Unwanted Zeros in Excel Chart Displayed Values

[Daniel Compton]

Hi, this is a very specific problem I have with an excel bar chart.
There must be a hero somewhere with a solution.

I am charting cells containing excel functions of the type
=IF(A1="","",A1)
=IF(A2="","",A2)
etc
Where cells A1, A2 can contain real number values, genuine zeros, or
genuine blanks

I have to show the Values on the chart above each bar.
The Real numbers and zeros display correctly but if there is a genuine
blank i still see an unwanted "0.0" value label on my chart. I cannot
entertain deleting them individually as I have a huge piece of macro
driven work which will print many thousands of different charts.

Furthermore, using N/A() does NOT work..e.g.
=IF(A1="",N/A(),A1)
as this then Displays an "N/A" on the chart instead. Also unwanted. I
want nothing on my chart at all for a blank.

I have looked high and low for a solution fruitlessly

Any help would be greatly appreciated

thanks
Daniel

 

[Ken Johnson]

Hi Daniel,
Try Tools>Options>Chart>Plot Empty cells as= not plotted (leave gaps)

Ken Johnson

 

[Ken Johnson]

Hi Daniel,

Or use the autofilter to hide the blanks then Tools>Options>Chart> tick
in Plot visible cells only.

Ken Johnson

 

[Daniel Compton]

Hi Ken,

the chart already has that setting, unfortunately it sees the function
in there and decides that doesn't count as empty and plots.

Daniel

 

[Daniel Compton]

Again, not that simple. The data itself is in large pivot tables and
cannot be filtered. The data in these pivot tables is dynamic and
changes all the time.