I have been looking for a solution for some time to a charting problem.
If the source cell of a linier chart is empty no value is plotted. If the source cell has anything in it (a zero, a string or a formula) the cell is plotted as zero and the line on the chart plots to the x-axis.
Example: Say you’re charting sales history by month and you’re six months into a year. The sales data is a roll-up of company divisions by using sum formulas in each of the 12 months source cells of the year. Then July through December plot as zero sales and the chart line shows a sudden drop to zero sales in July.
One solution is to leave the monthly cells empty and manually or through VBA add the sum formula as the months progress. What a drag.
Is there any way to keep all the formulas in the source cells and have the chart plot the line only when the value of the source cell is not zero?