Not using zeros in graphing.

[Guest]

I have a running workbook that has tons of information. I have added a sum
page in order to have all the data summed up in one simple place. I have
formulas that read back into the workbook to link to a cell. Depending on
what moth it is, that cell could be empty as it is a yearly wookbook. For
example, if this is August, then there is information in the workbook up to
August, but none after. With that said, the sum page has the #DIV/0! in the
cell which essentially equals zero. I also have graphs that I have linked to
this sum page. My problem is in order to keep the graphs up to date, I have
to physically go back to each graph and move the data range. I do this
because if I select for example, January through December, the graph goes
along till I have no data and drops to zero in the line graphing. How do I
prevent the graphs from using the zero(or the cell unless theres data there)
to graph with?

Any help would be appreciated.

Thank you.

 

[Tushar Mehta]

Option 1: replace the formula that yields a error value to return a NA()
instead of the error. For example, if you have =a1/b1, use =if(b1=0,na
(),a1/b1).

Option 2: adapt the ideas behind Dynamic Charts (http://www.tushar-
mehta.com/excel/newsgroups/dynamic_charts/index.html) so that instead of
using COUNTA() use COUNTIF() or some such alternative to decide how many
cells to include in the plot.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Multi-disciplinary business expertise
+ Technology skills
= Optimal solution to your business problem
Recipient Microsoft MVP award 2000-2005