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.

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

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Balzyone" wrote in message
...
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.

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" wrote:

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.

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.

"Bernard Liengme" wrote:

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

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Balzyone" wrote in message
...
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.

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

--
Saruman

---------------------------------------------------------------------------
All Outgoing Mail Scanned By Norton Antivirus 2003
---------------------------------------------------------------------------
"Balzyone" wrote in message
...
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.