Problem: I've built formulas to create a chartable range. I want to show how the fund balances have been increasing for three years. All of a sudden, the points for future months look like someone spent all the money, dropping to zero.
- Prevent the zero points for the future.
Strategy: Zero cells will be plotted. Empty cells or #N/A! cells will not be plotted. Change your formulas to put an NA() instead of 0. Say that you are building the chart range with =SUMIF(). Start to use =IF(SUMIF()=0,NA(),SUMIF()). The chart will look much better.
- Use #N/A instead of zero.
It is ironic that 99% of the time, you are trying to avoid the #N/A. In this case, however it solves the problem.