If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Dynamic Range for Area Chart with a line graph inside it too
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 |
#2
|
|||
|
|||
Dynamic Range for Area Chart with a line graph inside it too
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 |
#3
|
|||
|
|||
Dynamic Range for Area Chart with a line graph inside it too
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 |
#4
|
|||
|
|||
Dynamic Range for Area Chart with a line graph inside it too
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 |
#5
|
|||
|
|||
Dynamic Range for Area Chart with a line graph inside it too
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 |
#6
|
|||
|
|||
Dynamic Range for Area Chart with a line graph inside it too
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 |
#7
|
|||
|
|||
Dynamic Range for Area Chart with a line graph inside it too
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 David Biddulph wrote: 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] Regards Matt -- Matt Lynn Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...chart/200910/1 |
#8
|
|||
|
|||
Dynamic Range for Area Chart with a line graph inside it too
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 David Biddulph wrote: 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] Regards Matt -- Matt Lynn Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...chart/200910/1 |
Thread Tools | |
Display Modes | |
|
|