How do i not plot values as zero?

[uscben]

I am trying to create a running chart whereby information is added daily.
The information, as added, is run though a formula and is added to the chart.
I want only the new information to plot on the chart, not the cells where
information has not been added. The problem I am having is that the cells
where the information has not been added are plotting as "zero". I have the
option checked where it will "not plot empty cells", but since the cells
where data has not been entered contains a formula, it is not recognizing
this as an empty cell and plots it as zero. Is there a function that I can
use to tell the chart not to plot the formula as "zero"?

[Kelly O'Day]

USCBen:

When Excel sees a cell with a formula, it does not consider it empty. That's
why your don't plot empty cells configuration isn't doing what you want.

Your If formula will need to set the cell to NA() rather than "". Excel
recognizes NA() and places a #N/A in the cell. Your drop to zero problem
should be gone.

If you need any additional info see my tutorial
http://processtrends.com/pg_charts_missing_data.htm

...Kelly

koday@oprocesstrends.com


"uscben" <uscben@discussions.microsoft.com> wrote in message
news:74EE5CF8-AF19-421B-AE53-4D7CF45E6403@microsoft.com...
>I am trying to create a running chart whereby information is added daily.
> The information, as added, is run though a formula and is added to the
> chart.
> I want only the new information to plot on the chart, not the cells where
> information has not been added. The problem I am having is that the cells
> where the information has not been added are plotting as "zero". I have
> the
> option checked where it will "not plot empty cells", but since the cells
> where data has not been entered contains a formula, it is not recognizing
> this as an empty cell and plots it as zero. Is there a function that I
> can
> use to tell the chart not to plot the formula as "zero"?

[uscben]

This works well except for the fact that I do indeed have an average function
in the formula. So, as your tutorial states, the NA() disrupts the formula.
Is there a workaround for this (aside from averaging mauually i.e.
(A1+A2+A3....An) / n)?



"Kelly O'Day" wrote:

> USCBen:
>
> When Excel sees a cell with a formula, it does not consider it empty. That's
> why your don't plot empty cells configuration isn't doing what you want.
>
> Your If formula will need to set the cell to NA() rather than "". Excel
> recognizes NA() and places a #N/A in the cell. Your drop to zero problem
> should be gone.
>
> If you need any additional info see my tutorial
> http://processtrends.com/pg_charts_missing_data.htm
>
> ...Kelly
>
> koday@oprocesstrends.com
>
>
> "uscben" <uscben@discussions.microsoft.com> wrote in message
> news:74EE5CF8-AF19-421B-AE53-4D7CF45E6403@microsoft.com...
> >I am trying to create a running chart whereby information is added daily.
> > The information, as added, is run though a formula and is added to the
> > chart.
> > I want only the new information to plot on the chart, not the cells where
> > information has not been added. The problem I am having is that the cells
> > where the information has not been added are plotting as "zero". I have
> > the
> > option checked where it will "not plot empty cells", but since the cells
> > where data has not been entered contains a formula, it is not recognizing
> > this as an empty cell and plots it as zero. Is there a function that I
> > can
> > use to tell the chart not to plot the formula as "zero"?
>
>
>

[Kelly O'Day]

There are two approaches that I have seen:

1. Two column method - 1 column for raw data, 1 column with charting
formula to eliminate blank problem
2. One column method - use Sumproduct or Array formula with criteria to
get average for valid numbers only.

...Kelly


this will test to see if a valid average can be
"uscben" <uscben@discussions.microsoft.com> wrote in message
news:3A5A3B90-F747-4898-9B76-6E671C08C7C9@microsoft.com...
> This works well except for the fact that I do indeed have an average
> function
> in the formula. So, as your tutorial states, the NA() disrupts the
> formula.
> Is there a workaround for this (aside from averaging mauually i.e.
> (A1+A2+A3....An) / n)?
>
>
>
> "Kelly O'Day" wrote:
>
>> USCBen:
>>
>> When Excel sees a cell with a formula, it does not consider it empty.
>> That's
>> why your don't plot empty cells configuration isn't doing what you want.
>>
>> Your If formula will need to set the cell to NA() rather than "". Excel
>> recognizes NA() and places a #N/A in the cell. Your drop to zero problem
>> should be gone.
>>
>> If you need any additional info see my tutorial
>> http://processtrends.com/pg_charts_missing_data.htm
>>
>> ...Kelly
>>
>> koday@oprocesstrends.com
>>
>>
>> "uscben" <uscben@discussions.microsoft.com> wrote in message
>> news:74EE5CF8-AF19-421B-AE53-4D7CF45E6403@microsoft.com...
>> >I am trying to create a running chart whereby information is added
>> >daily.
>> > The information, as added, is run though a formula and is added to the
>> > chart.
>> > I want only the new information to plot on the chart, not the cells
>> > where
>> > information has not been added. The problem I am having is that the
>> > cells
>> > where the information has not been added are plotting as "zero". I
>> > have
>> > the
>> > option checked where it will "not plot empty cells", but since the
>> > cells
>> > where data has not been entered contains a formula, it is not
>> > recognizing
>> > this as an empty cell and plots it as zero. Is there a function that I
>> > can
>> > use to tell the chart not to plot the formula as "zero"?
>>
>>
>>

[CVH]

Thank you for your useful tutorial. I have been trying various workround for ages before discovering your advice.

USCBen:

When Excel sees a cell with a formula, it does not consider it empty. That's
why your don't plot empty cells configuration isn't doing what you want.

Your If formula will need to set the cell to NA() rather than "". Excel
recognizes NA() and places a #N/A in the cell. Your drop to zero problem
should be gone.

If you need any additional info see my tutorial
http://processtrends.com/pg_charts_missing_data.htm

...Kelly

koday@oprocesstrends.com


"uscben" <uscben@discussions.microsoft.com> wrote in message
news:74EE5CF8-AF19-421B-AE53-4D7CF45E6403@microsoft.com...
>I am trying to create a running chart whereby information is added daily.
> The information, as added, is run though a formula and is added to the
> chart.
> I want only the new information to plot on the chart, not the cells where
> information has not been added. The problem I am having is that the cells
> where the information has not been added are plotting as "zero". I have
> the
> option checked where it will "not plot empty cells", but since the cells
> where data has not been entered contains a formula, it is not recognizing
> this as an empty cell and plots it as zero. Is there a function that I
> can
> use to tell the chart not to plot the formula as "zero"?

[noman999]

I am using Excel 2007.

I want to Plot the Bar graph with non-zero value. I checked your tutorial, and used NA(), but still the Br graph show non zero value bar.

Could you please guide, how to solve this issue ?

[ataneja60]

Sort the table filter out the zero's and Blanks and graph plot adjusts automatically.