Can you paste formulas with no value so Charts don't registerthem
 

I am trying to paste (Paste Special - Values) a row of formulas into a range
on a chart, some of these have values and some don't ... where there are no
values the cell still registers on the chart as 0 ... is there anyway of
pasting formulas so that the chart doesn't register the blank cells???

Many thanks

Can you paste formulas with no value so Charts don't registerthem
 

Hi KolM#s. I'm not sure what you mean by pasting numbers into a chart, but
if you don't want to show the 0 you could use a helper column, assuming your
data is in a column, say D, after you paste specialvalues, you could use
=if(D1=0,"",D1) and copy down.
--
Sincerely, Michael Colvin


"KoLM#s" wrote:

I am trying to paste (Paste Special - Values) a row of formulas into a range
on a chart, some of these have values and some don't ... where there are no
values the cell still registers on the chart as 0 ... is there anyway of
pasting formulas so that the chart doesn't register the blank cells???

Many thanks

Can you paste formulas with no value so Charts don't registert
 

Hi Michael

Many thanks for your response... unfortunately I have tried this, but as
there is a formula in the blank cell, the chart registers it as a zero and
continues the line. I need the line to end on the relevant month. I have
automated the process using VB, this copies the column and pastes the values
into the the relevant column read by the chart.... unfortunately even though
the blank cells appear to have nothing in them they still register as zeros
on the chart, and only through manual intervention of highlighting the blank
cells and pressing delete can I get rid of the zeros on the chart.
Once again many thanks Vaughan

"Michael" wrote:

Hi KolM#s. I'm not sure what you mean by pasting numbers into a chart, but
if you don't want to show the 0 you could use a helper column, assuming your
data is in a column, say D, after you paste specialvalues, you could use
=if(D1=0,"",D1) and copy down.
--
Sincerely, Michael Colvin


"KoLM#s" wrote:

I am trying to paste (Paste Special - Values) a row of formulas into a range
on a chart, some of these have values and some don't ... where there are no
values the cell still registers on the chart as 0 ... is there anyway of
pasting formulas so that the chart doesn't register the blank cells???

Many thanks

Can you paste formulas with no value so Charts don't registert
 

Sorry it didn't help. I hope someone else may have a solution. Good luck.
--
Sincerely, Michael Colvin


"KoLM#s" wrote:

Hi Michael

Many thanks for your response... unfortunately I have tried this, but as
there is a formula in the blank cell, the chart registers it as a zero and
continues the line. I need the line to end on the relevant month. I have
automated the process using VB, this copies the column and pastes the values
into the the relevant column read by the chart.... unfortunately even though
the blank cells appear to have nothing in them they still register as zeros
on the chart, and only through manual intervention of highlighting the blank
cells and pressing delete can I get rid of the zeros on the chart.
Once again many thanks Vaughan

"Michael" wrote:

Hi KolM#s. I'm not sure what you mean by pasting numbers into a chart, but
if you don't want to show the 0 you could use a helper column, assuming your
data is in a column, say D, after you paste specialvalues, you could use
=if(D1=0,"",D1) and copy down.
--
Sincerely, Michael Colvin


"KoLM#s" wrote:

I am trying to paste (Paste Special - Values) a row of formulas into a range
on a chart, some of these have values and some don't ... where there are no
values the cell still registers on the chart as 0 ... is there anyway of
pasting formulas so that the chart doesn't register the blank cells???

Many thanks

Can you paste formulas with no value so Charts don't registerthem
 

You have to have the formula result in an NA() in order to have the
chart ignore it.

In your formula try something like: =IF(A1=0,NA(),A1)
OR
=IF(A1="",NA(),A1)


--
Cutter
------------------------------------------------------------------------
Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848
View this thread: http://www.excelforum.com/showthread...hreadid=507676