How do I set up formulas to update graphs dynamically

[osfard]

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

 

[Infinitogool]

Hi osfar

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

Regards,
Pedro J.

 

[Tushar Mehta]

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), (e-mail address removed) 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

 

[Jon Peltier]

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

 

[osfard]

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
_______










- 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

 

[osfard]

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
_______










- 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