How do I set up formulas to update graphs dynamically

Hello,

I have the following information I need to graph


date	Feb-08	Mar-08	Apr-08	May-08	Jun-08	Jul-08
burn	116	23	254
inventory	3035	3011	2757

The "date" is the x-axis label and the "burn" and "inventory" are the
values on the y-axis.  If the values for burn and inventory are blank
for a particular date, I don't want the information plotted.  As I add
values for each date, I would like the graph to be updated
automatically.  I understand I need to define a named range and use it
in the graph, however, I have not been successful in doing this.
Also, even though the data is blank, there is still a formula there
and I want to make sure that does not cause any problems.

thanks in advance for your help, OS
0
osfard (6)
8/4/2008 11:43:56 PM
excel.charting 18370 articles. 0 followers. Follow

5 Replies
684 Views

Similar Articles

[PageSpeed] 43

Hi osfar

See if the article at
http://peltiertech.com/Excel/Charts/Dynamics.html#DynColCht
does what you want.

Regards,
Pedro J.

> Hello,
> 
> I have the following information I need to graph
> 
> 
> date	Feb-08	Mar-08	Apr-08	May-08	Jun-08	Jul-08
> burn	116	23	254
> inventory	3035	3011	2757
> 
> The "date" is the x-axis label and the "burn" and "inventory" are the
> values on the y-axis.  If the values for burn and inventory are blank
> for a particular date, I don't want the information plotted.  As I add
> values for each date, I would like the graph to be updated
> automatically.  I understand I need to define a named range and use it
> in the graph, however, I have not been successful in doing this.
> Also, even though the data is blank, there is still a formula there
> and I want to make sure that does not cause any problems.
> 
> thanks in advance for your help, OS
0
8/5/2008 12:26:19 AM
For several examples see
Dynamic Charts
http://www.tushar-mehta.com/excel/newsgroups/dynamic_charts/index.html


On Mon, 4 Aug 2008 16:43:56 -0700 (PDT), osfard@gmail.com wrote:

Hello,

I have the following information I need to graph


date	Feb-08	Mar-08	Apr-08	May-08	Jun-08	Jul-08
burn	116	23	254
inventory	3035	3011	2757

The "date" is the x-axis label and the "burn" and "inventory" are the
values on the y-axis.  If the values for burn and inventory are blank
for a particular date, I don't want the information plotted.  As I add
values for each date, I would like the graph to be updated
automatically.  I understand I need to define a named range and use it
in the graph, however, I have not been successful in doing this.
Also, even though the data is blank, there is still a formula there
and I want to make sure that does not cause any problems.

thanks in advance for your help, OS

Regards,

Tushar Mehta
Microsoft MVP Excel 2000-2008
www.tushar-mehta.com
Tutorials and add-ins for Excel, PowerPoint, and other products
0
Tushar
8/5/2008 12:59:23 AM
If the cells are not blank, they will not plot in a line or XY chart. If 
they contain a formula that returns "", the cells are not blank, but instead 
contain a formula that returns text, which Excel treats as zero. To change 
this, use NA() instead of "". This produces the #N/A error in the cell, but 
a line or XY chart will not plot a point. If you don't mind keeping all the 
dates along the axis, this is all you need to do. If you only want to show 
the dates where you have data, you need to use names.

This blog entry has some examples, including data in rows instead of 
columns, which may confuse people.
http://peltiertech.com/WordPress/2008/05/14/dynamic-charts/

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


<osfard@gmail.com> wrote in message 
news:8624c1cc-9260-49b7-9827-137c6789fc80@o40g2000prn.googlegroups.com...
> Hello,
>
> I have the following information I need to graph
>
>
> date Feb-08 Mar-08 Apr-08 May-08 Jun-08 Jul-08
> burn 116 23 254
> inventory 3035 3011 2757
>
> The "date" is the x-axis label and the "burn" and "inventory" are the
> values on the y-axis.  If the values for burn and inventory are blank
> for a particular date, I don't want the information plotted.  As I add
> values for each date, I would like the graph to be updated
> automatically.  I understand I need to define a named range and use it
> in the graph, however, I have not been successful in doing this.
> Also, even though the data is blank, there is still a formula there
> and I want to make sure that does not cause any problems.
>
> thanks in advance for your help, OS 


