Hiding Zero Values in Graph

[Balzyone]

I have been developing an automated metric dashboard in excel and it is
working out beautifully. I am working on some of the finishing touches and
have run into a minor snag. I have it set up so that there is a Display tab
(that has the visual dashboard), a Data Entry tab ( that contains all the
calculations to figure the metrics), and several Graph tabs (seperated
because the director likes it that way). The graphs that show up at the
bottom of the display are generated from the Graph tabs. The graph tabs get
their information from the entry tab calculations. In the entry tab, most of
the calculations are set up with =IF((**)="","",(******) type of formulas.
This was done to keep the columns that did not have data from affecting the
YTD calculations and to make the entry tab look cleaner. The thing I have
found is the " " area does not work in the graph and shows zeros for the
empty months. I would like to show the line graphs for the months that are
being reported and show no data for the rest of the months of the year
instead of the line dropping to zero at the end of the supplied data. I did
try the ISBLANK function, but it does not work with the set-up I have. Does
anyone know how I can hide the zeros in such a way that I will not need to
spend time each month changing information. So far everything is set up so
that each group can enter their own data and I do not have to change anything
but one reference cell. My goal was to set it up so that I did not have to
put work into this to maintain it.

 

[Bernard Liengme]

Have you tried =IF((**)="",NA(),(******)
best wishes

 

[bj]

the na() function will not show on a graph
to keep your YTD set up and display nice
I would use a hidden helper column with something like =if(a1="",na(),a1)
It will display as #NA

The plot will probably be what you want

 

[Balzyone]

I tried that and it did not work. It only added an error in my entry tab and
left the 0% in the graph. I want the zero's to go away in the graph.

 

[Saruman]

Try this:

Click and highlight the data label numbers that produce the zero figure
The entire series should now be highlighted
Right click on one of the highlighted numbers and click the Format Data
Labels
Click on the Number Tab and select custom in the Left Hand Pane
In the Type: Box for a whole number type a zero followed by ;;;(thats 3 semi
colons one after the other)
You should see this: 0;;;
If you need a decimal point, 0.0;;;(or 2 places etc)
If you need a percentage, 0%;;; (or the decimal points as above)
OK to close the window
Test a zero value
Hope that helps!!! Enjoy