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


G

Guest

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
 
Ad

Advertisements

G

Guest

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.
 
G

Guest

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
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top