Finding and Deleting formulas based the result of the formula

[Verizon123]

I have a single column of formulas that calulate a numberic value based on the =IF formula. For the "value if false" part of the formula, when I use zero or leave it blank, it places a zero in the cell, as it should. The problem occurs when I graph that column; I want to have a clean line of the just the non zero values, rather than the "droping to zero and the graph line at the zeros". I have tried using "Find and Replace" to eliminate the zeros, but that doesn't work. To make my graph look like I want it to, I have to manully delete the formula in the cells that return zeros. I guess I am looking for a way to find a cell based on the results of the formula in that cell, and replace it will an empty cell.

 

[Hui]

Put your formula in another if

=If(Formula=0, na(), Formula)

 

[Verizon123]

Thanks Hui - That worked, to a point. What is now happening is in the column of formulas the IF formulas now returns either the calulated numeric value, or #N/A, which is good, as the graph line now follows the numeric numbers and then disappears when it sees the #N/A. However, it does not disappear iwhen the #N/As are between numeric values in the chart data range, it bridges the line between the first numberic value and the next numeric value. If I delete only one of the "#N/As" between the numeric values, it breaks the chain so to speak and displays correctly.

 

[Verizon123]

Here is an example

 

[Hui]

Verizon

There is no way to automatically break the chart series as you want
I learnt that the NA() trick only suppresses the display of markers, it doesn't actually break the series line

You could use some simple VBA if required to automatically put a blank into the series where there is a #N/A error

Alternatively you could use 2 series to do what you want

The above chart uses two series

See attached file

 

[Verizon123]

Thanks, but the was it looks the 2 series solution doesn't quite work for what I need either...this I guess will require VBA...See attached...

 

[Hui]

So you may need multiple series
Or a macro to go through one series and put gaps in

 

[Vândalo]

Hi Verizon,

See if it works for you, although I think this match Hui's solution

How-to Show Gaps in a Line Chart When Using the Excel NA() Function

Hope this help.

Vândalo