- Excel Book Excerpt
Excel Prevent the Drop to Zero
This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Prevent the Drop to Zero
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.
Figure 1022 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.
Figure 1023 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.
For more resources for Microsoft Excel: