X-axis data is formula, cannot do scatterplot problem

**t.jones** · 01-30-2012, 08:31 AM

Hi all

I am trying to make a simple XY plot of some data. The data for both axes are not actual values but formulae that take data from a different workbook. Within this formula I have an IF function to display a blank cell, rather than 0, when the original data file contains no data for that particular cell. But this is causing problems for the graph...if there is a blank cell within the range of the X-axis it causes the data to be ranked as 1,2,3, etc rather than taking the actual values. It is because the blank cell isn't actually blank, it contains a formula. Anyone know how I can get round this problem? It doesn't matter if there are blank cells in the Y-axis range for some reason.

Many thanks
Tim

**Andy Pope** · 01-30-2012, 08:36 AM

use NA() instead of "" in your formula

**Pete_UK** · 01-30-2012, 08:37 AM

Instead of returning a blank (""), return NA() instead - this will give you the error #N/A which will be ignored by the graph. If you don't want to see the error, you can use conditional formatting to print white on white if the cell contains an error, and this will make it look blank.

Hope this helps.

Pete