Chart lines referenced to formulas w/ no value = 0?

[ac3100]

Is there a way to have a line in a chart that references a cell that has a formula in it but, contains no value to not show up as a value of zero? sometimes there is missing data for a particular month and the line goes from 35,000 to 0 to 42,000. That kind of looks funny. I use an =if(cell="","",xxx) formula. I wish there would be a break in the line for that particular month until it gets a value. It would look a lot cleaner. I hope this makes sense.
Andy

 

[PaddyD]

one way - excel does not chart #NA's, so change your formula to
=if(a=b,X,na())

Paddy

 

[ac3100]

Thanks for the reply. I can't have #N/A in my worksheet otherwise the macros used to calculate will not work.
It suprises me that there is not a built-in option for charts to not plot a zero if there is no value in the cell.

 

[PaddyD]

On 2002-08-29 14:37, ac3100 wrote:
Thanks for the reply. I can't have #N/A in my worksheet otherwise the macros used to calculate will not work.
It suprises me that there is not a built-in option for charts to not plot a zero if there is no value in the cell.

If you've got zeros in the data & you don't want to plot the data points at all, custom filter your source data to exclude zero's - excel's default is to only plot visable cells.

There are also other things you can do to change the way excel charts blanks - see the options in tools | options |chart tab.

paddy
This message was edited by PaddyD on 2002-08-29 19:26