Friday, November 12, 2004

Creating blank lines in an Excel Chart

I regularly put together charts of Actuals Vs Budgets.
In most instances these charts will be referencing database sheets in some way, usually via a subsidiary chart data summary formula area.

Usually there will be 12 months of a budget which will generally display a value, but the formula for the actuals will only display a value for months where there is data, and will display a zero where there is not.

While this is not a problem with bar charts in Excel, line and area charts will display incorrectly. Months with actual data will show correctly then the line or area will take a big dive towards zero and continue to display as a zero.

My solutions to this problem is to edit my formula to return #N/A as a result. The chart will ignore this error and not show any result at all.

Here's how I do that using an IF function.

=IF(formularesult=0,#N/A,formularesult) where formula result is the existing formula to generate the actual data.


Posted by: Jethro | 1:48 AM |