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/
_______


MarianneR wrote:

> 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
>
>