Dynamic Range for Area Chart with a line graph inside it too

[Mattlynn via OfficeKB.com]

Hi all

I have 4 rows of date horizontal -Excel 2002.

Jan-09 Feb-09 Mar-09 Apr-09 May-09 Jun-09 Jul-09 Aug-09
Target 100 100 100 100 100
Amber 95 95 95 95 95
Actual 90 80 75 90 80

In the area chart, the background is green, represting on target colour.
Target area is red, amber orange, actual is the line graph.
I need to set it up (well i have 24 of them on a page for a scorecard) so
that the areas do not plummet to zero on the blank months.
I have seen various offset info, but those were for simple 2 axis line graphs.

Can somone help me set the dyanmic range or provide a link to helkp me
resolve this.
Many Thanks as usual for all your fantastic replies
Regards
Matt

 

[Bernard Liengme]

Depending on your needs they are several solutions
1) fill the empty cells with =NA() until the data is available. This
displays as #N/A and is ignored by the chart engine
2) use the option (select chart, open Tools | Option | Chart) and specify
that empty cells are to be ignored
3) make a true dynamic chart - Google "Excel dynamic Chart" but you can
readily find the answer at Jon Peltier's site
http://peltiertech.com/Excel/Charts/Dynamics.html
best wishes

 

[Mattlynn via OfficeKB.com]

Hmmmmm

1. =N/A is still recognised in the range and the graps still plummett to zero
2. I dont actylly have an option to say ignore empty cells in tool options
for the chart. The option id do have is plot visible cells only, and that
makes no difference
3. Jons site seems to be the answer.

Many Thanks
Matt

Depending on your needs they are several solutions
1) fill the empty cells with =NA() until the data is available. This
displays as #N/A and is ignored by the chart engine
2) use the option (select chart, open Tools | Option | Chart) and specify
that empty cells are to be ignored
3) make a true dynamic chart - Google "Excel dynamic Chart" but you can
readily find the answer at Jon Peltier's site
http://peltiertech.com/Excel/Charts/Dynamics.html
best wishes

Hi all

[quoted text clipped - 17 lines]

Regards
Matt

 

[Bernard Liengme]

Thanks for feedback
My first point was to type =NA() (not =N/A)
The NA() function returns (ie displays) the value #N/A
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

Hmmmmm

1. =N/A is still recognised in the range and the graps still plummett to
zero
2. I dont actylly have an option to say ignore empty cells in tool options
for the chart. The option id do have is plot visible cells only, and that
makes no difference
3. Jons site seems to be the answer.

Many Thanks
Matt

Depending on your needs they are several solutions
1) fill the empty cells with =NA() until the data is available. This
displays as #N/A and is ignored by the chart engine
2) use the option (select chart, open Tools | Option | Chart) and specify
that empty cells are to be ignored
3) make a true dynamic chart - Google "Excel dynamic Chart" but you can
readily find the answer at Jon Peltier's site
http://peltiertech.com/Excel/Charts/Dynamics.html
best wishes

Hi all

[quoted text clipped - 17 lines]

Regards
Matt

--
Matt Lynn

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-chart/200910/1

 

[Mattlynn via OfficeKB.com]

Hi Bernard - This now returns #NAME? and the graphs still plots them as zero?
Am i doing something else wrong.

Thanks
Matt

Thanks for feedback
My first point was to type =NA() (not =N/A)
The NA() function returns (ie displays) the value #N/A
best wishes

Hmmmmm

[quoted text clipped - 22 lines]

 

[David Biddulph]

Are you sure that you have =NA() ? Those are opening and closing
parentheses. Did you type someting into your formula, or copy from the
newsgroup?
Perhaps you can copy back from your formula bar to the newsgroup so that we
can see exactly what you've got in the formula which is returning #NAME?
--
David Biddulph

Hi Bernard - This now returns #NAME? and the graphs still plots them as
zero?
Am i doing something else wrong.

Thanks
Matt

Thanks for feedback
My first point was to type =NA() (not =N/A)
The NA() function returns (ie displays) the value #N/A
best wishes

Hmmmmm

[quoted text clipped - 22 lines]

Regards
Matt

 

[Mattlynn via OfficeKB.com]

Hi - Sorry for late reply.
I tried again and got the #N/A as you described.
Maybe i accidentally typed something into it too.

Aside from that though, the area chart still plots to zero when i ask it to
look at the =N/A() cells.
I tried clicking on the graph, and going to tool/options/chart and i have
options to...Plot emptycells as eother empty or interpolated - using either
of these make no difference.
Other options are plot visible cells only, and again make no difference with
the chart.
To clarify the chart as i am sometimes rubbish at explaining
Its an area chart with a line chart inside it
The background colour of the area chart is green giving an above target look
to the chart. Amber (orange) and target (Red) and actual (black line) are the
3 axis for the area chart. To get to the actual line i just clicked on what
was the actual area colour and changed chart type to line.
i want to extend the graph for a whole year, but dont want the graph to sink
to zero on empty months.

Maybe this is not possible. Excel 2002 is the version.

Many Thanks Bernard

Regards
Matt

Are you sure that you have =NA() ? Those are opening and closing
parentheses. Did you type someting into your formula, or copy from the
newsgroup?
Perhaps you can copy back from your formula bar to the newsgroup so that we
can see exactly what you've got in the formula which is returning #NAME?
--
David Biddulph

Hi Bernard - This now returns #NAME? and the graphs still plots them as
zero?

[quoted text clipped - 12 lines]

 

[Mattlynn via OfficeKB.com]

Hi David - really sorry i called you Bernard earlier.

I just realised that if i select the chart, and go tools/options/chart that
the plot empty cells as (not plotted - leave gaps) is greyed out. That would
be the option i would expect to fix this
Why would i not be able to select this option.

Many Thanks
Matt

Are you sure that you have =NA() ? Those are opening and closing
parentheses. Did you type someting into your formula, or copy from the
newsgroup?
Perhaps you can copy back from your formula bar to the newsgroup so that we
can see exactly what you've got in the formula which is returning #NAME?
--
David Biddulph

Hi Bernard - This now returns #NAME? and the graphs still plots them as
zero?

[quoted text clipped - 12 lines]