Stop the line

[MNaut]

Is there a way to set up a line chart that pulls data from cells with result
formulas in them so they leave the chart data points blank until they are
greater than zero? I don't want zeros showing all the way across the chart
as data is filled in week by week.

I've built line charts that track weekly sales comparing the total sales for
the week with the same calendar week total sales last year. Each of several
departments has their own chart and it runs for one quarter year (or 12 to
14 weeks). The new weekly sales figures are entered as they happen, but all
of last years figures are displayed through the entire time span to allow
dept, managers to see the future comparison points. The charts are printed
each week and posted. Some of the sales figures displayed are composites
that are determined by an equation in the cell that the chart draws its data
from. On those charts that draw from cells that have equations, the line
for this years weekly sales progresses as zero through the whole quarter.
It takes a dive from the latest weekly figure entered and looks bad. I've
tried entering "if" statements into the parent cell equations that leave the
cell blank if there is no data to drive it, but the chart continues to see
the zero result of the equation and display it. Does anyone know a way I
can fix this?

Thanks,

Mnaut

 

[Jon Peltier]

In your formula, change "" to NA(). This puts an ugly #N/A error in the
cells, but isn't charted. There is no true "Blank" that can be returned
by a formula. "" looks like a blank, but it's a text string, which is
plotted like a zero.

Use conditional formatting to hide the #N/A in the worksheet, as shown
by Deb Dalgleish:

http://contextures.com/xlCondFormat03.html#Errors

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

 

[Del Cotter]

In your formula, change "" to NA(). This puts an ugly #N/A error in the
cells, but isn't charted. There is no true "Blank" that can be returned
by a formula. "" looks like a blank, but it's a text string, which is
plotted like a zero.

Incidentally, Mnaut may have the same problem I had the other day, where
using "" caused the chart to look bad, but making zeroes into NA()
stopped me running statistics on the columns. I complained that I
needed a MAXIF, MINIF, STDEVIF, etc. to go with SUMIF.

Well, it turned out there was a way of doing that by putting an IF
function inside the brackets and entering it as an array formula:

=SUM( IF(ISNA(A1:A99),"",A1:A99) )

Because it has to be an array expression, you need to enter using
Ctrl-Shift-Enter instead of just Enter, and you'll know you succeeded
when the formula appears in curly brackets like so:

{=SUM( IF(ISNA(A1:A99),"",A1:A99) )}

(it was the chart with the multiple sheets and the form controls, and
boy, am I learning a lot from it)

Use conditional formatting to hide the #N/A in the worksheet, as shown
by Deb Dalgleish:

http://contextures.com/xlCondFormat03.html#Errors

I can't do this on the Excel 95 at home, but I'm happy to put up with
the #N/A. I don't even find them very ugly, to be honest.

 

[Jon Peltier]

Del -

Good point about the formulas not working right with #N/A in the range.
Most people are more worried about appearance.

I can't do this on the Excel 95 at home, but I'm happy to put up with
the #N/A. I don't even find them very ugly, to be honest.

Me either. They let me know something is happening in the cells.

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

 

[Tushar Mehta]

A simpler approach under the correct circumstances is to create a
duplicate dataset for plotting purposes, leaving the calculation data
unchanged. The instance where NA() replaces "" or zeros is what gets
plotted, the other is what gets used for downstream calculations.

--
Regards,

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

 

[MNaut]

Duplicate dataset? Is that where a second row of data is created that
"looks" at the row that has the calculation data? I tried that. I had the
chart refer to the second row of data instead of the original row with no
difference on the resulting chart.
Mnaut

 

[Tushar Mehta]

Yes, you have the right idea.

What was the formula that you used to create the 2nd row? And, in your
case, which is the first row and which is the 2nd row?

--
Regards,

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

 

[MNaut]

The 2nd row was created to generate the data for the chart and "looks at"
the original (1st) row of data that contains the formulas needed to result
in the data to chart. The second row has simple (=C6) formulas referencing
the 1st (original data formula) row. Unfortunately the chart continues to
exhibit the same problem as when it was focused on the original row.

 

[Tushar Mehta]

Each cell in the 2nd row should contain a formula along the lines of
=IF({first-row-cell}="",NA(),{first-row-cell})

--
Regards,

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