Dynamic charting

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

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


Thanks

Tom

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

"tom300181" wrote:

>
> I have a sheet that produces a chart for performance on a daily basis
> over a month.
>
> I produce a chart to show the day and have to change the data range
> every day as I don't want all days that have not yet occurred to show
> as 0. Is the any way to limit the appearance on the graph to days that
> have occured...
>
>
> Thanks
>
> Tom
>
>
> --
> tom300181
> ------------------------------------------------------------------------
> tom300181's Profile: http://www.excelforum.com/member.php...o&userid=21580
> View this thread: http://www.excelforum.com/showthread...hreadid=389870
>
>

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/
_______


Andrea Jones wrote:

> 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
>
> "tom300181" wrote:
>
>
>>I have a sheet that produces a chart for performance on a daily basis
>>over a month.
>>
>>I produce a chart to show the day and have to change the data range
>>every day as I don't want all days that have not yet occurred to show
>>as 0. Is the any way to limit the appearance on the graph to days that
>>have occured...
>>
>>
>>Thanks
>>
>>Tom
>>
>>
>>--
>>tom300181
>>------------------------------------------------------------------------
>>tom300181's Profile: http://www.excelforum.com/member.php...o&userid=21580
>>View this thread: http://www.excelforum.com/showthread...hreadid=389870
>>
>>