Excel Line Char: Empty Cell calculated by Formula “” as gaps

[longbow2000]

Good Day Team

I have a line graph (see attached image) with line graph with first set of cell value as blank. The graph correctly plot the empty cells as gaps

The after I have a formula calculating values, and if values are less than zero, or an illegal rule of calculation (e.g. SQRT of a negative value), then the cell must be blank. This is done by an IF statement and add “” in the cell. Ignoring the “” will add a Zero on the cell.

Plotting the values, the cells with “” (suppose to be empty) is also plotted ☹, in the image attached it then shows a vehicle decelerated to a stop (speed zero), but distance coniums.

Any help or suggestions, or is there a way to plot the zero values also as gaps?

Thanks in advance.

 

[RoryA]

Return NA() rather than "" from the formula (there is unfortunately still no equivalent in Excel of the DAX BLANK() function). You can use conditional formatting to hide the error values in the cells if needed.

 

[longbow2000]

Return NA() rather than "" from the formula (there is unfortunately still no equivalent in Excel of the DAX BLANK() function). You can use conditional formatting to hide the error values in the cells if needed.

Hi Rory, thanks works 100%!

 

[RoryA]

Glad we could help.