Dynamic charting

T

tom300181

I have a sheet that produces a chart for performance on a daily basi
over a month.

I produce a chart to show the day and have to change the data rang
every day as I don't want all days that have not yet occurred to sho
as 0. Is the any way to limit the appearance on the graph to days tha
have occured...


Thanks

To
 
A

Andrea Jones

Use an If function on the column of values you are plotting so that if there
is a result the answer is shown, if not the cell shows #N/A, e.g. if I had a
column A containing dates and column B containing Amounts I could create a
formula in column C:

=IF(ISNUMBER(B2),B2*1,NA())

If column B contains a number (the day has a value as it has been passed)
then this formula will return that value (or anything else you want to
calculate from it), otherwise it will show the value #N/A in the cell. Create
your chart using the whole date range that you will want to cover. Now click
on Tools -> Options and under Chart set empty cells as 'Not plotted (leave
gaps)'. The #N/A values will not appear on your chart but the line should
extend as you enter values day by day.

Andrea Jones
http://www.allaboutoffice.co.uk
http://www.stratatraining.co.uk
http://www.allaboutclait.com
 
J

Jon Peltier

You can make the whole chart more dynamic, only showing dates where
there are values. Here are a few examples and a lot of links:

http://peltiertech.com/Excel/Charts/Dynamics.html

Note: The #N/A is independent of the 'Not Plotted' blank cell charting
option. If there is any text in the cell, even a space or a formula that
returns "", it is no longer a blank cell, and Excel will try to plot it
as a zero. Using #N/A or NA() is a workaround that interpolates over a
point in a line or scatter chart.

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