software

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 quot;zeroquot;. I have the
option checked where it will quot;not plot empty cellsquot;, 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 quot;zeroquot;?

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 quot;quot;. 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
processtrends.com/pg_charts_missing_data.htm

...Kelly

quot;uscbenquot; gt; wrote in message
...
gt;I am trying to create a running chart whereby information is added daily.
gt; The information, as added, is run though a formula and is added to the
gt; chart.
gt; I want only the new information to plot on the chart, not the cells where
gt; information has not been added. The problem I am having is that the cells
gt; where the information has not been added are plotting as quot;zeroquot;. I have
gt; the
gt; option checked where it will quot;not plot empty cellsquot;, but since the cells
gt; where data has not been entered contains a formula, it is not recognizing
gt; this as an empty cell and plots it as zero. Is there a function that I
gt; can
gt; use to tell the chart not to plot the formula as quot;zeroquot;?
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)?
quot;Kelly O'Dayquot; wrote:

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

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.

...Kellythis will test to see if a valid average can be
quot;uscbenquot; gt; wrote in message
...
gt; This works well except for the fact that I do indeed have an average
gt; function
gt; in the formula. So, as your tutorial states, the NA() disrupts the
gt; formula.
gt; Is there a workaround for this (aside from averaging mauually i.e.
gt; (A1 A2 A3....An) / n)?
gt;
gt;
gt;
gt; quot;Kelly O'Dayquot; wrote:
gt;
gt;gt; USCBen:
gt;gt;
gt;gt; When Excel sees a cell with a formula, it does not consider it empty.
gt;gt; That's
gt;gt; why your don't plot empty cells configuration isn't doing what you want.
gt;gt;
gt;gt; Your If formula will need to set the cell to NA() rather than quot;quot;. Excel
gt;gt; recognizes NA() and places a #N/A in the cell. Your drop to zero problem
gt;gt; should be gone.
gt;gt;
gt;gt; If you need any additional info see my tutorial
gt;gt; processtrends.com/pg_charts_missing_data.htm
gt;gt;
gt;gt; ...Kelly
gt;gt;
gt;gt;
gt;gt;
gt;gt;
gt;gt; quot;uscbenquot; gt; wrote in message
gt;gt; ...
gt;gt; gt;I am trying to create a running chart whereby information is added
gt;gt; gt;daily.
gt;gt; gt; The information, as added, is run though a formula and is added to the
gt;gt; gt; chart.
gt;gt; gt; I want only the new information to plot on the chart, not the cells
gt;gt; gt; where
gt;gt; gt; information has not been added. The problem I am having is that the
gt;gt; gt; cells
gt;gt; gt; where the information has not been added are plotting as quot;zeroquot;. I
gt;gt; gt; have
gt;gt; gt; the
gt;gt; gt; option checked where it will quot;not plot empty cellsquot;, but since the
gt;gt; gt; cells
gt;gt; gt; where data has not been entered contains a formula, it is not
gt;gt; gt; recognizing
gt;gt; gt; this as an empty cell and plots it as zero. Is there a function that I
gt;gt; gt; can
gt;gt; gt; use to tell the chart not to plot the formula as quot;zeroquot;?
gt;gt;
gt;gt;
gt;gt;