Charting with zeros or DIV/0 values

[MarianneR]

Hi - this might have an easy solution, but I'm a bit stuck.

I've created a spreadsheet for data entry by another party. This data
will be entered on a monthly basis and I only want to update it
occasionally. So, I have control charts set up with the basic formulae
(percent, mean, upper and lower control limits) in there. I was
wondering if there was any way to have all of this data in the range of
the chart without having the chart bottom out (i.e. plotting zeros).

For example, my columns _might_ look something like this:
Date.........Total...Defects...%ofDefects...Mean....UCL...LCL

And this _might_ be one data point:
Mar2005...230....23............10%.............10%.....0%....50%

So, I would have the percent, mean and upper and lower control limits
set to calculate automatically, however, when there's no denominator
(Total) present, there is naturally an error. I've taken care of the
error appearance with an IF function, but when I set up the graphs,
there are zeroes for the subsequent months because they have no data.

Essentially, I would like to know if I can have the calculations set up
to be captured by the chart and NOT have zeroes show up on the chart. I
was reading something about dynamic range, but couldn't figure it out.
If you all believe that is the best option, I'm happy to go learn more
about it. I hope this makes sense.

Thanks so much,
Marianne

 

[Jon Peltier]

Your formula is like this:

IF(<something>,<value>,"")

Change this to

IF(<something>,<value>,NA())

This produces the #N/A error in the worksheet, which is ugly there, but
which is ignored in a line or scatter chart. Debra Dalgleish shows how
to hide the errors in the sheet with conditional formatting:

http://contextures.com/xlCondFormat03.html#Errors

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______