Prev: graph creation from table using cells with links not possible
Next: Axis labeling
From: C Kreig on 10 Nov 2009 11:30
I have a chart that shows the fiscal year. In the months that currently do
not have data it is charting to a zero for the month. How can I get the
chart not to show the next month's that have a zero for a value? The catch
is that some months maybe zero and the months that do not have a value are
currently empty. Below is the formula that I am currently using.

=IF(SUM('Global KPI Monitoring chart'!F7:F8)=0,IF(AND('Global KPI Monitoring
chart'!F7="",
'Global KPI Monitoring chart'!F8=""),"",0),SUM('Global KPI Monitoring
chart'!F7:F8))
From: Luke M on 10 Nov 2009 11:40
Instead of an output of "", have the formula output the #N/A error (as charts
recognize this data as not applicable)

I believe something like this:

=IF(SUM('Global KPI Monitoring chart'!F7:F8)=0,IF(AND('Global KPI Monitoring
chart'!F7="",
'Global KPI Monitoring chart'!F8=""),NA(),0),SUM('Global KPI Monitoring
chart'!F7:F8))
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"C Kreig" wrote:

> I have a chart that shows the fiscal year. In the months that currently do
> not have data it is charting to a zero for the month. How can I get the
> chart not to show the next month's that have a zero for a value? The catch
> is that some months maybe zero and the months that do not have a value are
> currently empty. Below is the formula that I am currently using.
>
> =IF(SUM('Global KPI Monitoring chart'!F7:F8)=0,IF(AND('Global KPI Monitoring
> chart'!F7="",
> 'Global KPI Monitoring chart'!F8=""),"",0),SUM('Global KPI Monitoring
> chart'!F7:F8))
From: C Kreig on 10 Nov 2009 12:12
Luke-
If I do that, I get ?Name error

"Luke M" wrote:

> Instead of an output of "", have the formula output the #N/A error (as charts
> recognize this data as not applicable)
>
> I believe something like this:
>
> =IF(SUM('Global KPI Monitoring chart'!F7:F8)=0,IF(AND('Global KPI Monitoring
> chart'!F7="",
> 'Global KPI Monitoring chart'!F8=""),NA(),0),SUM('Global KPI Monitoring
> chart'!F7:F8))
> --
> Best Regards,
>
> Luke M
> *Remember to click "yes" if this post helped you!*
>
>
> "C Kreig" wrote:
>
> > I have a chart that shows the fiscal year. In the months that currently do
> > not have data it is charting to a zero for the month. How can I get the
> > chart not to show the next month's that have a zero for a value? The catch
> > is that some months maybe zero and the months that do not have a value are
> > currently empty. Below is the formula that I am currently using.
> >
> > =IF(SUM('Global KPI Monitoring chart'!F7:F8)=0,IF(AND('Global KPI Monitoring
> > chart'!F7="",
> > 'Global KPI Monitoring chart'!F8=""),"",0),SUM('Global KPI Monitoring
> > chart'!F7:F8))
From: C Kreig on 10 Nov 2009 12:21
Luke-
I got the formula to work, however it is still showing a value of zero on my
chart.
Any suggestions?

"Luke M" wrote:

> Instead of an output of "", have the formula output the #N/A error (as charts
> recognize this data as not applicable)
>
> I believe something like this:
>
> =IF(SUM('Global KPI Monitoring chart'!F7:F8)=0,IF(AND('Global KPI Monitoring
> chart'!F7="",
> 'Global KPI Monitoring chart'!F8=""),NA(),0),SUM('Global KPI Monitoring
> chart'!F7:F8))
> --
> Best Regards,
>
> Luke M
> *Remember to click "yes" if this post helped you!*
>
>
> "C Kreig" wrote:
>
> > I have a chart that shows the fiscal year. In the months that currently do
> > not have data it is charting to a zero for the month. How can I get the
> > chart not to show the next month's that have a zero for a value? The catch
> > is that some months maybe zero and the months that do not have a value are
> > currently empty. Below is the formula that I am currently using.
> >
> > =IF(SUM('Global KPI Monitoring chart'!F7:F8)=0,IF(AND('Global KPI Monitoring
> > chart'!F7="",
> > 'Global KPI Monitoring chart'!F8=""),"",0),SUM('Global KPI Monitoring
> > chart'!F7:F8))
From: Luke M on 10 Nov 2009 14:03
Hmm. You might want to take a look at dynamic charting techniques to help
limit the amount of data going into your chart.

http://peltiertech.com/Excel/Charts/Dynamics.html

It appears you are trying not only keep the data from months that have no
data from being plotted, but also keep those months from appearing on the
axis, correct?
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"C Kreig" wrote:

> Luke-
> I got the formula to work, however it is still showing a value of zero on my
> chart.
> Any suggestions?
>
> "Luke M" wrote:
>
> > Instead of an output of "", have the formula output the #N/A error (as charts
> > recognize this data as not applicable)
> >
> > I believe something like this:
> >
> > =IF(SUM('Global KPI Monitoring chart'!F7:F8)=0,IF(AND('Global KPI Monitoring
> > chart'!F7="",
> > 'Global KPI Monitoring chart'!F8=""),NA(),0),SUM('Global KPI Monitoring
> > chart'!F7:F8))
> > --
> > Best Regards,
> >
> > Luke M
> > *Remember to click "yes" if this post helped you!*
> >
> >
> > "C Kreig" wrote:
> >
> > > I have a chart that shows the fiscal year. In the months that currently do
> > > not have data it is charting to a zero for the month. How can I get the
> > > chart not to show the next month's that have a zero for a value? The catch
> > > is that some months maybe zero and the months that do not have a value are
> > > currently empty. Below is the formula that I am currently using.
> > >
> > > =IF(SUM('Global KPI Monitoring chart'!F7:F8)=0,IF(AND('Global KPI Monitoring
> > > chart'!F7="",
> > > 'Global KPI Monitoring chart'!F8=""),"",0),SUM('Global KPI Monitoring
> > > chart'!F7:F8))
 |  Next  |  Last
Pages: 1 2
Prev: graph creation from table using cells with links not possible
Next: Axis labeling