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

--
Matt Lynn

Message posted via http://www.officekb.com

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Mattlynn via OfficeKB.com" <u44078@uwe wrote in message
news:9d55b8ed6b85c@uwe...
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

--
Matt Lynn

Message posted via http://www.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



Bernard Liengme wrote:
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.a...chart/200910/1

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


"Mattlynn via OfficeKB.com" <u44078@uwe wrote in message
news:9d561f0451c12@uwe...
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



Bernard Liengme wrote:
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.a...chart/200910/1

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

Thanks
Matt



Bernard Liengme wrote:
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

--
Matt Lynn

Message posted via http://www.officekb.com

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

"Mattlynn via OfficeKB.com" <u44078@uwe wrote in message
news:9d7910e6c5b3d@uwe...
Hi Bernard - This now returns #NAME? and the graphs still plots them as
zero?
Am i doing something else wrong.

Thanks
Matt



Bernard Liengme wrote:
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

--
Matt Lynn

Message posted via http://www.officekb.com