Thread: How to exclude conditional calculated no or zero values from a cha

Picture this: a chart for 30 days and 30 values. The values come from a
different table (sheet1!A:G) using the following formula
=IF(VLOOKUP(J11;Sheet1!A:G;2;FALSE)="";"";VLOOKUP(J11;Sheet1!A:G;2;FALSE)).
If the table on sheet1 has no value (""), then the a.m. formula also displays
no value (""). The chart however does show a zero value, which i do not need.
Is there a possibility to not show this zero or no value in a chart?

Thanks for your help!

Hi,

Use the formula NA() instead of "".

Here are a couple of pages on the subject.

http://www.stfx.ca/people/bliengme/E...issingData.htm
http://processtrends.com/pg_charts_missing_data.htm

Cheers
Andy

Frodo wrote:
> Picture this: a chart for 30 days and 30 values. The values come from a
> different table (sheet1!A:G) using the following formula
> =IF(VLOOKUP(J11;Sheet1!A:G;2;FALSE)="";"";VLOOKUP(J11;Sheet1!A:G;2;FALSE)).
> If the table on sheet1 has no value (""), then the a.m. formula also displays
> no value (""). The chart however does show a zero value, which i do not need.
> Is there a possibility to not show this zero or no value in a chart?
>
> Thanks for your help!
>

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Hi Andy,

thanks, works great!

Frodo

"Andy Pope" wrote:

> Hi,
>
> Use the formula NA() instead of "".
>
> Here are a couple of pages on the subject.
>
> http://www.stfx.ca/people/bliengme/E...issingData.htm
> http://processtrends.com/pg_charts_missing_data.htm
>
> Cheers
> Andy
>
> Frodo wrote:
> > Picture this: a chart for 30 days and 30 values. The values come from a
> > different table (sheet1!A:G) using the following formula
> > =IF(VLOOKUP(J11;Sheet1!A:G;2;FALSE)="";"";VLOOKUP(J11;Sheet1!A:G;2;FALSE)).
> > If the table on sheet1 has no value (""), then the a.m. formula also displays
> > no value (""). The chart however does show a zero value, which i do not need.
> > Is there a possibility to not show this zero or no value in a chart?
> >
> > Thanks for your help!
> >
>
> --
>
> Andy Pope, Microsoft MVP - Excel
> http://www.andypope.info
>