0
jonxlmvpNO (4558)
8/5/2008 12:52:23 PM
On Aug 5, 5:52=A0am, "Jon Peltier" <jonxlmv...@SPAMpeltiertech.com>
wrote:
> If the cells are not blank, they will not plot in a line or XY chart. If
> they contain a formula that returns "", the cells are not blank, but inst=
ead
> contain a formula that returns text, which Excel treats as zero. To chang=
e
> this, use NA() instead of "". This produces the #N/A error in the cell, b=
ut
> a line or XY chart will not plot a point. If you don't mind keeping all t=
he
> dates along the axis, this is all you need to do. If you only want to sho=
w
> the dates where you have data, you need to use names.
>
> This blog entry has some examples, including data in rows instead of
> columns, which may confuse people.http://peltiertech.com/WordPress/2008/0=
5/14/dynamic-charts/
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> Peltier Technical Services, Inc. -http://PeltierTech.com
> _______
>
> <osf...@gmail.com> wrote in message
>
> news:8624c1cc-9260-49b7-9827-137c6789fc80@o40g2000prn.googlegroups.com...
>
>
>
> > Hello,
>
> > I have the following information I need to graph
>
> > date Feb-08 Mar-08 Apr-08 May-08 Jun-08 Jul-08
> > burn 116 23 254
> > inventory 3035 3011 2757
>
> > The "date" is the x-axis label and the "burn" and "inventory" are the
> > values on the y-axis. =A0If the values for burn and inventory are blank
> > for a particular date, I don't want the information plotted. =A0As I ad=
d
> > values for each date, I would like the graph to be updated
> > automatically. =A0I understand I need to define a named range and use i=
t
> > in the graph, however, I have not been successful in doing this.
> > Also, even though the data is blank, there is still a formula there
> > and I want to make sure that does not cause any problems.
>
> > thanks in advance for your help, OS- Hide quoted text -
>
> - Show quoted text -

Thank you for your help.  I couldn't get it to work, though.  I tried
reproducing the exact formula you had in the example in the link and
it doesn't work.  There is a problem when entering the series formula
(and I did catch that it was on the wrong sheet).

thanks, OS
0
osfard (6)
8/5/2008 5:48:18 PM
On Aug 5, 5:52=A0am, "Jon Peltier" <jonxlmv...@SPAMpeltiertech.com>
wrote:
> If the cells are not blank, they will not plot in a line or XY chart. If
> they contain a formula that returns "", the cells are not blank, but inst=
ead
> contain a formula that returns text, which Excel treats as zero. To chang=
e
> this, use NA() instead of "". This produces the #N/A error in the cell, b=
ut
> a line or XY chart will not plot a point. If you don't mind keeping all t=
he
> dates along the axis, this is all you need to do. If you only want to sho=
w
> the dates where you have data, you need to use names.
>
> This blog entry has some examples, including data in rows instead of
> columns, which may confuse people.http://peltiertech.com/WordPress/2008/0=
5/14/dynamic-charts/
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> Peltier Technical Services, Inc. -http://PeltierTech.com
> _______
>
> <osf...@gmail.com> wrote in message
>
> news:8624c1cc-9260-49b7-9827-137c6789fc80@o40g2000prn.googlegroups.com...
>
>
>
> > Hello,
>
> > I have the following information I need to graph
>
> > date Feb-08 Mar-08 Apr-08 May-08 Jun-08 Jul-08
> > burn 116 23 254
> > inventory 3035 3011 2757
>
> > The "date" is the x-axis label and the "burn" and "inventory" are the
> > values on the y-axis. =A0If the values for burn and inventory are blank
> > for a particular date, I don't want the information plotted. =A0As I ad=
d
> > values for each date, I would like the graph to be updated
> > automatically. =A0I understand I need to define a named range and use i=
t
> > in the graph, however, I have not been successful in doing this.
> > Also, even though the data is blank, there is still a formula there
> > and I want to make sure that does not cause any problems.
>
> > thanks in advance for your help, OS- Hide quoted text -
>
> - Show quoted text -

I do want to only show the dates where I have data.  I couldn't get
the defined names and the series formula to work but what I did was
take this into account in the excel formula.  If there was no data
associated with the date, then I made the date also be #NA (my data
comes from a different sheet and I just referenced it on the sheet
where I am graphing so this was pretty easy to take into account).  So
in essence, I have a static graph and not a dynamic one but it still
acts as a dynamic graph as data is entered.

thanks again for your help.  I am still curious as to how it would
work the real, more sophisticated way.

take care
0
osfard (6)
8/5/2008 5:56:08 PM
Reply:

Similar Artilces: