Formula that returns values to a scatterplot

[karnor]

Hi,

I have a series of values for a scatter plot. For some of the values I have created this formula to return values: =IF(LEFT(D43;4)="-N/A";"";D43)

When a cell is -N/A the value for the scatter plot is blank but it doesn't work with with the diagram. All I can do now is to delete the formula and the diagram works. Is there a workaound for this?

 

[pgc01]

When a cell is -N/A the value for the scatter plot is blank but it doesn't work with with the diagram. All I can do now is to delete the formula and the diagram works. Is there a workaound for this?

Hi
Welcome to the board

"it doesn't work with with the diagram"

What do you mean by this? What happens now and what would you like to happen differently?

 

[karnor]

Ok, so let's say that cell D43 is -N/A then the formula returns a blank cell (""). However, this still enters a value to the scatter plot even though the cell is blank and distorts the diagram. But if I delete the formula the diagram works fine.

 

[pgc01]

Hi

So you want a gap in the line if the cell does not have a valid value.

This is not possible with just native formulas. Unless the a cell is empty, excel does not break the line.

Solutions:
- you empty those cells where you want the gaps in the line
- you use a udf to adjust the chart input to replace those cells with empty ones

You can also use vba and build a custom solution.

The simplest way, IMO, is to use a udf to replace in the series input ranges those cells that do not have a value with an empty cell.

Jon Peltier posted a small udf that does just that here:

Another approach to plotting gaps in Excel charts - Peltier Tech Blog

Applying a udf like this one to the series input will give you gaps in the line when a cell does not have a valid value.

Does this help?