Plotting with a formula in a cell

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
I need help with this, I am sure it is a simple answer,
but I cannot figure it out. I have a workbook with 2
worksheets in it. The first worksheet has all the inputs
from my data in it, and the second worksheet has the
charts in it. I have pasted the links into the second
worksheet and all is fine with that, but when the value is
zero, it plots it as a zero. I would like to just have it
as a gap when the value is zero. I have gone into the
options menu and clicked on the one that reads "NOT
PLOTTED,(LEAVE GAPS)", but it does not seem to fix it.
What I found is that becuase there is a formula present in
the cell, it still plots it as a zero. If I delete the
formula, it does not go to zero and a gap is present. Also
with this, if the value is zero in the cell, I would like
to hide it and show the cell as blank. In the pasted cell,
this is what is in there right now, =Sheet2!$C$5.

Any help with this would be apreciated
 
Unfortunately, no, it is not a simple answer. Worse, the answer is "it
cannot be done."

Once you use a formula, there is no way to duplicate the effect of an
empty cell in a chart. The closest one can get is to use NA() instead
of "" in the formula.

For most charts this value will be treated like an empty cell.
However, for line and scatter charts, XL will interpolate.

For scatter charts, you could introduce gaps with an add-in I developed
(Excel/Add-Ins/'Chart gap for N/A' page of my web site) that relies on
point-by-point formatting to create gaps.

It used to work with line charts, but a bug in XL that as far as I know
remains unresolved means it ignores point-by-point formatting for a
line chart.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Andy Pope has a workaround on his web site that fakes a gap
(http://andypope.info). It might still work in later versions of Excel.

You don't need to paste the links into a second worksheet in order to
create charts on the second worksheet. The chart can be created based on
the other worksheet's data. You can right click the chart and choose
Source Data from the pop up menu to change the links to the first
worksheet. You could also use a handy little addin on my web site that
changes the series formulas, kind of like Find-Replace in the worksheet.

http://peltiertech.com/Excel/Charts/ChgSrsFmla.html

Carefully enter the entire second worksheet name in the Change From
field, and the entire worksheet name in the Change To field.

- Jon
 
Back
Top