Lines in graph only if values in fields

[Vads]

Dear readers,

I've got a challenge for all. Working at a helpdesk, I've got a
question from an user regarding the following situation.

User has a sheet with data. In the same workbook, he's got a sheet with
graphs. In one of these graphs are two lines. These lines refer to
cells D13..CY13 (red line) and D16..CY16 (black line).

The cells D16 till AA16 each contain a value: percentages, like 60%,
etc. From AB16 till CY16 they're empty cells. This results in the graph
that the line stops somewhere halfway, instead of going on till the end
of the horizontal line with a 0% value.

This last description is exactly what happens and is the reason I'm
writing you. User wants the red line to stop as soon as there is no
value in the cells. BUT: the content of cells D13 till V13 are
references to other cells. By example, cell D13's content looks like:
=D11. Cell D11 on the other hand contains a formula: =IF(P6=0,"
",1-(P9/P10)).

I tend to tell the user that the red line continues, because in some
way it contains a zero value. That's why the line suddenly drops to 0
in the graph and continues at 0 till the end of the graph.

I know there's a workaround to this by replacing the empty W13 till
CY13 cells with no content, just like row 16... But I expect the user
to reply that next time he edits the sheet-data, the graph to adapt
itself...

What can be done here? Did this user bump into the limitations of MS
Excel? Or is there some setting that stops the red line as soon as
there are no values?

I hope to have received some replies by monday to inform the user.
Thanks to all and have a nice weekend!

Vitor

 

[Jon Peltier]

Hi Vitor -

Have your user change his formula to

=IF(P6=0,NA(),1-(P9/P10))

The "" is an empty string, which Excel interprets not as a blank, but as
a bit of text, and charts as a zero. The NA() returns an #N/A error
which looks ugly in the sheet, but is mostly ignored in the chart. The
errors can be made to disappear using conditional formatting.

For another approach, the user could also set up a dynamic range as
follows. Press Ctrl-F3, enter a name, like RedLine, in the Name box,
and in the Refers to box enter a formula like:

=OFFSET(Sheet1!D13,0,0,1,COUNT(Sheet1!D13:CY13))

(Use the actual sheet name in place of Sheet1). This identifies a range
starting with D13, extending to the right only as far as there is data
in row 13 (blanks before the end will throw off the counting). Define
also BlackLine, which Refers To

=OFFSET(Sheet1!D16,0,0,1,COUNT(Sheet1!D16:CY16))

Now right click the chart, choose Source Data from the pop up menu, and
click on the Series tab. Select the Red Line series, and change the
contents of the Values box from =Sheet1!D13:CY13 to =Sheet1!RedLine. Do
the same for the Black Line series.

The chart only contains the range with data, and it updates as data is
added. It still continues the entire extent of the original X axis. If
you want to expand only the range with data, and not have lots of empty
space to the right, define a range name for the X values (or category
labels, whatever). Ctrl-F3 again, ChartX in the Name box, and assuming
the X data is in D1:CY1, use a formula like this:

=OFFSET(Sheet1!D1,0,0,1,COLUMNS(RedLine))

If RedLine and BlackLine have different lengths, use this instead:

=OFFSET(Sheet1!D1,0,0,1,MAX(COLUMNS(RedLine),COLUMNS(BlackLine))

Now go back to the chart, right click, Source Data, Series Tab, and
replace whatever's in the X Values or Category Labels box with
=Sheet1!ChartX.

- Jon