zero values in line charts

**eric_t_viking** · 06-01-2009, 06:38 PM

How do I stop the line chart showing a value of zero for errors and zeros as for the example below? I have trued iserror and "" combinations.

Expected (1st col) Actual (2nd col)
10 11
12
14
16 17
18 18

The data will appear at a future time when linked cells are updated

I am currently using office on a mac but the final workbook will be on a PC

**shg** · 06-01-2009, 06:48 PM

Replace the blanks with NA()

**eric_t_viking** · 06-01-2009, 06:53 PM

in this example

=IF(ISERROR('Wr Ind Progress'!S85),"",('Wr Ind Progress'!S85))

replacing the "" with NA() still doesn't work

**shg** · 06-01-2009, 07:00 PM

Post a workbook.

**eric_t_viking** · 06-01-2009, 07:07 PM

The workbook attached only contains dummy data to test it out. Usually the line graph would be updated every three months.

**shg** · 06-01-2009, 07:23 PM

To pick the example in D8, the value in P85 of the other sheet is zero, not an error.

You also have circular references -- is that intentional?

**bmwtaxi** · 06-01-2009, 10:11 PM

I have the same issue. When a cell has a zero value I do not want a tick to show on my line chart until I put data into that particular cell.

**bmwtaxi** · 06-01-2009, 10:21 PM

Here is my workbook.

**eric_t_viking** · 06-02-2009, 03:25 AM

Originally Posted by shg

To pick the example in D8, the value in P85 of the other sheet is zero, not an error.

You also have circular references -- is that intentional?

The value is zero. If I stop the zero appearing, would that solve the problem of the line graph showing a value of zero?

Also, could you explain a cirucular reference? I am fairly new to excel and have justed produced this to get the result I needed. I know it is not 'tidy', but it does what I need at the moment.

Thanks for your support with this

**shg** · 06-02-2009, 09:15 AM

If I stop the zero appearing, would that solve the problem of the line graph showing a value of zero?

Not if you stop it from APPEARING (like using conditional formatting to make it white as you have done, or suppressing display of zero), but if you stop it from being the RESULT being returned by the formula, yes.

Also, could you explain a cirucular reference?

You have formulas that in some way depend on their own results. If you're not doing it on purpose, it's always a mistake. Select the sheet that has them, delete those formulas, and reconstruct them so that they are not circular.