Expression for Blank Spaces
 

I have an Excel line graph that I update weekly. Column "A" with the weekly
date, and "B" with the data. There are two more columns in the sheet, "C"
with an 'if' formule that depends on the data in Col "B" and "D" with a
weekly percentage increase of an index. Col "D" is extended out for an extra
100 weeks; the graph and the x axis accomodates that. If I do not extend the
Col "C" formula out, line graph for C stops as desired, however, if I expend
out the Col "C" formula, the line drops to the x axis, making the the entire
graph very unattarctive.
The { if } statement reads as follows:{{ If(Col A = blank), Col C = blank,
Col B/constant}}. I use the expression " " to represent the blank.
Apparently in Col "C", Excel reads that as somthing equivalent to 0 and sends
the line to the x axis. Are there any suggestions for some other expression
that can be used to instead of " " to represent a true blank space????
I have used this method and formula for the past 15 years with a version of
Quattro Pro (DOS) and it has worked well there. It probably will not be long
before all DOS applications become extinct, so I would like to convert to
Excel.
Thanks for any help.

Expression for Blank Spaces
 

"" is interpreted as text, which is plotted as zero. Use NA() instead, which
returns an error, #N/A, in the cell, but which is ignored by line and XY
charts. Use conditional formatting to hide the error if necessary.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"dsears" wrote in message
...
I have an Excel line graph that I update weekly. Column "A" with the
weekly
date, and "B" with the data. There are two more columns in the sheet, "C"
with an 'if' formule that depends on the data in Col "B" and "D" with a
weekly percentage increase of an index. Col "D" is extended out for an
extra
100 weeks; the graph and the x axis accomodates that. If I do not extend
the
Col "C" formula out, line graph for C stops as desired, however, if I
expend
out the Col "C" formula, the line drops to the x axis, making the the
entire
graph very unattarctive.
The { if } statement reads as follows:{{ If(Col A = blank), Col C = blank,
Col B/constant}}. I use the expression " " to represent the blank.
Apparently in Col "C", Excel reads that as somthing equivalent to 0 and
sends
the line to the x axis. Are there any suggestions for some other
expression
that can be used to instead of " " to represent a true blank space????
I have used this method and formula for the past 15 years with a version
of
Quattro Pro (DOS) and it has worked well there. It probably will not be
long
before all DOS applications become extinct, so I would like to convert to
Excel.
Thanks for any help.

Expression for Blank Spaces
 

Thanks--it worked very well.

DSC

"Jon Peltier" wrote:

"" is interpreted as text, which is plotted as zero. Use NA() instead, which
returns an error, #N/A, in the cell, but which is ignored by line and XY
charts. Use conditional formatting to hide the error if necessary.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"dsears" wrote in message
...
I have an Excel line graph that I update weekly. Column "A" with the
weekly
date, and "B" with the data. There are two more columns in the sheet, "C"
with an 'if' formule that depends on the data in Col "B" and "D" with a
weekly percentage increase of an index. Col "D" is extended out for an
extra
100 weeks; the graph and the x axis accomodates that. If I do not extend
the
Col "C" formula out, line graph for C stops as desired, however, if I
expend
out the Col "C" formula, the line drops to the x axis, making the the
entire
graph very unattarctive.
The { if } statement reads as follows:{{ If(Col A = blank), Col C = blank,
Col B/constant}}. I use the expression " " to represent the blank.
Apparently in Col "C", Excel reads that as somthing equivalent to 0 and
sends
the line to the x axis. Are there any suggestions for some other
expression
that can be used to instead of " " to represent a true blank space????
I have used this method and formula for the past 15 years with a version
of
Quattro Pro (DOS) and it has worked well there. It probably will not be
long
before all DOS applications become extinct, so I would like to convert to
Excel.
Thanks for any help.