Most valuable professional
  • 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.

LE10001054.jpg 

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.

LE10001055.jpg 

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.

<-Previous Topic                    Next Topic->

For more resources for Microsoft Excel: