Plotting #N/A

Hello,

I am trying to plot a set of data that has #N/A values in some cells. Excel will ignore the #N/A values and skip plotting them IF they are on the ends of the data (i.e. ..., N/a , data, data, ... , data, N/A, ...). However, if the N/A values are interspersed with data values Excel automatically interpolates the chart(i.e. N/A, data, n/A, ..., N/A, Data, ...., Data, N/A). In the previous example there would be a line interpolation between the first data and the next data even with the N/A values. Does anyone know a workaround for this problem besides deleting the N/A Values? (I want blanks in the graph if there is no data.)

Thank you,

excelBRISKbaby

the N/A shows up because of your formula right? modify the formula to look like this
=if(iserror({yourformulahere}),"",{yourformulahere})

that will put blanks in instead of #n/a

If I do this then the points plot as zero's. Which is also incorrect.

In the few minutes I tinkered with it, I couldn't see a ready work around. I made sure the "how to plot blanks" option (Tools->Options) was set to "not plot--leave breaks." But the only way to get the Chart to recognize a blank was if the cell was truly blank (remember that any cell with any function including =IF(iserror(A1),"","") is considered "not blank" even though it appears empty).

Best approach I could see was clearing the cells with NA. If this is tedious for you, I could see approaching this with a VBA Sub procedure, perhaps even associated with the calculate event.

I'd love to see if anyone else has any ideas.

Currently deleting the N/A's are the only way that I have found to accomplish this. Since I have to do this frequently I can only see using Macros. If anyone does know of a workaround, it would be much appreciated.

excelBRISKbaby