blank cell interpolation problems

  • Thread starter Thread starter Jacobs
  • Start date Start date
J

Jacobs

I am trying to plot a data set with a conditional. Here are a few of my
attempts :

=IF(O111=0, NA(),IF(O111<6,O111,NA()))

or

=IF(O111=0, #N/A),IF(O111<6,O111,#N/A))

even

=IF(O15=0, "",IF(O15<6,O15, "")) *this one doesn't even plot *my* x
values, just some incrementally increasing crap*

I want the chart to plot only the values that have data in them as a
line plot. So you would see gaps in the line where there is no data.
I can't get it to work. I have read all the other posts about this
similar topic but I can't seem to get it right. I have my tried
formatted the cells as numbers and as general. I have tried setting
the chart options to 'not plotted (leave gaps)'. The problem is that
excel is interpolating between the cells that have values in them and
not leaving gaps in the plot. Each plot starts with the first actual
value. So, if my data starts with 100 blanks spaces the plot will
start at the 101 piece of actual data, nut then it will interpolate
through any other blanks. Probably because it has nothing above it
(the 100 blanks) to interpolate with. I am using excel2002. Please
let me know if you need more info or I am being vague. Your help will
be appreciated.
 
I am trying to plot a data set with a conditional. Here are a few of my
attempts :

=IF(O111=0, NA(),IF(O111<6,O111,NA()))

or

=IF(O111=0, #N/A),IF(O111<6,O111,#N/A))

even

=IF(O15=0, "",IF(O15<6,O15, "")) *this one doesn't even plot *my* x
values, just some incrementally increasing crap*

I want the chart to plot only the values that have data in them as a
line plot. So you would see gaps in the line where there is no data.
I can't get it to work. I have read all the other posts about this
similar topic but I can't seem to get it right. I have my tried
formatted the cells as numbers and as general. I have tried setting
the chart options to 'not plotted (leave gaps)'. The problem is that
excel is interpolating between the cells that have values in them and
not leaving gaps in the plot. Each plot starts with the first actual
value. So, if my data starts with 100 blanks spaces the plot will
start at the 101 piece of actual data, nut then it will interpolate
through any other blanks. Probably because it has nothing above it
(the 100 blanks) to interpolate with. I am using excel2002. Please
let me know if you need more info or I am being vague. Your help will
be appreciated.
Click to expand...

Try www.tushar-mehta.com, look for "Chart gap for N/A" on the left.
Perhaps that will help.
 
I haven't tried it yet but it looks like it is exactly what I need.

Thanks for your help Dave! You da man :)
 
I can't seem to get it to work. Maybe I am doing something wrong but it
the code doesn't seem to be working. I tried reproducing the example
that Tushar gave but couldn't even get that to work. I am going to
email him soon so maybe he can help straighten me out.
 
You need to post the context of your reply as I did by including your
message.

You may also want to connect to the Usenet newsgroups directly. For
more, see the 'Outlook Express and Newsgroups' tutorial on my web site.

Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Tushar said:
*You need to post the context of your reply as I did by including
your
message.

You may also want to connect to the Usenet newsgroups directly. For
more, see the 'Outlook Express and Newsgroups' tutorial on my web
site.

Regards,

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

Tushar, do you think you can tell me why I can't get the addin to work?
I think I am doing it right but maybe not. I am just plotting one
series of data as a line plot. I tried to do a simple example like the
one on your page with =na() every now and then, but I couldn't get it
to work. I really appreciate your help with this matter, because it it
really important that I figure out a way to do this. Also, your
webpage is great. You help a lot of people out and I think that's
pretty damn cool.
 
Back
Top