Archive of Mr Excel Message Board

Check out Bill's new book on Charts and Graphs for Microsoft Office Excel 2007

Back to Charting for Excel archive index
Back to archive home

Can I avoid impact of zero values with a line chart

Posted by Phil on July 29, 2001 11:29 PM
I have a worksheet which tracks weekly receipt amounts (ie. column 1=Date, Column 2=Amount, Column 3= percentage difference on last year)

Column 3 contains the % difference formula in each row but returns zero if amount column is blank (ie. not yet reached this date)

I chart column 3 and have included a 12 month range (ie. 52 rows) to avoid updating chart range each week.

The line in my chart tracks the % difference however in the months that zero is entered it obviously reflects this value.

Is there a way to chart column 3 without the impact of zero values in future weeks. (ie. can the chart line be formatted or a more suitable value entered in the worksheet. NB. Bar chart not an option.

Thanks for any help.


Re: Can I avoid impact of zero values with a line chart

Posted by Aladin Akyurek on July 30, 2001 1:12 AM
What about returning #N/A instead of 0, where you deem appropriate or desirable? This, because the chart procs seem to ignore #N/A's, so I'm told I believe.

Aladin

============


Re: Can I avoid impact of zero values with a line chart

Posted by Andy on July 30, 2001 9:14 AM
Try this

e.g. if your values are in columns A & B and % is in column C, enter the formula

=if(A1/B1=0,"",A1/B1)

i.e. if A1/B1=0, then enter a blank (NOT zero), else enter the actual value


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.