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.

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

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

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