Getting a chart to stop if the formula resutls in a blank cell

Hi,

I'm trying to make a chart that displays data for each month of the year.
The data itself is referenced from another location. this is the formula that
is in each cell currently:

=IF(ISERROR(VLOOKUP($G6,Results!$A$4:$P$368,4,FALSE)),"
",VLOOKUP($G6,Results!$A$4:$P$368,4,FALSE))

so my data has infor from January through July, but because I don't have
info yet from August through December, those cells are blank.

My problem is that my chart doesn't think those cells are blank, it
automatically plots them like there value is zero. I just would like my
chart to stop at those blank cells and not automatically default to zero. I
tried changing it under options/chart but it is already set to stop at blank
cells. I would appreciate any help I can get.

Thank you,

Markag

You may use the function NA() instead of the double quotes in the formula.
Something like:
=IF(ISERROR(VLOOKUP($G6,Results!$A$4:$P$368,4,FALSE)),NA(),VLOOKUP($G6,Results!$A$4:$P$368,4,FALSE))

The result will be #N/A for unfound data, and that will not be represented
as zero.

Hope this helps,
Miguel.

"markag" wrote:

> Hi,
>
> I'm trying to make a chart that displays data for each month of the year.
> The data itself is referenced from another location. this is the formula that
> is in each cell currently:
>
> =IF(ISERROR(VLOOKUP($G6,Results!$A$4:$P$368,4,FALSE)),"
> ",VLOOKUP($G6,Results!$A$4:$P$368,4,FALSE))
>
> so my data has infor from January through July, but because I don't have
> info yet from August through December, those cells are blank.
>
> My problem is that my chart doesn't think those cells are blank, it
> automatically plots them like there value is zero. I just would like my
> chart to stop at those blank cells and not automatically default to zero. I
> tried changing it under options/chart but it is already set to stop at blank
> cells. I would appreciate any help I can get.
>
> Thank you,
>
> Markag
>

Try something like this:

Let your base formula return an error, like this:
=VLOOKUP($G6,Results!$A$4:$P$368,4,FALSE)

Excel charts ignore error values.

THEN....
Apply Conditional Formatting to the plotted data...
Select the data (Assuming Cells A3:K3, with A3 as the active cell)

From the Excel main menu:
<format><conditional formatting>
Formula is: =ISERROR(A3)
Click the [Format...] button and set the font color to white.

That way, the errors won't chart and the error values won't be visible.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"markag" wrote:

> Hi,
>
> I'm trying to make a chart that displays data for each month of the year.
> The data itself is referenced from another location. this is the formula that
> is in each cell currently:
>
> =IF(ISERROR(VLOOKUP($G6,Results!$A$4:$P$368,4,FALSE)),"
> ",VLOOKUP($G6,Results!$A$4:$P$368,4,FALSE))
>
> so my data has infor from January through July, but because I don't have
> info yet from August through December, those cells are blank.
>
> My problem is that my chart doesn't think those cells are blank, it
> automatically plots them like there value is zero. I just would like my
> chart to stop at those blank cells and not automatically default to zero. I
> tried changing it under options/chart but it is already set to stop at blank
> cells. I would appreciate any help I can get.
>
> Thank you,
>
> Markag
>

Thank you, I would have been really stuck without that.

"Miguel Zapico" wrote:

> You may use the function NA() instead of the double quotes in the formula.
> Something like:
> =IF(ISERROR(VLOOKUP($G6,Results!$A$4:$P$368,4,FALSE)),NA(),VLOOKUP($G6,Results!$A$4:$P$368,4,FALSE))
>
> The result will be #N/A for unfound data, and that will not be represented
> as zero.
>
> Hope this helps,
> Miguel.
>
> "markag" wrote:
>
> > Hi,
> >
> > I'm trying to make a chart that displays data for each month of the year.
> > The data itself is referenced from another location. this is the formula that
> > is in each cell currently:
> >
> > =IF(ISERROR(VLOOKUP($G6,Results!$A$4:$P$368,4,FALSE)),"
> > ",VLOOKUP($G6,Results!$A$4:$P$368,4,FALSE))
> >
> > so my data has infor from January through July, but because I don't have
> > info yet from August through December, those cells are blank.
> >
> > My problem is that my chart doesn't think those cells are blank, it
> > automatically plots them like there value is zero. I just would like my
> > chart to stop at those blank cells and not automatically default to zero. I
> > tried changing it under options/chart but it is already set to stop at blank
> > cells. I would appreciate any help I can get.
> >
> > Thank you,
> >
> > Markag
> >