RE: How can I make a Chart data series treat blanks as "Empty" cells

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Option 1: Replace the "" in your formula with NA().

Option 2: If you don't like the ugly #N/A or it messes up downstream
calculations, use another column in which you have the NA() instead of
the "". Plot this new column but use the original for other work.

Option 3: Adapt the ideas behind
Dynamic Charts
http://www.tushar-mehta.com/excel/newsgroups/dynamic_charts/index.html
If you use COUNT() instead of COUNTA(), the solution will include only
those cells with numbers and exclude the ""s.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <D4279D04-1449-4565-9038-4A7474C39744@xxxxxxxxxxxxx>,
groj@xxxxxxxxxxxxxxxxxxxxxxxxx says...
> Have you found the answer yet?
> I am trying to plot a data series also through months. If the month is not
> here yet, the cell is calculated to be blank, but the chart is plotting it as
> zero. Is this similar to your situation? How did you fix it?
> Thanks
>
> "XLADLK" wrote:
>
> > My Data Series contains blanks as the result of formula calculations. The
> > location and number of blanks can change with each recalculation as input
> > criteria change. I can make my Chart ignore an "Empty" cell easy enough but
> > my cells still contain the formula which created the blank ("") cell and
> > therefore, not truely empty. I want to plot the series, about 7000 values,
> > without the blanks showing up as zeros and ruining my trendline fit.
> > Removing those rows is not an option as I have to retain the x-axis value as
> > place holder on the chart.
> >
> > Thanks for your help; nothing is as simple as it seems when you start
> >
> > Dan
>
.

Relevant Pages

  • Re: Ignor zero values
    ... while the other returns values or something that doesn't mess up further ... Chart the one set of values, make your calculations from ... >> subsequently reference the data series for further calculations. ...
    (microsoft.public.excel.charting)
  • Re: Excel bar charts with percentages
    ... Calculate the percentage in your worksheet. ... Add the data to the chart. ... copying the data series from the worksheet & Paste Special onto the chart. ... Charts aren't for doing calculations; they are for displaying the results ...
    (microsoft.public.excel.charting)
  • RE: Adding a chart to large workbook brings workbook activity to a
    ... Anyhow, I hate to tell you this, but the problem is Excel 2007. ... chart took an equally long time to take effect. ... This slowdown in charting in Excel 2007 has been documented and/or commented ... Thinking that the issue was the number of calculations occurring in the ...
    (microsoft.public.excel.misc)
  • RE: Adding a chart to large workbook brings workbook activity to a
    ... Anyhow, I hate to tell you this, but the problem is Excel 2007. ... chart took an equally long time to take effect. ... This slowdown in charting in Excel 2007 has been documented and/or commented ... Thinking that the issue was the number of calculations occurring in the ...
    (microsoft.public.excel.misc)
  • Re: Loop and variable number of table
    ... amounts are huge. ... computer would not be able to do calculations / add filters on such a great ... When the number of rows is fixed, I can draw all data on a chart without any ... this will pay off in this one task alone, ...
    (microsoft.public.access.modulesdaovba)