Empty versus #N/A and Pasted Values

Originally Posted by ChristiaanV

Hi. Like many I have a chart on which I do not want to plot "blank" values. I use another spreadsheet on which I do calculations (I do them on a separate sheet because I use a number of arrays and it is exceedingly slow) then paste the values into the chart sheet.

The problem is that when I pasted empty cells, the chart plotted them as zero. When I changed the cells manually to #N/A, Excel behaved how I wanted and did not plot those cells. So I changed my calculation spreadsheet to put #N/A in what were previously blank cells. However, when I pasted the new results in my chart sheet, Excel then plotted #N/A as zero. When I deleted the #N/A values from those cells and made them empty, Excel did what I wanted and did not plot those cells.

In essence, whether I use empty cells or cells with #N/A, Excel never accepts what I initially paste into the sheet -- it always requires me to change the values. What I can do so that Excel "accepts" the values I initially paste as far not plotting "blank" cells?

Thanks,
Christiaan

Hi,

I have attempted to do this, and cannot.

I have cells that contain #N/A and cells which the result of a VLookup are #N/A interpersed with cells that contain numbers, on two sheets. No matter how I copy & paste the #N/A remains as #N/A and the chart remains devoid of any series Point for those cells.

Do you have any more details?
---

I am copying calculated values to the named ranges. I use the "paste-special, paste as values" option to paste into the named ranges.

I'm going to try a test where I paste the values first to an intermediate sheet, then copy and paste from there into the named ranges using the simple "paste" command.

Edit: I tried it but achieved the same result - whether I'm pasting values from the initially calculated results, or from another sheet with pasted values, Excel plots empty or #N/A cells as zero unless I delete the cell contents.

Originally Posted by ChristiaanV

Well, I found an easy workaround at least. I paste the values as before with "blank" cells holding a value of #N/A. Then I use the Replace menu command to replace #N/A with NA() in the selected range.

ok if it works, but as per my other post, I tested copy paste, copy paste-special-values, and copy paste special other options but could not come up with a zero in place of a #N/A

Is your #N/A a true #N/A (derived from a formula failing) or one where you are using "#N/A"
ie quotes#N/Aquotes ?

---

Originally Posted by ChristiaanV

Brian, it is the latter: "#N/A" is the result of one option in an "if" statement. I'm using Excel 2000 at work; I don't know if that has an effect. I had the same results on my 2003 version at home, but I was only monkeying with the same spreadsheet that was created on 2000.

Originally my formulas had a a blank "" as one result, but because I was pasting those values from one spreadsheet into the final chart spreadsheet, the cells were empty and Excel was plotting them as zeros. I was advised to enter #N/A for Excel to treat them differently, which worked. I changed my formulas in the first spreadsheet to return #N/A instead of blanks, but I still had to make manual changes in the final spreadsheet in order for the data to plot correctly.

I wasn't familiar with the Replace command or I would have worked this out earlier. Thanks for your help anyway.

Hi,

the quotes will kill it

Use
=If("A"="B","",#N/A) and not
=If("A"="B","","#N/A")

#N/A is a condition, not a piece of text
"#N/A" is a piece of text, and not a condition.

Then your #N/A will work as planned.
---