I run Excel 2007 (part of Office 2007) on Windows 7 Pro (64 bit) SP1. I have created a Table, and am having a problem getting an entry which contains no data to plot correctly on a Chart.
Look at the attached image. I have a series of entries (to the left of what is shown on the attached image) that I sum. The formula I have underlined in red at the top is what would go into the cell pointed to with the red arrow. If I recorded data on a specific date, the information is summed correctly and plots correctly on the chart.
But if I miss a day, the data cells to the left are blank. Likewise, the cell pointed to with the arrow is also blank. But the chart plots it as zero, which is wrong. If I manually select the cell pointed to, right click and choose ‘Clear Contents’ from the menu, then nothing is plotted on the chart, and a straight line is drawn between the two adjacent dates that have data. This is exactly what I want. Since there was no numerical entry in the cell I have no idea what the ‘contents’ of it might have been.
But I have to do this manually for every cell (or group of cells) that does not contain data. Is there some way to change the formula I am using so that each of these summary cells automatically are recognized by the chart as having had the ‘contents cleared’, so I don’t have to do this manually? And because I always add some form of Comment to lines that have no data, I don’t want to simply delete the complete line for that date.
Harry
