Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
How do i not plot values as zero?
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"? |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
How do i not plot values as zero?
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 "uscben" wrote in message ... 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"? |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
How do i not plot values as zero?
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" wrote in message ... 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 "uscben" wrote in message ... 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"? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I break values apart that are in the same cell? | Excel Worksheet Functions | |||
Continuous series plot | Charts and Charting in Excel | |||
Plot Wizard -- "Not enough system resources to display completely. | Charts and Charting in Excel | |||
how do I get the x-axis to plot values rather than categories in . | Charts and Charting in Excel | |||
HOW TO: Plot a Team Name on a chart based on two values | Charts and Charting in Excel |