|
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 -- tom300181 ------------------------------------------------------------------------ tom300181's Profile: http://www.excelforum.com/member.php...o&userid=21580 View this thread: http://www.excelforum.com/showthread...hreadid=389870 |
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 |
| All times are GMT +1. The time now is 10:03 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com